Checking the Output of SQL Scripts and Commands for Errors

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.