Viewing DB2 Console Messages

Another way to monitor DB2 performance is to view the DB2 console messages for the active DSNMSTR address space. You can obtain a wealth of statistics from this log.

To view DB2 console messages, you must be able to view the DSNMSTR region either as it executes or, for an inactive DB2 subsystem, from the spool. Most shops have a tool for displaying the outlist of jobs that are executing or have completed but remain on the queue. An example of such a tool is IBM's SDF.

Using your outlist display tool, select the DSNMSTR job. (This job may have been renamed at your shop to something such as DB2TMSTR or DB2MSTR .) View the JES message log, which contains DB2 messages that are helpful in determining problems.

Information in the DB2 message log can help you monitor many situations. Several examples follow.

When you first view the console messages, a screen similar to Figure 24.5 is displayed. In the DB2 start-up messages, look for the DSNZ002I message code. It shows you the DSNZPARM load module name that supplied DB2 with its start-up parameters. From this first part of the DB2 console log, you also can determine the following:

• The time DB2 was started (in the example, 18:01:52)

• The name of the Boot Strap Data Set (BSDS) and associated information

• The name of the active log data sets and associated log RBA information

Figure 24.5. DB2 console messages.

Sometimes, when DB2 performs a log offload, the overall performance of the DB2 subsystem suffers. This outcome can be the result of the physical placement of log data sets and DASD contention as DB2 copies data from the active logs to archive log tapes and switches active logs.

In Figure 24.6, find the DB2 message DSNJ002I , which indicates the time an active log data set is full (10:25:21 in the example). The DSNJ139I message is issued when the log offload has completed successfully (10:26:47 in the example). This efficient log offload required a little more than one minute to complete. If users complain about poor performance that can be tracked back to log offload periods, investigate the DASD placement of your active logs. Specify multiple active logs, and place each active log data set on a separate DASD device. As an additional consideration, think about caching the DASD devices used for DB2 active logs.

Figure 24.6. Log offloading .

Resource unavailable messages are also in this message log. You can find them by searching for DSNT501I messages. For example, refer to the portion of the log displayed in Figure 24.7. It shows a resource unavailable message occurring at 10:17:26 . From this message, you can determine who received the unavailable resource message (correlation-ID), what was unavailable, and why. In this case, a tablespace was unavailable for reason code 00C900A3 , which is a check pending situation. (As you can see by scanning further messages in the log, the check pending situation is cleared up approximately four minutes later.)

Figure 24.7. Resource unavailable.

Another area that requires monitoring is locking contention. When a high degree of lock contention occurs in a DB2 subsystem, you get many timeout and deadlock messages. Message code DSNT375I is issued when a deadlock occurs, and DSNT376I is issued for every timeout. Figure 24.8 shows two examples of timeouts due to lock contention. You can determine who is timing out, who holds the lock that causes the timeout, and what resource has been locked so that access is unavailable. In the example, the DSNDB01.DBD01 DB2 Directory database is locked, probably due to the concurrent execution of DDL by the indicated correlation-ID.

Figure 24.8. Locking contention and timeouts.

The final monitoring advice in this section concentrates on two internal plans used by DB2: BCT (Basic Cursor Table) and BINDCT . DB2 uses the BCT plan to issue commands. For example, assume that you issue a -STOP DATABASE command, but the database cannot be stopped immediately because someone is holding a lock on the DBD. The database is placed in stop pending ( STOPP ) status, and DB2 continues issuing the command using the BCT plan until it is successful.

In Figure 24.9, the BCT plan is timed out at 14:58:26 and then again at 14:59:41. This timeout occurred because an attempt was made to issue -STOP DATABASE while another job was issuing DDL for objects in the database. The BCT plan tries to stop the database repeatedly until it succeeds.

Figure 24.9. The BCT plan.

DB2 uses the BINDCT plan to bind packages and plans. If users have problems binding, the cause of the problem can be determined by looking in the log for occurrences of BINDCT . In the example in Figure 24.10, the bind failed because someone was using a vendor tool that held a lock on the DB2 Catalog. Because the BIND command must update the DB2 Catalog with plan information, the concurrent lock on the catalog caused the BIND to fail.

Figure 24.10. The BINDCT plan.

The situations covered here are a few of the most common monitoring uses for the DB2 console message log. Look for corroborating evidence in this log when you're trying to resolve or track down the cause of a DB2 problem.