Times (class 3)—suspensions (BACCTDR)

LOCK

The lock time

LATCH

Wait time due to latch contention (QWACAWLH, QWACARNL)

SYNC I/O

Wait time for synchronous I/O (QWACAWTI)

In the DB2 Accounting CPU/Elapsed Time Report, this value is the average elapsed time waiting for I/O to complete in seconds per plan execution. This value indicates the elapsed time spent waiting for all synchronous I/O. Accounting class 3 must be activated to determine this value.

Tuning Tip: Most of the time included in this value is synchronous read time, but under stress conditions it can reflect synchronous write time (when immediate write threshold is reached). During heavy update activity, it also can reflect wait for synchronous log I/O.

SYSLGRNG REC

Accumulated wait time for a synchronous execution unit switch to the Db2 SYSLGRNG recording service (QWAXSLSE) and the number of wait trace events processed for waits for synchronous execution unit switching to the SYSLGRNG recording service (QWAXSLNS)

This service is also sometimes used for level ID checking for down-level detection.

EXT/DEL/DEF

Accumulated wait time for a synchronous execution unit switch to the Db2 data space manager services (QWAXDSS)

This value includes DEFINE DATA SET, EXTEND DATA SET, DELETE DATA SET, and RESET DATA SET. This value is an average.

OTHER READ

Accumulated waiting time due to a read I/O that performed under a thread other than the one being reported (QWACAWTR)

OTHER WRITE

wait time for write I/O (QWACAWTW)

This value is the time spent waiting for an asynchronous write I/O to complete or for synchronous write I/O to be performed for another thread.

Tuning Tip: Time reflected here should be small, because normal checkpoint and deferred write thresholds protect threads from waiting for asynchronous write time. If this number is large, the buffer pool size or deferred write threshold might need to be adjusted. Accounting class 3 must be activated to obtain this time.

SER.TASK SWTCH

Wait time for Db2 services

This value is the time spent waiting for special Db2 services. A synchronous execution unit switch is done from the thread to one of the Db2 service tasks. Service times reported here are a summary of each of the following wait values. Accounting class 3 must be activated to obtain these times:

• — COMMIT/UPDATE (QWACAWTE)
• — OPEN/CLOSE (QWAXOCSE)
• — SYSLNG REC (QWAXSLSE)
• — DEFINE/EXTEND/DELETE (QWAXDSSE)
• — OTHER (QWAXOTSE)

ARC.LOG(QUIES)

Wait time for log quiesce (QWACALOG or QWAXALOG)

This value is the time spent waiting for an -ARCHIVE LOG MODE(QUIESCE) to take place. This process externalizes buffers and switches the active log data set. Time reflected here is the time the thread waited not the time for the command to complete. Accounting class 3 must be activated to obtain this time.

ARC.LOG READ

Wait time for archive read from tape (QWACAWAR or QWAXAWAR)

This value is the amount of time spent waiting for reads from an archive tape. Time spent waiting for an archive tape is either for the completion of the RECOVER utility or for an application program, which has not committed, to read the archive tape in order to complete backout. Accounting class 3 must be activated to obtain this time.

Tuning Tip: If this time is large, the application program should commit more often. It might also make sense to increase the size and number of active logs.

STOR.PRC SCHED

Wait time for SPAS TCB scheduling (QWACCAST)

This time is the elapsed time spent waiting for an available TCB in the Stored Procedures Address Space to become available for scheduling a stored procedure.

Tuning Tip: This time should be minimal depending on the workload to the Stored Procedures Address space. It is calculated only if accounting class 3 is active.

UDF SCHEDULE

Total elapsed time spent for user-defined functions to execute SQL (QWACUDEB)

This number is calculated only if accounting class 3 is active.

DRAIN LOCK

Wait time for drain lock (QWACAWDR or QWAXAWDR)

This value is the time spent by a requester of a drain lock, such as a utility or command, waiting to acquire that lock. Processes, such as utilities that serialize events to page sets, must acquire a drain lock. This value is the time spent waiting for this event. Accounting class 3 must be activated to obtain this time.

CLAIM RELEASE

Wait time for claim release (QWACAWCL or QWAXAWCL)

This value is the time spent by the holder of a drain lock waiting for SQL claimers to complete their activity. Drainers must wait until the SQL claim count reaches zero. This value is the time spent waiting for them to clear. Accounting class 3 must be activated to obtain this time.

PAGELATCH

Wait time for page latch contention (QWACAWTP)

This value is the time spent waiting for page latching activities due to lock avoidance. Accounting class 3 must be activated to obtain this time.

Tuning Tip: Page latching can be avoided by scheduling applications when there is a lower likelihood of latch contention.

NOTIFY MSGS

Wait time for data sharing notify messages (QWACAWTG)

This value is the wait time for sending notify messages to another data sharing member. When tables in a data sharing group are CREATed, ALTERed, or DROPped in a data sharing group, intersystem messages must be sent to update the database descriptors (DBDs) in the other members’ EDM pools. This value is the time spent waiting for this message traffic to occur. This time is collected only when accounting class 3 is activated and data sharing is enabled.

GLOBAL CONT

Wait time for global lock contention (QWACAWTJ)

This time is the amount of elapsed time caused by an IRLM lock suspension due to global lock contention which requires intersystem communication to resolve. This time is collected only when accounting class 3 is activated and data sharing is enabled.

FORCE-AT-COMM

Accumulated wait time for phase 1 commit write I/O (QWAXAWFC)

ASYNCH IXL RQ

Accumulated wait time for IXLCACHE and IXLFCOMP requests (QWAXIXLT)

DATABSE I/O

Accumulated I/O elapsed wait time for database I/O for this thread. This field is for synchronous I/O only (QWACAWTI)

This value includes synchronous read and write I/O. This value is an average.

LOG/WRT I/O

Accumulated wait time for log write I/O (QWACAWLG)

TCP/IP LOB

Accumulated wait time for TCP/IP LOB materialization (QWACALBW)

CMT PH1 WRT

Accumulated time waiting for phase 1 commit write I/O (QWAXAWFC)