CODEWITHSUNDEEP
variableswhile-loopdb2subshell
Richard C
Richard C

Reputation: 411

DB2 Output to Variable via bash script

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.

Upvotes: 0

Views: 6714

Answers (2)

Fred Sobotka
Fred Sobotka

Reputation: 5332

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

Upvotes: 2

Ian Bjorhovde
Ian Bjorhovde

Reputation: 11042

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

Upvotes: 0

Related Questions