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

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'"


  1 record(s) selected.

SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 0   sqlerrml: 0
 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:


SQLCA Information

 sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 605   sqlerrml: 20
 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.

Ember Crooks
Ember Crooks

Ember is always curious and thrives on change. She has built internationally recognized expertise in IBM Db2, spent a year working with high-volume MySQL, and is now learning Snowflake. Ember shares both posts about her core skill sets and her journey learning Snowflake.

Ember lives in Denver and work from home

Articles: 556


  1. 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!”
    exit 1

    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.

  2. 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


  3. 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

  4. Hello All,

    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
    Navin Natu

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.