Introduction to troubleshooting problems in Db2 for z/OS

Messages and codes

One of the first tools that you typically use to troubleshoot a problem are the messages and codes that Db2 issues:

Error, warning, and informational messages

Db2 for z/OS message numbers begin with the prefix DSN. The message number is followed by some explanatory text. Depending on the message and the situation, messages are either displayed on the console, returned in the job output, or passed back to the application. Messages do not always indicate a problem. Some messages merely provide information about the state of the system and do not require a user response.

For more information about the various parts of a message number, see How to interpret message numbers.

If you receive messages from other IBM® products (messages that do not begin with DSN), use the following resource to find information for those messages: How to find additional information about messages.

Reason codes

Reason codes are typically presented as a value in a message that Db2 issues. Reason codes provide specific details about the circumstances of a particular instance of a message.

For information about the various parts of a reason code number, see How to interpret code numbers.

SQL codes

SQL codes are numbers that indicate the results of SQL statements. Positive and 000 SQL codes indicate that the SQL statement ran successfully. Negative SQL codes indicate an error.

The SQL code is passed to a structure called the SQL communication area (SQLCA). Your application can retrieve the SQL code value from the SQLCODE field in the SQLCA. The SQLCA also contains other information that is related to the SQL code, such as the SQLSTATE value, which also indicates whether the SQL statement was successful. Any variable values (message tokens) for the SQL code are also stored in the SQLCA.

For more information about SQL codes, SQLSTATE, and the SQLCA, see SQL codes.

One way to check the SQL code and other diagnostic information for SQL statements is to use the GET DIAGNOSTICS statement. See Checking the execution of SQL statements by using the GET DIAGNOSTICS statement .

Getting information from the Db2 catalog

The Db2 catalog is a set of tables that contain information about the data that Db2 controls. You can use the information in the catalog to troubleshoot problems.

For example, if Db2 returns an unexpected character or a CCSID conversion error, you can look at the CCSID information in the catalog. See Collecting data for CCSID problems.

If you receive messages about objects that Db2 cannot find, you can check the catalog to verify that those objects really exist in the catalog or to verify that they were specified correctly.

The information in the catalog can also be useful for diagnosing authorization problems. If you are using native Db2 security, which means that Db2 checks authorization, you can use SELECT statements against the Db2 catalog to confirm that the authorizations in question exist. If you are using external Db2 security, your security administrator can run reports that show which authorizations exist.

For a general description of the catalog, see Db2 catalog. For a detailed list of the various catalog tables, see Db2 catalog tables.

Troubleshooting for Db2

Troubleshooting for Db2 in the online product documentation contains detailed diagnostic information that can help you to diagnose problems in Db2.

Db2 Diagnosis Guide and Reference: The Db2 Diagnosis Guide and Reference, which was available as a licensed publication for earlier Db2 releases, is not available with Db2 12. However, the content is still available:

• Most of the information from the Db2 Diagnosis Guide and Reference is now available without restriction in Troubleshooting problems in Db2.
• The remaining information is available only for customers who have an Db2 12 for z/OS license associated with their IBMid. See Db2 12 for z/OS Licensed Diagnosis Information.

Monitoring threads

A thread is a structure that describes a connection made by an application and traces its progress in the Db2 subsystem. You can monitor threads by using the DISPLAY THREAD command. This command returns various information about threads, such as the connection name, the status, and the user. The information that is returned depends on the options that you specify.

For basic information about threads and the types of threads in a Db2 subsystem, see Monitoring threads.

The following list gives some examples of when you might want to monitor threads:

• If you suspect that DDF or Db2 might be hung, use DISPLAY THREAD to check for suspended threads. See the examples in -DISPLAY THREAD command (Db2).
• To help identify and resolve indoubt threads, see Scenarios for resolving problems with indoubt threads.
• To identify and recover from any indefinitely long wait conditions, see Recovering from an indefinite wait condition.
• To determine the status of resources that you are trying to access, see the examples 00C900AB and DSNU209I.
• To troubleshoot any messages about agents that do not exist at partner locations, see DSNL025I.
• To find a thread token so that you can cancel the thread, see -CANCEL THREAD command (Db2).

See Monitoring threads for detailed instructions. Be aware that the techniques for diagnosing problems with distributed threads are different from diagnosing problems with local threads. Details about the available techniques are in Diagnostic aids for distributed data and Diagnostic aids for single systems and data sharing.

Traces

A trace is a log of monitoring, auditing, performance, accounting, statistics, or serviceability data that is captured over a given time period. In Db2 for z/OS, the trace facility is called the instrumentation facility component (IFC). When you run a trace, the IFC gathers information about certain Db2 events. Each piece of information that is gathered has a numeric identifier. These identifiers are called IFCIDs. For example, IFCID 0305 records information that is related to defining, removing, and enforcing a table check constraint.

There are several types of Db2 for z/OS traces. A trace type is the type of information that IFC gathers, such as performance information or statistics information. Within each trace type is a number of trace classes. A trace class is a number that identifies a group of one or more IFCIDs.

For example, if you start performance trace class 1, the following IFCIDs are activated:

Similarly, if you start statistics trace class 1, the following IFCIDs are activated:

Notice that an IFCID can belong to more than one trace class.

For a description of all of the IFCIDs, view the contents of the DSNWMSGS file. For more information about DSNWMSGS and all of the information that it contains, see Trace field descriptions.

To run a trace, use the Db2 START TRACE command. (This command invokes the IFC to run a trace.) See -START TRACE command (Db2).

For more information about other commands that you can use to control traces, see Controlling Db2 trace data collection.

Each trace produces a number of trace records. Generally, you will use tools such as IBM OMEGAMON for Db2 Performance Expert on z/OS to interpret these trace records. However, you can analyze traces manually. For a description of Db2 trace output, see Db2 trace output.

Dumps

A dump is a copy of the contents of memory at a particular point in time. Dumps are a useful tool for diagnosing and debugging problems in Db2. Often, IBM Support will request that you generate a dump to help them understand the state of your system when a problem occurred.

For more information about the different types of dumps, see Dumps (z/OS basic skills).

Additional guidance about the types of Db2 dumps and analysis is in the Printing and analyzing dumps.

For instructions for generating a Db2 SVC dump, see Requesting Db2 SVC dumps.

If you concatenate the Db2 load library to the Interactive Program Control System (IPCS) startup procedures, you can analyze dumps yourself. For information about the IPCS commands to issue, see Format dumps by using IPCS options

You can also use the DSN1SDMP stand-alone utility to force a dump when certain trace events occur. For example, you might want to generate a dump for a particular negative SQL code. For an example of this situation, see example 4 in Sample DSN1SDMP control statements. For more information about DSN1SDMP, see DSN1SDMP.

Troubleshooting specific types of problems

• Debugging an application program
• Troubleshooting Db2 stored procedure problems
• Debugging CCSID and Unicode problems
• Problem diagnosis (ODBC)
• Investigating Db2 performance problems
• Troubleshooting the administrative task scheduler
• Investigating access path problems
• Steps for diagnosing a system hang (z/OS basic skills)

If you recently migrated to a new version of Db2 for z/OS, check for any release incompatibilities that might be the source of the problem. See Incompatible changes in Db2 12.

Fixes

If you suspect that the problem is with Db2 for z/OS, ask your system programmer to help you search for any available fixes. For Db2 for z/OS, a fix is called a PTF (program temporary fix). Potentially, someone else has already experienced the same problem and a PTF is available or will be available soon. In some cases, interim workarounds might be provided.

You can also search for authorized program analysis reports (APARs). An APAR is a formal problem report of a suspected defect in the product. An APAR can contain known workarounds and an indication of whether a PTF is planned.

You can search for both PTFs and APARs in the IBM Support Portal:Db2 for z/OS. For a list of keywords that you can use to optimize your search results when searching APAR text, see Db2 for z/OS Keywords for searching APAR text.

Contacting IBM Support

If you cannot solve the problem, contact IBM Support by using the following procedure: Contacting IBM Support about Db2 problems.

When you contact IBM Support, you can expect to send certain data, such as the following information:

• A complete SQLCA for a failing SQL statement
• A complete dump for a Db2 abend
• Service SQL documentation as described in Collecting service SQL documentation.
• SMF or RMF data for Db2 system performance problems
• Precompiler or compiler output for SQL issues that are related to program preparation
• Full Db2 error message text if a message is issued
• LOGREC and SYSLOG for abend or environmental issues

For instructions for gathering this data, see Collecting diagnostic data.