db2 sql clp

[pspivack@snet.net]

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.

[Phil Sherman]

Re: db2 sql clp

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


pspivack@snet.n et 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.
>

[Lennart]

Re: db2 sql clp

On Feb 3, 10:44 pm, pspiv...@snet.n et 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