DB2 CLP passing variable in .sql file

Question

As my shell script is calling Db2 many times with an ultra long SQL statement, I would like to abstract the SQL statements from the script and put them into a .sql file, then run it from the Db2 command line processor db2 -f xxx.sql.

I want some of the SQL statements to use variables that are passed in from the shell script.

Is there any method like Oracle's sqlplus that can pass variables inside the .sql file by '&1'?

xxx.sql
SELECT * FROM TABLE_A WHERE FIELD_B > &1

CLP
db2 -f xxx.sql 999

This returns a DB21004E error:

DB21004E  You cannot specify both an input file and a command when invoking
the Command Line Processor.

Answer 1

The db2 CLP on Linux/Unix/Windows does not support substitutable parameters, something that IBM omitted long ago.

You can consider one the these options:

• Use the java based clpplus tool instead of the db2 CLP to run scripts. It emulates some of the Oracle SQL*plus functionality, including the ability to pass positional parameters on the command line ,and reference them like &1 and &2 etc in your scripts. However, the scripts then need to use Oracle style syntax , although your Db2-server does not need Oracle compatibility. You also get more formatting control over the query output.

• Move the query logic into routines in the database(s), and invoke them with parameters in your scripts. For example call the queries as stored-procedures with input parameters.

• dynamically generate your SQL scripts with the relevant parameters and continue to run them with the db2 CLP. In effect you are doing your own variable substitution, possible via tools like awk and sed or similar.

Answer 2

A session global variable can be initialized and referenced by different SQL statements and scripts that execute within the same Db2 session/connection handle. This is a Db2 SQL feature that will work in CLP, clpplus, stored procedures, inline/compound SQL blocks, and any programming language with a Db2 client driver.

The variable itself is a permanent object in the database and only needs to be created once. Its name and data type will be visible to all authorized users in the database, but the value stored in the variable is private for each connection.

First, create the variable. This only needs to be done once.

db2 "CONNECT TO DBNAME"
db2 "CREATE VARIABLE UTIL.FIELDBMINVALUE INTEGER"

In your SQL script, refer to the variable instead of a literal value. The script cannot contain any CONNECT statements because that would destroy the current session.

-- xxx.sql
SELECT * FROM TABLE_A WHERE FIELD_B > UTIL.FIELDBMINVALUE

Before running the SQL script, connect to the database and initialize the variable.

db2 "CONNECT TO DBNAME"
db2 "SET UTIL.FIELDBMINVALUE = 999"
db2 -f xxx.sql
db2 "CONNECT RESET"
db2 "TERMINATE"

Answer 3

Thank you all the above reply.

Here is my method to dynamically generate the SQL from .sql file by envsubst. This is highly similar to the original oracle .sql file. Only have to change &N to $varN.

--xxx.sql
--$var1 : field B remark
--$var2 : field C remark

SELECT * FROM TABLE_A 
    WHERE FIELD_B > $var1
    AND   FIELD_C = $var2 ;

#Script
send_xxx_sql_file () {
var_string=$(for i in $(seq $#); do echo -e "var$i=\${$i} \c"; done)
SQL_statement=$(eval "$var_string envsubst < xxx.sql " | sed '/^--/d' | tr "\n" " " | tr -s " ")
db2 "$SQL_statement"
}

send_xxx_sql_file 999 111

var_string becomes var1=${1} var2=${2}

SQL_statement becomes SELECT * FROM TABLE_A WHERE FIELD_B > 999 AND FIELD_C = 111 ;

sed '/^--/d' delete lines with leading --

tr "\n" " " replace newline by space

tr -s " " replace sequence of repeated space into 1 space