By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,004 Members | 1,253 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,004 IT Pros & Developers. It's quick & easy.

db2 sql clp

P: n/a
I have a UNIX .sh script that invokes a DB2 SQL .sql script that
contains the following:

db2 -t -f parmtest.sql -z parm_output.log

My question is how can I pass a parameter to the parmtest.sql script
from the db2 clp (if it is possible) and how does one reference the
parameter in the .sql script. The .sql script is not a procedure.

Feb 3 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
The simple answer is: "you can't".

The command line processor, when reading a file containing SQL
statements has no ability to pass parameters and perform on-the-fly
modification to the statements.

All is not lost, however. You can take the entire .sql script and embed
it into the .sh script. Preface each statement in the .sql script with
an "echo" command and append (>>) all output of the "echo" commands
except the first to the .sql script. The first "echo" statement should
redirect (>) its output to the .sql file. Now you can use parameters to
the .sh script to modify the SQL statement(s).

Remember that the userid running the .sh script must have write access
to the .sql file for this to work.

I'd also change the options on the db2 command to -tvf. This will list
the SQL statements in the output. Of course, if you pass the output into
additional automated processing, you'll need to handle the additional
data records in the output file.

Phil Sherman
ps******@snet.net wrote:
I have a UNIX .sh script that invokes a DB2 SQL .sql script that
contains the following:

db2 -t -f parmtest.sql -z parm_output.log

My question is how can I pass a parameter to the parmtest.sql script
from the db2 clp (if it is possible) and how does one reference the
parameter in the .sql script. The .sql script is not a procedure.
Feb 4 '07 #2

P: n/a
On Feb 3, 10:44 pm, pspiv...@snet.net wrote:
I have a UNIX .sh script that invokes a DB2 SQL .sql script that
contains the following:

db2 -t -f parmtest.sql -z parm_output.log

My question is how can I pass a parameter to the parmtest.sql script
from the db2 clp (if it is possible) and how does one reference the
parameter in the .sql script. The .sql script is not a procedure.
You can't, but there are ways around. Assume parmtest.sql looks like:

select * from T where T.a = ? and T.b = ?

You can change it to (for example)

select * from T where T.a = @@P1@@ and T.b = @@P2@@

Now you can do this as:

sed -e "s/@@P1@@/<your p1>/g" -e "s/@@P2@@/<your p2>/g" < parmtest.sql
tmp.sql
db2 -t -f tmp.sql -z parm_output.log

/Lennart

Feb 4 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.