Response times

Response time is the amount of time that Db2 requires to process an SQL statement. To correctly monitor response time, you must understand how it is reported.

The following figure shows how some of the main measures relate to the flow of a transaction, including the relationship between user response time, Db2 accounting elapsed times and Db2 total transit time.

Begin figure description. A diagram of events and class 1, 2, and 3 times on a vertical timeline from when a user presses enter to when a response is received. End figure description. — Figure 1. Transaction response times. Class 1 elapsed time includes application and Db2 elapsed times. Class 2 elapsed is the elapsed time in Db2. Class 3 is elapsed wait time in Db2. Standard accounting data is provided in IFCID 0003, which is turned on with accounting class 1. When accounting classes 2 and 3 are turned on as well, IFCID 0003 contains additional information about Db2 CPU times and suspension times.

End-user response time

End-user response time is the amount of time from the moment the end user presses the enter key until he or she receives the first response back at the terminal.

Db2 accounting elapsed times

Db2 accounting elapsed times are taken over the accounting interval. The start and end of the accounting interval differs based on factors such as the type of attachment facility, whether threads are reused, the value of the CMTSTAT subsystem parameter, and other factors. The elapsed times are collected in the records from the accounting trace and can be found in the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® accounting reports.

Table 1. Accounting interval boundaries for different thread types
Attachment facility or thread type	Accounting interval details
CICS® attachment facility	The accounting interval begins during thread creation processing. When CICS implements thread reuse, a change in the authorization ID or the transaction code initiates the sign-on process. This process terminates the accounting interval and creates the accounting record. TXIDSO=NO eliminates the sign-on process when only the transaction code changes. When a thread is reused without initiating sign-on, several transactions are accumulated into the same accounting record. The accumulated transactions can make it difficult to analyze a specific transaction occurrence and correlate Db2 accounting with CICS accounting. However, applications that use ACCOUNTREC(UOW) or ACCOUNTREC(TASK) in the DBENTRY RDO definition initiate a partial sign-on, which creates an accounting record for each transaction. You can use this data to tune your programs and to determine Db2 resource costs. For more information, see Relating Db2 accounting records to CICS performance class records.
IMS attachment facility	The accounting interval begins during thread creation processing. When an IMS thread is reused, the accounting interval ends and the accounting record is created at the next signon. When an IMS thread is not reused, the accounting interval ends at thread termination.
RRS attachment facility	The accounting interval begins during thread creation processing. The end of the accounting interval is controlled by the value specified for the `accounting-interval` area of the SIGNON function for RRSAF. For more information, see SIGNON function for RRSAF.
Distributed Data Facility threads	The accounting interval starts when the first SQL statement is received. The end of the accounting interval depends on the value of the CMTSTAT subsystem parameter, whether the connection can go inactive at the point of commit or rollback, and the ACCTG option specified in the MODIFY DFF command: CMTSTAT=INACTIVE If the default behavior of the ACCTG option of the MODIFY DDF command is in effect: If the thread can go inactive, the accounting interval ends at the point of commit or rollback. If the thread cannot go inactive for some reason, the accounting interval ends at the end of processing for the thread. If the thread cannot go inactive specifically because the KEEPDYNAMIC(YES) bind option is in effect for one of the packages, the accounting interval ends at the point of commit or rollback. If the behavior of ACCTG(ALLCOMMIT) option is in effect, the accounting interval ends after any commit or rollback. CMTSTAT=ACTIVE (deprecated) The accounting interval ends at the end of processing for the thread. The ACCTG(ALLCOMMIT) option of the modify DDF command is not supported with this setting. For more information, see: -MODIFY DDF command (Db2) DDF THREADS field (CMTSTAT subsystem parameter) Methods for keeping prepared statements after the point of commit or rollback KEEPDYNAMIC bind option
Other allied attached threads	The accounting interval begins during thread creation processing. The accounting interval ends at thread termination and includes a portion of the time spent terminating the thread.

The following types of elapsed times are provided:

Class 1 elapsed time: Class 1 elapsed time is the duration of the accounting interval. It includes time spent in Db2 as well as time spent in the front end. It is sometimes referred to as application time.
Class 2 elapsed time: Class 2 elapsed time is the time spent in the Db2 thread during the accounting interval. It represents the sum of the times from any entry into Db2 until the corresponding exit from Db2. It is also sometimes referred to as the time spent in Db2. It begins when the thread is created or, for reused threads, when another authorization ID signs on. It ends when the thread is terminated, or for reused threads, when another authorization ID signs on. This value is produced only when accounting class 2 trace is active. If class 2 trace is not active for the duration of the thread, the class 2 elapsed time does not reflect the entire Db2 time for the thread, but only the time when the class 2 trace was active.
Class 3 suspension time: Class 3 suspension time is the amount of wait time, which includes synchronous buffer pool I/O wait time, log I/O wait time, lock and latch wait time and other wait times.
Class 5 elapsed time: Class 5 elapsed time indicates the amount of elapsed time spent in Db2 processing instrumentation facility interface (IFI) requests. This time is included as part of the value for class 2 elapsed time.

Depending on your environment, additional considerations might be necessary. For example:

Parallelism requires special considerations for accounting.
Elapsed times for stored procedures, user-defined functions, or triggers are reported separately and are also included in the class 1 and class 2 total elapsed times.

Db2 class 2 not accounted time

Db2 class 2 not accounted time represents time that Db2 cannot account for. It is Db2 accounting class 2 elapsed time that is not recorded as class 2 CPU time or class 3 suspensions.

The following formula defines Db2 Class 2 Not Accounted Time when no parallelism is involved:

Db2 Class 2 Not Accounted Time = Db2 Class 2 Elapsed time -
(Db2 Class 2 CPU time + Db2 Class 3 suspension time)

Not-in-Db2 time

Not-in-Db2 time is the calculated difference between the class 1 and the class 2 elapsed time. This value is the amount of time spent outside of Db2, but within the Db2 accounting interval. A lengthy time can be caused by thread reuse, which can increase class 1 elapsed time, or a problem in the application program, CICS, IMS, or the overall system.

For distributed applications, not-in-Db2 time is calculated with the following formula:

Not-in-Db2 time = A - (B + C + (D - E))

Where the variables have the following values:

A is the class 1 elapsed time.
B is the class 2 non-nested elapsed time
C is the class 1 non-nested elapsed time of any stored procedures, user-defined functions, or triggers
D is class 1 non-nested CPU time
E is class 2 non-nested CPU time