DB2 LUW Error Messages are some of the more descriptive ones I have seen in the computing world, but maybe everyone thinks that about their chosen specialty. Truly understanding error messages can take a bit of practice and knowledge, so I thought I would share a few tricks and things that can make it easier.
In DB2, each executed statement returns a wealth of information. This information is called the SQLCA or SQL Communications Area. Each application, including the DB2 command line, processes this information with varying levels of precision. If you are building an application, always place as much of this data in application logs as possible when a failure occurs. Some applications will note or return incomplete error information, which makes troubleshooting an exercise in frustration. The full structure and use of the SQLCA is beyond the scope of this post. The DB2 command line gives you the critical pieces of information by default.
Errors vs. Warnings vs. System Errors
First, it is important to understand the structure of the SQL error message itself. SQL error messages are returned to standard output when a DB2 command is run that generates an error.
SQL Code
The SQL Code looks like this:
SQL0000X
Where the 0’s are replaced by any digits. There are almost always four digits, but for a few error codes, there may be 5 digits. The final character (represented by X above) is a letter – one of the following:
- W – Warning – The statement completed, but there was some issue that could mean that everything is OK or that the statement partially failed.
- N – Error – The execution of this statement failed, though the error may be isolated to this statement.
- C – Severe System Error – Not only did the execution of this statement fail, but a system error was encountered indicating other failures are likely and other users may be impacted.
Most of us think of the error code as the 4-digit number portion of this error message. You will not see the same 4-digit number with different ending characters.
SQL Message and Reason/Return Code (RC)
In addition to the SQL code, errors include a text description of the error, and often one or more RCs (Return or Reason Codes). Also included may be the names of objects in the database that this particular error is related to. All of this information is critical. While the bulk of the error message will not change from time to time, those RCs and object names will point DB2 DBAs straight to the issue in many cases. When I ask someone for the error they received, I want as much information as possible from this.
SQLSTATE
The one part of the error returned that I have never found any use for is the SQLSTATE. I would much rather have the full text and SQL code of the error. There is some detailed information in the IBM DB2 Knowledge Center if you want to try to make use of the SQLSTATE.
Looking Up Additional Information at the Command Line
Sometimes all we get is the numeric portion of the SQL error code. If that is all we have, the first thing to know is that positive numbers represent warnings while negative numbers represent errors. 0000 would represent successful completion of a statement. If all you have is a number, you often need more information. I only have a handful of error messages memorized, even after over 14 years as a DB2 DBA. I am frequently looking up an SQL Code to get the more verbose description. This can be done at a command line on Linux/UNIX or a command window on MS Windows like this:
$ db2 ? SQL1024N
SQL1024N A database connection does not exist.
Explanation:
There is no connection to a database. Other SQL statements cannot be
processed unless an SQL CONNECT was previously executed.
The command cannot be processed.
User response:
If the error occurred when disconnecting from the database, continue
processing. If the error occurred on another SQL statement, issue an SQL
CONNECT statement and resubmit the command or statement.
sqlcode: -1024
sqlstate: 08003
The SQL Code must be prefixed with ‘SQL’ when getting this informaiton. No database connection is required – simply a user with the DB2 environment correctly set up. The trailing character (N, C, or W) is optional, in case you don’t happen to know it. The above is about the simplest output for an error message possible. Most error messages will list both possible causes and user responses based on the various possible reason codes.
Looking Up Additional Information Online
As always, the IBM DB2 Knowledge Center has some great technical information if you know where to look. One way to find error information is simply to search on an error code. The full SQL message reference is also available. Using either method, you should be able to get to a page that looks like this:
This page contains very similar information to what is available at the command line. Read the text very carefully – most error messages have very specific suggestions on what you should try to resolve the issue.
Java Client Error Messages
SQL error codes with numeric components between 4200 and 4299 or between 4450 and 4499 are reserved for the JDBC or SQLJ drivers. They are therefore largely client-side messages, though they can occasionally be a symptom of a server problem. You can find more details on the various codes on the IBM DB2 Knowledge Center page on the error codes issued by the Data Server Driver for JDBC and SQLJ.
Other Prefixes
There are other prefixes besides ‘SQL’ that can start out a message, but they are usually messages that are recorded in other locations. In the DB2 diagnostic log, for example, you may see errors that start with ADM. Details about these messages can be searched in the IBM DB2 Knowledge Center.
Finding Messages in DB2 Diagnostic Log
More severe or system type messages are also recorded in the DB2 diagnostic log. Depending on your DIAGLEVEL, different amounts of information are recorded. Reading the DB2 diagnostic log is something of an art, but the db2diag tool can help with it. If you have a specific error message you’re looking for you can use something like this to find all occurrences of it in the DB2 diagnostic log:
db2diag -e 3304 -fmt '%ts %msg'
2015-12-03-11.53.44.098997 Table type error forcing LOAD to quit , -3304, (nil), Detected in
file:sqlulval.C, Line:2395
2015-12-03-11.53.50.793515 Table type error forcing LOAD to quit , -3304, (nil), Detected in
file:sqlulval.C, Line:2395In the above example, we’re looking for occurrences of SQL3304N in the DB2 diagnostic log.