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