DB2 Alias Example

Add an alias
Enter the alias name: alias-db2
Enter the name of the precompiler: db2prep.sh
Enter precompiler options: database B_INPUT B_OUTPUT B_ERROR
Enter precompiler directives: EXEC SQL
Enter required precompiler extension if any: .sqb
Press <Return> to continue...

DB2 requires some setup before running its precompiler. You can perform the necessary setup by specifying a shell script file (db2prep.sh in this example) to run instead of specifying the name of the precompiler. The shell script performs the necessary setup and then starts the precompiler. The Boomerang keywords beginning with B_ are passed to the shell script by specifying them at the precompiler options line. EXEC SQL is specified as the precompiler directive since DB2 users EXEC SQL to begin its DB2 statements. The DB2 precompiler requires that the input file have an extension of .sqb so this is specified at the required precompiler extension line.

Using the example above, when the Boomerang server runs the shell script it looks something like this:

db2prep.sh database ACCT01.sqb acu__pp1.out acu__pp1.std

Note that the precompiler options must be specified in the order that the shell script expects. The following is an example of a DB2 shell script used by the Boomerang server:


# db2prep.sh - This script is designed to be called from the 
# Boomerang server. For this script the Boomerang server alias # file would need to have the following precompiler options     # using the Boomerang file keywords:
# Precompiler-Options: database B_INPUT B_OUTPUT B_ERROR 
# In this script the Boomerang keywords get mapped to the       # following script variables:
# database = $1 the name of the database to connect to
# B_INPUT  = $2 the precompiler input file
# B_OUTPUT = $3 the precompiled output file
# B_ERROR  = $4 the precompiler error file

# Execute the DB2 configuration file
. /home/db2inst1/sqllib/db2profile

# DB2 precompile -- takes .sqb as input, outputs .cbl
echo ==================================================
echo  Begin output from \"db2 prep\" SQL precompiler:
echo ==================================================

# Connect to the database
db2 connect to $1 >$4 2>&1

# Execute the precompiler on $2 sending any error output to $4
# capture the return code in $returnCode
db2 prep $2 target ansi_cobol >>$4 2>&1

# Boomerang expects the precompiled output file to be the name
# specified by $3.  The precompiled output file created by DB2
# is the name as the source file but with an extension of .cbl.
# We need to remove the extension from the input file, $2, and
# add a .cbl extension so that we can copy it to $3.
# The following command removes the "." and everything past it # to create the prefix.
prefix=`echo $2 | sed -e "s/\..*$//"`

# Copy the precompiled output file to the name that Boomerang 
# is expecting, $3.
cp $prefix.cbl $3

# the db2 CLP returns 2 for warnings; treat as if a 0 was returned
if [ $returnCode -eq 2 ]; then
if [ $returnCode -eq 0 ]; then
    db2 connect reset  >>$4 2>&1
    db2 terminate      >>$4 2>&1
    echo \"db2 prep\" failed:  return code:  $returnCode >>$4 2>&1
exit $returnCode