Solved

DB2/UDB batch query

Posted on 2001-07-30
8
759 Views
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
  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
Comment
Question by:llewelm
  • 3
  • 3
  • 2
8 Comments
 
LVL 4

Expert Comment

by:pluim
ID: 6335455
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
 
LVL 1

Author Comment

by:llewelm
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.
0
 
LVL 4

Expert Comment

by:pluim
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

0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Accepted Solution

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

#/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
 
LVL 4

Expert Comment

by:pluim
ID: 6348802
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
 
LVL 1

Author Comment

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

Expert Comment

by:jbrickard
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:

  #!/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
 
LVL 1

Author Comment

by:llewelm
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...
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL anywhere 11 databases 1 90
Session that filled up my transaction logs 1 65
SQL SELECT query help 7 56
Display SQL 2008 last modified/update Database 11 18
I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
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.
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…

829 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