DB2 Output to Variable via bash script

Question

I'm hoping someone can help with applying the output from a db2 command to a variable to use later on in a script.

So far I am at...

db2 "connect to <database> user <username> using <password>"

while read HowMany ;
do
  Counter=$HowMany
  echo $HowMany
done < <(db2 -x "SELECT COUNT(1) FROM SYSCAT.COLUMNS WHERE TABNAME = 'TableA' AND TABSCHEMA='SchemaA' AND GENERATED = 'A'")

When trying to reference $Counter outside of the while loop, it returns SQL1024N A database connection does not exist. SQLSTATE=08003 as does the echo $HowMany

I've tried another method using pipe, which makes the $HowMany show the correct value, but as that is a sub shell, it's lost afterwards.

I'd rather not use temp files and remove them if possible as I don't like left over files if scripts abort at any time.

Fred Sobotka · Accepted Answer · 2015-02-07 02:56:07Z

The DB2 CLP on Linux and UNIX can handle command substitution without losing its database connection context, making it possible to capture query results into a local shell variable or treat it as an inlined block of text.

#!/bin/sh
# This script assumes the db2profile script has already been sourced

db2 "connect to <database> user <username> using <password>"

# Backtick command substitution is permitted
HowMany=`db2 -x "SELECT COUNT(1) FROM SYSCAT.COLUMNS WHERE TABNAME = 'TableA' AND TABSCHEMA='SchemaA' AND GENERATED = 'A'"`

# This command substitution syntax will also work
Copy2=$(db2 -x "SELECT COUNT(1) FROM SYSCAT.COLUMNS WHERE TABNAME = 'TableA' AND TABSCHEMA='SchemaA' AND GENERATED = 'A'")

# One way to get rid of leading spaces
Counter=`echo $HowMany`

# A while loop that is fed by process substitution cannot use 
# the current DB2 connection context, but combining a here 
# document with command substitution will work
while read HowMany ;
do
  Counter=$HowMany
  echo $HowMany
done <<EOT
$(db2 -x "SELECT COUNT(1) FROM SYSCAT.COLUMNS WHERE TABNAME = 'TableA' AND TABSCHEMA='SchemaA' AND GENERATED = 'A'")
EOT

Ian Bjorhovde · Accepted Answer · 2015-02-05 17:35:21Z

0

As you have found, a DB2 connection in one shell is not available to sub-shells. You could use a sub-shell, but you'd have to put the CONNECT statement in that sub-shell.

So it's more of a simple rewrite, and don't use a sub-shell:

db2 "connect to <database> user <username> using <password>"

db2 -x "SELECT COUNT(1) FROM SYSCAT.COLUMNS WHERE TABNAME = 'TableA' AND TABSCHEMA='SchemaA' AND GENERATED = 'A'" | while read HowMany ; do
    Counter=$HowMany
    echo $HowMany
done

answered Feb 5, 2015 at 17:35

Ian Bjorhovde

11.1k1 gold badge30 silver badges26 bronze badges

4 Comments

Richard C Over a year ago

Thanks Ian. Your solution above is an option I've tried, but the variable when referenced outside the loop returns a null value. I'm starting to think that I might need to use files for this which I'd love to avoid

Ian Bjorhovde Over a year ago

Are you saying that the value of $Counter is not set after you leave the do loop? Or $HowMany?

Richard C Over a year ago

$Counter returns a null value, well, both do after the loop

Ian Bjorhovde Over a year ago

You must be using bash. It is a little more careful about ensuring that variables used in a loop are only in the scope of that loop. If you execute the script I've suggested with ksh, it will work. Or, @Fred Sobotka's solution will work if you're only returning 1 column from your query.

Collectives™ on Stack Overflow

DB2 Output to Variable via bash script

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related