Many DBAs who have been DBAs for a while have been bitten by executing an SQL script, not thoroughly checking the output, and finding later that one or more statements in the SQL script failed. I have certainly been guilty of this at times.
Success of a Command
In Linux and UNIX, when a command is run, the OS notifies us if the command fails. If you’re scripting in these, you’ll check the return code of the command to see if it was successful. In my favorite scripting language, Perl, any error message is stored in $!. However, it is a little different when executing DB2 commands. Whether DB2 thinks the command succeeded or failed, the operating system is checking at a different level. Therefore even DB2 errors are seen as successes by the OS. From the Operating System’s point of view, it passed a command to DB2, and DB2 successfully handled the command and returned any output that needed to be returned. Even if that output was a error from DB2 saying DB2 could not do what you asked.
This means that parsing the output from DB2 commands takes a bit more work than just checking what’s written to STDERR.
Parsing the Output of an SQL Script for Errors
A lot of work is done with DB2 using SQL scripts of commands. See my blog entry on DB2 Basics: Executing a Script of SQL if you’re not familiar with how to execute a script of SQL.
The first step in making sure you can easily find errors in your output is saving that output somewhere. It is also important to use the
v option when executing SQL to ensure that you are capturing both the statement that is failing and the output from that statement. My preferred syntax for executing SQL files is:
db2 -tvmf filename.sql |tee filename.sql.out
The tee allows me to see the output while the statements are being run. I do change the tee to a simple “>” if I’m doing a large number of statements or expect a large number of lines as output. tee can add significant processing time in these situations that “>” avoids.
The “m” tells db2 to output the count of rows affected for DML, and is only applicable in certain situations. It was introduced in DB2 9.1, so if you are seriously down level, it may not be available to you.
Using syntax like this gives you good information in checking for errors. If there are too many lines of output to easily parse through visually, I use grep to help me search through the output file for errors. I’ve found the following works well to catch many errors and eliminate many false positives:
cat filename.sql.out |grep SQL |grep -v DB20000I|grep -v "LANGUAGE SQL" |grep -v "READS SQL DATA"
Errors generally start with “SQL”. Running this on an output file I’ve been working with lately, I get this:
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be created is identical to the existing name "DB_ADM_STOGRP" of type "STOGROUP". SQLSTATE=42710 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be created is identical to the existing name "USERTEMP32K" of type "TABLESPACE". SQLSTATE=42710 DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0601N The name of the object to be created is identical to the existing name "DBA32K" of type "TABLESPACE". SQLSTATE=42710 CONTAINS SQL DECLARE SQLCODE INTEGER DEFAULT 0; WHILE (SQLCODE=0) DO
So the last three lines there are not errors, but lines that happen to contain the string “SQL”. But before that are valid errors. This is much easier to look at than the 9,091 rows of the actual file I was parsing in this case.
More Advanced Error Checking and Scripting
When you get beyond simple SQL files to shell or Perl or other scripting, you have to decide how to locate and handle errors. In my Perl scripts, I have several error checking routines that I can hand the output of every SQL statement to – some die on finding an SQL error, some simply warn, and others allow me to pass in an error that is OK, and so forth. If you’re using Perl, a DBI will do a lot of that for you, and may make error handling much easier. Many scripting languages more sophisticated than KSH have that kind of construct.
These languages get and parse the SQLCA. The SQLCA is the SQL Communications Area. It consists of a number of variables that are updated at the end of every SQL statement. The information defined there is well laid out in the IBM DB2 Knowledge Center: http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0002212.html?cp=SSEPGG_10.5.0/2-9-12-0
If you want to play with the SQLCA and start understanding it, you can use -a on the command line:
$ db2 -a "select npages from syscat.bufferpools where BPNAME='IBMDEFAULTBP'" NPAGES ----------- -2 1 record(s) selected. SQLCA Information sqlcaid : SQLCA sqlcabc: 136 sqlcode: 0 sqlerrml: 0 sqlerrmc: sqlerrp : SQLRI01F sqlerrd : (1) -2147221503 (2) 1 (3) 1 (4) 0 (5) 0 (6) 0 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 00000
Usually the sqlcode is the thing we are most interested in.
If the sqlcode is a negative number, then it’s an error:
db2 -a "select junk from syscat.bufferpools where BPNAME='IBMDEFAULTBP'" SQLCA Information sqlcaid : SQLCA sqlcabc: 136 sqlcode: -206 sqlerrml: 4 sqlerrmc: JUNK sqlerrp : SQLNQ075 sqlerrd : (1) -2145779603 (2) 0 (3) 0 (4) 0 (5) -10 (6) 0 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 42703
And if it’s a positive number, then it’s a warning:
CREATE INDEX WSCOMUSR.I_ATTRVAL01 ON WSCOMUSR.ATTRVAL (ATTR_ID, VALUSAGE, FIELD3, FIELD2, FIELD1, STOREENT_ID, IDENTIFIER, ATTRVAL_ID) ALLOW REVERSE SCANS COLLECT DETAILED STATISTICS SQLCA Information sqlcaid : SQLCA sqlcabc: 136 sqlcode: 605 sqlerrml: 20 sqlerrmc: WSCOMUSR.I_ATTRVAL01 sqlerrp : SQLRL1CF sqlerrd : (1) -2145779603 (2) 0 (3) 0 (4) 0 (5) 0 (6) 0 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 01550
Of course there may be more than just the sqlcode that you’re interested in, and at the command line, the actual error text is often more useful than the SQLCA. Using the -a option replaces the usual text output with the SQLCA output. But if you’re scripting it may be easier to parse the SQLCA. This is the information that the Perl DBI and such modules look at.
It is a every DBA’s responsibility to check the success or failure of every statement they run at the command line and in scripts. You should never assume that something was successful without checking. This may require you to step up your scripting skills, but it is really important to ensure that things are doing what they are expected to.
You can also use the -s flag with the db2 command line to get DB2 to stop on error. For example:
db2 -sv “insert into myschema.table where id = 1”
This will return an error to the operating system (non-zero in Unix). You can then use
if [ $? != 0 ] ; then
echo “Error running command to insert!”
The -s will cause DB2 to stop execution and it will return non-zero for both errors (N) and warnings (W) from DB2. So you need to know what could run in each case and be aware of when you want to use -s and when you do not.
Thanks for the tip, Chris!
Thanks Ember for sharing this with us.
For those who are lucky to have AIX 🙂 they can use ‘p’ option (paragraph) of the ‘grep’ command and it will help them tremendously to filter paragraphs instead of just lines.
cat filename.sql.out | grep -vp DB20000I
I cannot tell you how many times I have wished I had the p option on grep on Linux.
grep -p like on Linux using awk:
Well after the discussion but still relevant I think.
You could search for only lines starting with SQL or DB with this.
grep -E “^(SQL|DB)” filename.sql.out
I have one query – related to suppressing warnings generated in html report through unix shell script. I am using db2 -txf for running (HTML – tagged SQL). The issue is In tabular report before the data – i m getting one column generated through HTML and having warning message like below SQL0437W Performance of this complex query might be sub-optimal. Reason code: “1”. SQLSTATE=01602- i want to either supress this warning or hide this warning from report. so that report can be shared ahead with business.
Thanks & Regards
DB2 UDB DBA