• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 805
  • Last Modified:

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
  echo %ERRORLEVEL%

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.
0
llewelm
Asked:
llewelm
  • 3
  • 3
  • 2
1 Solution
 
pluimCommented:
You can check the return code of the db2 operation by evaluating "$?", e.g.

-------------------------------------begin samplescript.ksh
#!/usr/bin/ksh

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

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

exit 0
---------------------------------------end samplescript.ksh
0
 
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.
0
 
pluimCommented:
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

0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
jbrickardCommented:
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:

#/bin/ksh
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.
0
 
pluimCommented:
Jeremy,

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.
0
 
llewelmAuthor Commented:
I don't have the -x command available to me either.  Is there an alternative?
0
 
jbrickardCommented:
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:

  #!/usr/bin/ksh
  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.
0
 
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...
0

Featured Post

Independent Software Vendors: 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!

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now