DB2/UDB batch query

Posted on 2001-07-30
Last Modified: 2008-02-07
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.
Question by:llewelm
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2

Expert Comment

ID: 6335455
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

Author Comment

ID: 6335582
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.

Expert Comment

ID: 6340554
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Accepted Solution

jbrickard earned 75 total points
ID: 6347015
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.

Expert Comment

ID: 6348802

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.

Author Comment

ID: 6348846
I don't have the -x command available to me either.  Is there an alternative?

Expert Comment

ID: 6350276
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.

Author Comment

ID: 6380135
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...

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
mysql database, schema and table creation 13 89
T-SQL Default value in Select? 5 54
Database ERD 4 80
SQL*Plus debugging?  Is there a way to step through the SQL Script? 3 43
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question