0

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
  • 5,252
  • 22
  • 32
Richard C
  • 401
  • 1
  • 5
  • 19

2 Answers2

2

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
Fred Sobotka
  • 5,252
  • 22
  • 32
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
Ian Bjorhovde
  • 10,916
  • 1
  • 28
  • 25
  • 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 – Richard C Feb 05 '15 at 19:03
  • Are you saying that the value of `$Counter` is not set after you leave the do loop? Or `$HowMany`? – Ian Bjorhovde Feb 05 '15 at 19:42
  • $Counter returns a null value, well, both do after the loop – Richard C Feb 05 '15 at 22:15
  • 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. – Ian Bjorhovde Feb 06 '15 at 20:40