There is a lot of information and technology for users of DB2 for Linux, Unix, and Windows to learn and master in order to be an effective DBA or SQL developer. At a minimum, you will need to understand relational database design, SQL, and the basics of DB2 like how to issue a command, how to use Control Center, and the like. But you will also need to know what to do when you are working with DB2 and you get an error message. What does that message mean? Is there more information than the terse pseudo-sentence that sometimes accompanies the message? How can I interpret this string of characters and numbers?

Most senior DB2 technicians and DBA can relate to the frustration of trying to decipher a cryptic error message. Such messages can cause novices to run screaming down the aisles "My application failed!", or even worse “There’s something wrong with DB2!” Armed with an understanding of the way DB2 delivers its error messages users can avoid panic because they will know what the message means, or will at least have the skills to discover what the message means.

Help is on the way! DB2 messages are always returned in the same format: CCCnnnnnS. The CCC identifies the DB2 component returning the message, the nnnnn is a four or five digit error code, and the S indicates the severity of the error. Table 1 identifies the possible DB2 components for the first three characters of the error message.

Table 1: DB2 Components

For example, consider the DB2 message SQL0289N. This component identifier is SQL, which means that this message came from the Database Manager. You can completely identify any DB2 return code message using the DB2 command line by separating the db2 command and the error code with a question mark:

C:\>db2 "? sql0289"
 SQL0289N Unable to allocate new pages in table space
          "<tablespace-name>".

This is a start. Sometimes, the information returned by this simple help message will be sufficient for you to resolve the problem and move along your merry way. But perhaps you need more help. This is when you will need to pull out your trusty IBM error manual; well, actually manuals is more like it. The manuals you will need is titled DB2 UDB Messages Reference, Volumes 1 & 2. These manuals can be easily obtained (if you do not already have copies) by downloading them from the IBM web site. Start at http://www 3.ibm.com/software/data/db2/library/ and follow the links to the appropriate flavor of DB2 you are using. There should be four links, 1) Linux, Unix, and Windows, 2) VM and VSE, 3) z/OS and OS/390, and 4) iSeries (AS/400).

The Messages Reference is broken down into separate chapters by component code. Find the proper component, and then within that chapter the messages are in numerical order. Currently, the second volume is dedicated to SQL messages, all other messages are in the first volume. So, for our SQL0289N message, we can refer to the second volume and obtain additional information including an in-depth explanation of the problem as well as possible user responses to rectify the problem. Doing so provides us with the information as shown in Figure 1.

SQL0289N Unable to allocate new pages in
table space ?<TABLESPACE-NAME>?.
Explanation: One of the following conditions is
true:
1. One of the containers assigned to this SMS table space has 
reached the maximum file size. This is the likely cause of the 
error.
2. All the containers assigned to this DMS table space are 
full. This is the likely cause of the error.
3. The table space object table for this DMS table space is 
full.
4. A rebalance is in progress, but has not progressed far 
enough to enable the newly added space to be used.
5. A redirected restore is being done to containers that are too 
small.
6. A rollforward is being done following a redirected restore 
and all the containers assigned to this tablespace are full.
7. A rollforward skipping add containers is being done and all 
the containers assigned to this tablespace are full.

Details can be found in the system error log and/or the 
database manager error log.

User Response: Perform the action corresponding to the 
cause of the error:
1. either switch to a DMS TABLESPACE or recreate the 
SMS TABLESPACE with more directories (PATHs) such 
that: (number of directories) >= (max tablesize / maxfilesize).
Note that maximum file size is operating system dependent.
2. add new container(s) to the DMS table space and try the 
operation again, after the rebalancer has made the new pages 
available for use. 
3. drop unnecessary tables from this DMS table space.
4. wait for the rebalancer to make more progress.
5. perform the redirected restore again to larger containers.
6. perform the redirected restore again to larger containers.
7. perform the rollforward again allowing add containers, or 
perform a redirected restore to larger containers.

sqlcode: -289

sqlstate: 57011

Figure 1: Sample error message detail

Armed with this additional information you are well on your way to resolving the problem. The manual has even pointed us to the most likely cause of this problem in point 1 of the explanation section (“One of the containers assigned to this SMS table space has reached the maximum file size. This is the likely cause of the error.”)

Now you need to fix the problem. The user response section provides guidance here. This particular error will likely require DBA assistance to resolve because it probably requires making changes to database structures. Of course, an error of this type probably means that you will not be the only application experiencing problems (unless, of course, your is the only application using this table space).

Or, maybe you’ll be lucky and point 4 under user response applies – in which case the “just run it again and see if it works now” approach (which usually does not work) could work! Of course, I do not recommend such an approach but we’ve all been there before and tried this approach – and heck, we try it because sometimes miracles do seem to happen, right?

But the whole point of this little article “Don’t rely on miracles, rely on messages and manuals!” IBM provides a plethora of valuable information to help us debug problem situations. Learning how to use that information will make you a more valuable DB2 user.

--

Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig has extensive experience in the field of database management having worked as an application developer, a DBA, and an instructor with multiple database management systems including DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at http://www.craigsmullins.com.