Application Development Guide

Error-Handling Considerations

In a partitioned environment, DB2 breaks up SQL statements into subsections, each of which are processed on the partition that contains the relevant data. As a result, an error may occur on a partition that does not have access to the application. This does not occur in a single-partition environment.

You should consider the following:

• Non-CURSOR (EXECUTE) non-severe errors
• CURSOR non-severe errors
• Severe errors
• Merged multiple SQLCA structures
• How to identify the partition that returned the error

If an application ends abnormally because of a severe error, indoubt transactions may be left in the database. (An indoubt transaction pertains to global transactions when one phase completes successfully, but the system fails before the a subsequent can complete, leaving the database in an inconsistent state.) For information on handling them, see the Administration Guide.

Severe Errors

If a severe error occurs in DB2 Universal Database, one of the following will occur:

• The database manager on the node where the error occurs shuts down.

  Active units of work are not rolled back.

  In this situation, you must recover the node and any databases that were active on the node when the shutdown occurred.

• All agents are forced off the database at the node where the error occurred.

  All units of work on that database are rolled back.

  In this situation, the database at the node where the error occurred is marked as inconsistent. Any attempt to access it results in either SQLCODE -1034 (SQLSTATE 58031) or SQLCODE -1015 (SQLSTATE 55025) being returned. Before you or any other application on another node can access the database at this node, you must run the RESTART DATABASE command against the database. Refer to the Command Reference for information on this command.

The severe error SQLCODE -1224 (SQLSTATE 55032) can occur for a variety of reasons. If you receive this message, check the SQLCA, which will indicate which node failed. Then check the db2diag.log file shared between the nodes for details. See Identifying the Partition that Returned the Error for additional information.

Merged Multiple SQLCA Structures

One SQL statement may be executed by a number of agents on different nodes, and each agent may return a different SQLCA for different errors or warnings. The coordinating agent also has its own SQLCA. In addition, the SQLCA also has fields that indicate global numbers (such as the sqlerrd fields that indicate row counts). To provide a consistent view for applications, all the SQLCA values are merged into one structure. This structure is described in SQL Reference.

Error reporting is as follows:

• Severe error conditions are always reported. As soon as a severe error is reported, no additions beyond the severe error are added to the SQLCA.
• If no severe error occurs, a deadlock error takes precedence over other errors.
• For all other errors, the SQLCA for the first negative SQLCODE is returned to the application.
• If no negative SQLCODEs are detected, the SQLCA for the first warning (that is, positive SQLCODE) is returned to the application. The exception to this occurs if a data manipulation operation is issued on a table that is empty on one partition, but has data on other partitions. The SQLCODE +100 is only returned to the application if agents from all partitions return SQL0100W, either because the table is empty on all partitions or there are no rows that satisfy the WHERE clause in an UPDATE statement.
• For all errors and warnings, the sqlwarn field contains the warning flags received from all agents.
• The values in the sqlerrd fields that indicate row counts are accumulations from all agents.

An application may receive a subsequent error or warning after the problem that caused the first error or warning is corrected. Errors are reported to the SQLCA to ensure that the first error detected is given priority over others. This ensures that an error caused by an earlier error cannot overwrite the original error. Severe errors and deadlock errors are given higher priority because they require immediate action by the coordinating agent.

Identifying the Partition that Returned the Error

If a partition returns an error or warning, its number is in the SQLERRD(6) field of the SQLCA. The number in this field is the same as that specified for the partition in the db2nodes.cfg file.

If an SQL statement or API call is successful, the partition number in this field is not significant.

For information about the SQLCA, see the SQL Reference.