DB2/UDB batch query

I need to execute a batch DB2/UDB command under AIX which counts the number of records in a table.  The resulting value will be used subsequently in the script.

A simple example under MS-SQL Server would be as follows from within a DOS batch file:

  isql -S CMC_NT1 -d CSTI -U sa -P -Q "EXIT(select count(*) from Products)" > NUL

where ERRORLEVEL will contain the results of the SELECT statement.

I'm new to DB2/UDB and need to know how I can execute a query using the db2 command line utility and transfer the resulting value back to the operating system level (exit code?) for later use.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

jbrickardConnect With a Mentor Commented:
Using the EXPORT utility would be one way, but then the result has to be read back from a file.

On UNIX however, you can just capture the result of a standard DB2 CLP command (with appropriate formatting options) in a shell variable.

For example, assuming your CLP session has default options, the following script should work:

db2 "CONNECT TO sample USER youruser USING yourpassword"
result=`db2 -x "SELECT COUNT(*) FROM yourschema.yourtable"`
echo $?
echo $result

So long as $? returns 0, $result should contain the single value returned by the db2 CLP command contained between the ` marks.  The -x CLP option suppresses column headings, thereby ensuring that only a single line containing a single number is returned (so long as your SQL is correct).

My Korn shell knowledge is not that strong, so there may possibly be a slight error in the above - but it's definitely along the right lines (I've used this for real).

Jeremy Rickard
DB2 UDB specialist
United Systems(UK) Ltd.
You can check the return code of the db2 operation by evaluating "$?", e.g.

-------------------------------------begin samplescript.ksh

db2 "connect to mydb user myuser using mypassword"
if [ $? != 0 ]; then
  print "An error occurred when connecting to the database"
  exit -1

db2 "select count(*) from products" > productcount.txt
if [ $? != 0 ]; then
  print "An error occurred when counting the products"
  exit -1

exit 0
---------------------------------------end samplescript.ksh
llewelmAuthor Commented:
According to the DB2/UDB documentation, the exit code from the db2 command is 0, 1, 2, 4 or 8 indicating the relative level of success in executing the command, not the actual record count as I'm looking for.

Referring to your specific example, I could parse the value out of the 'productcount.txt' file.  However, that is way more trouble than it's really worth.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Sorry, I misunderstood the question. Your reference to the exit code led me to believe you wanted to know whether the operation succeeded, not the actual record count.

I agree that parsing out the value can be a pain, since you have to account for the column header and separator line. It's a lot easier when you use the export facility, e.g.

db2 "export to count.txt of del select count(*) from products"
prodcount=`cat count.txt`
rm count.txt


My version of DB/2 (6.1) doesn't recognize the -x option. Is this new in v7? This would be a really useful flag to have - much more elegant than using a temporary file.
llewelmAuthor Commented:
I don't have the -x command available to me either.  Is there an alternative?
I don't have a copy of v6 to hand, but I'm pretty certain that -x has been supported always (in DB2 UDB)

To double check, you can type:

  db2 LIST COMMAND OPTIONS at the command prompt

-x should be displayed (near the bottom).

I've just tried a simple script on Linux, pretty much as before, except I had the ksh directive wrong:

  db2 "CONNECT TO SAMPLE USER rickard USING xxx"
  result=`db2 -x "SELECT COUNT(*) FROM SYSCAT.TABLES"`
  echo return code = $?
  echo result = $result

The output is:

  return code = 0
  result = 150

Have you tried this out?  If so and you are still having problems, send me your script and the output, and I'll take a look.
llewelmAuthor Commented:
We're running v6.1 here and the -x option is not available.  The result is a message indicating an invalid option.

However, removing the -x from the test script still results in the correct result.  Thanks for the help...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.