Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


DB2/UDB batch query

Posted on 2001-07-30
Medium Priority
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.


Accepted Solution

jbrickard earned 225 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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

610 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