Does DB2 CLI support parameters markers in SQL that needs to be executed from a bash script [AIX]

Question

Does the db2 -x or db2 -f filename.sql options support use of parameter markers? I am calling a SQL statement from a bash script. I cannot find any optios so far and I think I have to use the string concatenation in bash and pass the SQL to db2 -x which does not look neat.

Answer 1

The DB2 CLP does not support using parameter markers.

However, when you are running db2 non-interactively (i.e., from the shell), you can substitute shell variables:

$ v=4
$ db2 "select count(*) from syscat.tables where card = ${v}"

Because this is just using shell substitution you'll need to handle adding single quotes around your strings (... where tabschema = '${tabschema}' ...), and if your strings have single quotes this becomes more complicated.

You can also use this method in shell scripts in loops:

#!/bin/ksh

db2 connect to sample

for v in 1 2 3 4 5 ; do
    db2 "insert into t1 (c1) values (${v})"
done

db2 terminate

This will not help if you're trying to use parameter markers to avoid recompiling the SQL statement every time it's executed, but if you're looking for that you probably want to think about using perl with DBI, ruby/ibm_db, etc.