Db2 Basics: Error Messages

When I do work in other domains – even something as connected as scripting – I find the error messages confusing and unhelpful. Most of the time, this is mostly because I don’t know much about the language or the the area. I’d like to think that my opinion that Db2 error messages are really quite helpful and useful is unbiased, but having built a career on focusing on IBM Db2, I’m sure I am biased.

There are some simple things that can really help understand Db2 error messages, and I thought I’d share them.

Structure of an Error Message

Error messages that users see from Db2 nearly always start with SQL. There are a few other three-letter prefixes such as DSN or DBI, but SQL is the most common one. I get the vague impression that some other RDBMSes or some developers rely more on the SQLSTATE than the error. But if I have a choice, the error is much more useful to me.

Sometimes applications log the error message number using a minus sign in front, like this:

Chained SQLException #1: Error for batch element #1: DB2 SQL Error: SQLCODE=-1476, SQLSTATE=40506, SQLERRMC=-911, DRIVER=4.17.29

In the case of the above, the error messages are both SQL1476N The current transaction was rolled back because of error sqlcode and the error SQL0911N – which is the error code that caused the rollback.

When reported in full, it is important to record the full error message because it often has other critical information like the object name the error occurred on or the reason code for the error. For example, the error message above:

SQL0911N  The current transaction has been rolled back because of a deadlock or timeout.  Reason code "68".  SQLSTATE=40001

means something technically different with a reason code of 2 vs. a reason code of 68.

Another example of this is when there is a permissions problem:

SQL0551N  "VDBA" does not have the required authorization or privilege to
perform operation "EXECUTE" on object "SYSPROC.MON_GET_TABLESPACE".
SQLSTATE=42501

This error message gives us the user id (VDBA), the type of privilege (EXECUTE), and the object name (SYSPROC.MON_GET_TABLESPACE). With those pieces of information, the error is much easier to resolve.

Let’s take a moment to look at the structure of the error message again. I already mentioned that the first three characters are nearly always SQL. After those three characters come normally 4, but on some occasions 5, numbers. At the end of every error message is a letter. The letter is always the same for a given error message. For example, SQL0911N will always have an N on the end. These letters actually tell you how severe the error is. The common values are:

• W – Warning. This means the statement was at least partially successful. Sometimes the “warning” simply notifies you of something that makes no difference to the result, while other times (like during a restore), it can mean some part of the command did not complete as expected.
• N – Statement Failure. This means that the statement did not have its desired effect and must be re-run after the issue is corrected. This usually only affects the current statement. Depending on how you are executing a file of statements or script of statements, statements after the failure may succeed.
• C – Serious System Failure. This means there is a very serious problem that may affect not just this statement or connection, but the server as a whole. If you have a DBA, involve them when you get an error code ending in C.

One of the most common statement failures is SQL0104N – it indicates that you have some sort of syntax error with the statement. It generally gives you more details on where in the statement the issue is:

$ db2 connect to db sample
SQL0104N  An unexpected token "sample" was found following "DB".  Expected 
tokens may include:  "USER".  SQLSTATE=42601
$ db2 connect to sample

Looking up Error Messages

There are two great locations where you can look up the official description on the error and get the official details on what different reason codes mean.

Command Line

A lot of details on an error message are available at the command line. Simply type db2 ? and the error number, like this:

$ db2 ? SQL0551N


SQL0551N  The statement failed because the authorization ID does not
      have the required authorization or privilege to perform the
      operation. Authorization ID: "". Operation:
      "". Object: "".

Explanation: 

The operation could not be performed on the specified object. In
general, this message is returned because the authorization ID does not
have the required authority or privilege to perform the operation. In
some cases, it is returned for an object that does not allow the
operation even when the authorization ID has an administrative
authority.

This message can be returned for different reasons. Here are some
examples of scenarios in which this message could be returned:

Scenario 1

         An attempt to create or alter a table that has referential
     constraints fails because the user does not have REFERENCE
     privileges. In this scenario, the value of the runtime token
     "" is "REFERENCES" and the value of the
     runtime token "" is the object that the
     constraint references.

Scenario 2

         Execution of a DB2 utility or CLI application fails because the
     user ID that created the database no longer exists or no longer
     has the privileges required to execute the DB2 utility program
     or CLI package.

Scenario 3

         Invocation of a routine fails because the user does not have
     EXECUTE privileges on any candidate routine in the SQL path. In
     this scenario, the value of the runtime token
     "" is the name of a candidate routine in
     the SQL path.

Scenario 4

         Creation of a routine fails because the user ID that was used
     to bind the routine does not have the privileges necessary to
     issue all the statements in the routine.

Scenario 5

         Replacing an existing object by using a CREATE OR REPLACE
     statement failed because the user is not the owner of the
     object.

Scenario 6

         An attempt to complete an online move table operation fails
     because the user does not have the same authorization ID as the
     user who initiated the online move table operation.

Scenario 7

         An attempt is made to perform an unsupported operation on a
     system-generated statistical view that is associated with an
     expression-based index. It is not supported for any user to
     modify the privileges of or to access this type of statistical
     view. However, users with adequate authority and privileges on
     the underlying table may update the statistics of this type of
     statistical view, either by issuing the RUNSTATS command or by
     manually updating the statistics.

In Federated environments:

         This message might be returned when the following actions fail
     because the user does not have the necessary authority:

         *  Altering one of the following user-mapping options:

        *  REMOTE_PASSWORD

        *  FED_PROXY_USER

        *  USE_TRUSTED_CONTEXT

     *  Altering a trusted user context

     *  Creating or dropping a user mapping that has the
        FED_PROXY_USER option or the USE_TRUSTED_CONTEXT option

     *  Creating or dropping a server that has the FED_PROXY_USER
        option

The required authorization can be at the federated server, at the data
source, or at both the federated server and the data source.

Some data sources do not provide appropriate values for the runtime
tokens "", "", and
"". In these cases, the message tokens might be
in one of the following formats:

*   AUTHID:UNKNOWN

*  UNKNOWNM

*   :TABLE/VIEW.

User response: 

If the authorization ID "" does not have the
required authority or privilege, grant the necessary privilege or
authority, if appropriate.

Here are responses for the scenarios described in the explanation
section of this message:

Scenario 1

         Grant the REFERENCES privilege by using the statement GRANT
     REFERENCES ON "" TO
     "".

Scenario 2

         Rebind the DB2 utility programs or CLI packages to the database
     by connecting to the database and then issuing one of the
     following CLP commands from the bnd subdirectory of the
     instance directory:

     *  For the DB2 utilities: DB2 bind @db2ubind.lst blocking all
        grant public

     *  For CLI packages: DB2 bind @db2cli.lst blocking all grant
        public

     If the user does not have EXECUTE privileges on the package,
     grant the EXECUTE privilege on the package, by use executing
     the GRANT statement.

Scenario 3

         Grant the EXECUTE privilege on the routine to the authorization
     ID by executing the GRANT statement.

Scenario 4

         Grant explicit privileges to the authorization ID on the
     objects that the statements within the routine are attempting
     to access.

Scenario 5

         Respond to scenario 5 in one of the following ways:

     *  Issue the statement as the user who is the owner of the
        object to be replaced.

     *  Transfer the ownership of the object by executing the
        TRANSFER OWNERSHIP statement.

Scenario 6

         Call the procedure again, using the same authorization ID as
     the user who initiated the online move table operation.

Scenario 7

         Specify a different object for the operation.

sqlcode: -551

sqlstate: 5UA0K, 42501


   Related information:
   Authorization
   TRANSFER OWNERSHIP statement
   Granting privileges
   Binding bind files after installing fix packs
   ALTER TABLE statement
   Authorizations and binding of routines that contain SQL
   Privileges on expression-based indexes

This isn’t even the longest one, by a long shot. Notice particularly the “User response” section that often suggests things to try to resolve the issue.

IBM Db2 Knowledge Center

The same information, plus a little more, is available in the IBM Db2 Knowledge Center. In the KC, you can link to related topics. Check out the same error information in the KC. You can also navigate to the message reference. This used to be a book in the shelf of reference books. You can use the left pane with the table of contents to navigate to specific error messages if search seems to be failing you.

There are, of course, other great resources available online. It is pretty rare that you encounter an actual bug in the Db2 product, but you can look up known issues (APARS).

Any IT professional develops a knack for googling and sifting through the junk for the diamonds. There are a number of bloggers and forums where you can find good information and practical advice. Just remember the poor schmo who truncated a table because of something he saw online, and then wondered where all the data went! (for the newbies, truncating a table means completely deleting all of the data)