How is DB2 CPU time accounted for?

The most common bottleneck, and the one most directly affected by application design, is CPU time. It is important to understand the way DB2 collects CPU time statistics, the difference between TCB and SRB time, as well as the difference between application time and DB2 CPU time.

DB2 uses TCBs and SRBs in its own address spaces as well as in application address spaces to do its work. For example, when an application issues an SQL call, execution of the SQL call switches from the application's address space to code in the DB2 data base services address space. However, execution continues under control of the application's (aka the thread's) TCB, NOT a database services' TCB. It is for this reason that DB2 provides CPU times for the TCB and SRB time spent IN APPLICATION, as well as its own address spaces. Therefore, if there is a problem with high CPU time in a CICS region, it may not be CICS proper, but rather DB2 acting under the application's (CICS transaction's) TCB, thusly, showing up under the CICS region as CPU time.

Usually, the time spent executing the thread's SQL statements is the largest consumer of DB2 TCB CPU time. DB2 processing accomplished by executing DB2 code under the thread's TCB include:

• SQL Processing
• Synchronous data base reads and writes
• Lock Request, Acquisition, and Release
• Part of the Create Thread function
• Logical logger

The following tasks are executed under a DB2 TCB in one of the DB2 address spaces:

• VSAM OPEN/CLOSE (Data Base Services TCB)
• Archiving (System Services TCB)
• BSDS Accesses (System Services TCB)

SRBs are used for processing asynchronous work and incur less overhead, but are limited. SRB time is not useful for tuning applications, but will be useful for tuning DB2 address spaces. DB2 makes use of SRBs for several functions where very high performance and low overhead is required - they are:

• Sequential and List Prefetch (Data Base Services SRB)
• Asynchronous database writes (Data Base Services SRB)
• Part of Create Thread (Data Base Services SRB)
• Space Management (Data Base Services SRB)
• Physical Logging (Master SRB)
• Checkpoint (Master SRB)
• Lock Resumption (IRLM SRB)
• Deadlock Detection (IRLM SRB)

RMF reports on DB2 address space CPU times WILL NOT include the time that DB2 spent executing DB2 code under the threads' TCBs. For a true measure of DB2 CPU, one must use the total of the threads' TCB CPU plus the DB2 address spaces' CPU time.