Measuring DB2 response time

To correctly monitor DB2 response time, you must understand how it is reported. Figure 66 shows how some of the main measures relate to the flow of a transaction.

Figure 66. Transaction response times

These times can be distinguished:

• User response time

  This is the response time, from the moment the user presses the Enter key until the first response appears back at the terminal.

• DB2 accounting elapsed times

  These times are collected in the accounting records. They are taken over the accounting interval between the point where DB2 starts to execute the first SQL statement, and the point preceding thread termination or reuse by a different user (signon). The interval excludes the time spent in creating and terminating a thread.

  DB2 records these elapsed times:

  Class 1 elapsed time

  This time is always presented in the accounting record and shows the duration of the accounting interval. It includes the time spent in DB2 and time spent in the front end.

  Class 2 elapsed time

  Class 2 elapsed time, produced only if the accounting class 2 trace is active, counts only the time spent in the DB2 address space 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 referred to as the time spent in DB2. If class 2 is not active for the duration of the thread, the class 2 elapsed time may not reflect the entire DB2 time for the thread, but only the time when the class 2 trace was active.

  Class 3 elapsed time

  Class 3 elapsed time, produced only if the accounting class 3 trace is active, is elapsed wait time in DB2. Possible causes of wait time include:

  • Synchronous I/O suspension time
  • Lock/latch suspension time
  • Asynchronous read suspensions
  • Asynchronous write suspensions
  • Service task suspensions
  • Archive log mode time

• DB2 total transit time

  Regarding an SQL transaction or query, the total transit time is the elapsed time from the beginning of create thread, or the signon of another authorization ID when reusing the thread, until either the end of the thread termination, or the signon of another authorization ID.

  When a thread is reused, the transit time may include idle time. Unlike the accounting class 1 and 2 times, it includes time spent in create and terminate thread.

Figure 67 shows the breakdown of elapsed times:

Figure 67. Accounting response time diagram

The elapsed time spent in each of the processing areas provides the basis for beginning the tuning of an application. You can determine the IMS/CICS transaction elapsed time from reports created by the CICS Performance feature and the IMS Performance feature of Tivoli Decision Support for z/OS. You can determine the accounting class 1, class 2, and the total transit time from the DB2 component.

To have correct average times, accounting class 2 and class 3 traces must be active for the entire accounting period. If these classes are started and stopped within an application run, DB2 records only the time when the classes are active, thereby creating a discrepancy with the real DB2 times.

You can analyze the different areas of elapsed times, make comparisons, and determine the possible problem area more easily.

[ Top of Page | Previous Page | Next Page | Contents ]