db2 sql script file

Question

I have an oracle script that I am trying to convert to valid db2 syntax. Within this sql file I have various calls to other sql files passing in a parameter using the '@' syntax.

e.g.

@script1 param1
@script2 param2

Can anyone help me with valid db2 equivalent statements? Is there an equivalent run command in db2? is it possible to pass parameters to a sql script in db2?

thanks,

smauel

Answer 1

The thing you are after is the DB2 Command Line Processor (CLP).

If you want to execute a script, you would execute in the CLP:

db2 -vtf script1

-f tells the CLP to run command input from the given file.

Here's the full list of options.

Unfortunately db2 doesn't support passing parameters to a script. You would have to combine your db2 -vtf commands with other scripting commands (such as sed) to generate the scripts for you, as in this example.

Answer 2

1) place the filename.sql file in SQLLIB/BIN

2) run db2cmd

3) execute this to connect to the required db

 db2 connect to *dbname* user *userid* using *password*

4) excute this command

 db2 -vtf *filename.sql*

This should execute the sql statements in the file one by one. The sql statements must be ending with a semicolon

Answer 3

There is an easier way for passing in parameters, that works fine for us (it might not work with (complex) multiline sql statements).

Convert your sql-script into a shell script by adding 'db2 ' at the beginning of each line. Than you can use the standard variable replacement syntax from your shell in your scripts.

so instead of

insert ...
update ...

you will have

db2 insert ...
db2 update ...

Answer 4

---

1. Place file in one directory.
2. Open db2cmd.exe as administrator
3. Navigate to directory where you have place the script
4. type db2 -vtf `