How to pass parameters to an sql script

[ddiamond]

I understand that you can execute a db2 script with the following command:

db2 -f <file name>

Is there a way to pass command line parameters to this file?

For example, in oracle you can do this by referring to the first command line parameter as &1, the 2nd as &2, ect... Does DB2 have anything similar?

 

[BrianTyler]

I don't think that there is any way to pass parameters this way.

I tend to build the SQL in a script, and to let AIX do the substitution.

eg:

LOGFILE=$LOGDIR/temp.log
echo $1
db2 -t -s +v +p -x connect > $LOGFILE
db2 -t -s +v -z$LOGFILE +p <<FLAG;
select * from custschm where cs_cust_no = $1;
FLAG

The SQL statement can be any number of lines (within reason), and the system works particularly well when using export to a CSV file to go into Excel etc

When setting up the scripts make sure that there are no trailing spaces after the end of file marker (FLAG in my case)

 

[ddiamond]

Thanks Brian. Right now our test DB2 database is running on Windows, but our production DB2 database will be running on AIX end of December. So I'll try out your script when we get our AIX box. I'm new to unix scripts. Which shell are you using? Why didn't you need db2 in front of your sql statement? Does it have something to do with FLAG?

 

[BrianTyler]

The actual line before the 'select' is the line passed to AIX. This starts with db2 so it will be passed on to the database. The 'piping' in signified by the <<FLAG tells the system to read all rows until the FLAG row, and to pass them on to db2.

It is a bit like reading data from stanadard system input into a batch program.

I imagine that you could do something similar with a pc BAT file, but I am really rusty in that area.

We are using the KORN shell, KSH89