IBM OMEGAMON AI for Db2 accounting reports

You can obtain IBM OMEGAMON AI for Db2 reports of accounting data in long or short format and in various levels of detail.

The examples of IBM OMEGAMON AI for Db2 reports in this information are based on the default formats, which might have been modified for your installation. Furthermore, the IBM OMEGAMON AI for Db2 reports have been reformatted or modified for this publication. You can also time results for nested activities such as triggers, stored procedures, and user-defined functions.

Use the long format accounting report for detailed analysis when you have isolated a problem to a particular application.

When you analyze the long format accounting report, you might focus your initial investigation on the following components of response time. Figure 1 shows the arrangement of the following values in a sample report:

APPL(CL.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.
DB2 (CL.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.
IFI (CL.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.
CLASS 3 SUSPENSIONS TOTAL CLASS 3
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.
IRLM LOCK/LATCH
IRLM lock/latch suspension time is time spent waiting for locked resources, and latches that are used for internal serialization within IRLM.
DB2 LATCH
Db2 latch suspension time indicates wait time for latches that are acquired internally within Db2 for short term serialization of resources such as storage and control block changes.

If the Db2 latch suspension time is high, check the statistics report data that reports the frequency of the Db2 latch contentions and identify the Db2 latch classes (LCnn) (QVLSLC01 to QVLSLC32 and QVLSLC254) that have high rates. Look for tuning opportunities for any latches that exceed 10000 per second.

The following latch classes typically result in high contention:
LC06
Index leaf page split latch in data sharing.
LC14
Buffer pool lease recently used chain latch.
LC19
Log output buffer latch.
LC24
Prefetch latch or EDM least recently used chain latch.
SYNCHRON. I/O
Synchronous I/O suspension time is the total application wait time for synchronous I/Os. It is the total of database I/O and log write I/O. In the IBM OMEGAMON AI for Db2 accounting report, check the values for SYNCHRON. I/O, DATABASE I/O, and LOG WRITE I/O. Database I/O and log I/O are not reported separately at the package level.
OTHER READ I/O
Other read suspensions result from by waiting to read pages that already have I/O in progress. The reported value is the accumulated wait time for read I/O for threads other than this one. It includes time for:
  • Sequential prefetch
  • List prefetch
  • Dynamic prefetch
  • Synchronous read I/O performed by a thread other than the one being reported
OTHER WRITE I/O
Other write suspensions result from waiting to update pages that already have I/O in progress. The reported value is the accumulated wait time for write I/O for threads other than this one. It includes time for asynchronous write I/O and synchronous write I/O performed by a thread other than the one being reported
SER.TASK SWTCH
Service task suspension is the accumulated wait time from switching synchronous execution units, by which Db2 switches from one execution unit to another.
Wait times for the following activities are the most common contributors to service task suspensions:
  • Phase 2 commit processing for updates, inserts, and deletes (UPDATE COMMIT - QWACAWTE). This value includes wait time for Phase 2 commit Log writes and database writes for LOB with LOG NO. For data sharing environments, it includes page P-locks unlocks for updated pages and GBP writes.
  • The OPEN/CLOSE service task. You can minimize this wait time by using two strategies. If the threshold set by the value of the DSMAX subsystem parameter is frequently reached, increase the value of the DSMAX subsystem parameter. If this threshold is reached, change CLOSE YES to CLOSE NO on data sets that are used by critical applications.
  • The SYSLGRNG recording service task.
  • The Data set extend/delete/define service task (EXT/DEL/DEF). You can minimize this wait time by defining larger primary and secondary disk space allocation for the table space.
  • Other service tasks (OTHER SERVICE TASK). Contributors to the other service tasks suspensions are likely to include time spent on the network for outgoing allied threads over TCP/IP connections, VSAM catalog updates, and parallel query cleanup. Other contributors are possible. The performance trace of the following IFCIDs provide useful information when the OTHER SERVICE TASK value is high:
    • 0170 and 0171
    • 0046, 0047, 0048, 0049, with 0050, when more detail is needed.
PAGE LATCH
Page latch suspension indicates the accumulated wait time because of page latch contention.
Page latch contention can occur in highly concurrent insert environments. When a page is written to disk, multiple threads wait to update the same page, the first thread waits for other write I/O and other threads must wait for page latches. Longer page latch waits might occur if the disk writes are slower because of disk I/O performance issues. Page latch contention on data pages can occur during highly sequential updates to the same page from multiple threads. In a data sharing environment, high page latch contention might occur because of global locks for multithreaded applications that run on multiple members and use many insert, update, and delete operations.
  • If the suspension is on the index leaf page, use one of the following strategies:
    • Make the inserts random
    • Drop the index
    • Perform the inserts from a single member
    • Use a smaller index page size
  • If the page latch suspension is on a space map page, use the MEMBER CLUSTER option for the table space.
  • Activate and analyze the performance trace for IFCIDs 0226 and 0227 to analyze the page latch details.
The IBM OMEGAMON AI for Db2 lock suspension report shows this suspension for page latch contention in the "other" category.
APPL(CL.1)CPU TIME
Class 1 CPU times indicate the amount of time consumed in both Db2 and in applications on the central processor during the accounting interval.
DB2(CL.2) CP CPU TIME
Class 2 CPU times indicate the amount of time consumed in Db2 on the central processor during the accounting interval. It does not include application time.
IFI (CL.5) CP CPU TIME
Class 5 CPU time indicates the amount of time consumed on the central processor for processing instrumentation facility interface (IFI) requests during the accounting interval. This time is a subset of and included in the values for class 2 CPU time.
SE CPU
Specialty engine CPU time indicates CPU time consumed on the specialty engines (zIIP and zAAP). This time is not included in the class 1 or class 2 CPU times.
NOT ACCOUNT
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.
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

The following figure shows part of an example long format accounting report, including the arrangement of the fields that are described here.

Figure 1. Partial accounting report (long format)
AVERAGE       APPL(CL.1)  DB2 (CL.2)  IFI (CL.5)    CLASS 3 SUSPENSIONS   AVERAGE TIME  AV.EVENT    HIGHLIGHTS
------------  ----------  ----------  ----------    --------------------  ------------  --------    --------------------------
ELAPSED TIME    0.136869    0.022632    0.000429    LOCK/LATCH(DB2+IRLM)      0.000192      0.14    #OCCURRENCES    :  1568491
 NONNESTED      0.124535    0.010763         N/A     IRLM LOCK+LATCH          0.000188      0.04    #ALLIEDS        :        0
 STORED PROC    0.012321    0.011859         N/A     DB2 LATCH                0.000004      0.11    #ALLIEDS DISTRIB:        0
 UDF            0.000002    0.000000         N/A    SYNCHRON. I/O             0.010347      6.38    #DBATS          :  1474051
 TRIGGER        0.000010    0.000010         N/A     DATABASE I/O             0.009948      6.18    #DBATS DISTRIB. :    94440
                                                     LOG WRITE I/O            0.000399      0.19    #NO PROGRAM DATA:        0
CP CPU TIME     0.004499    0.004212    0.000333    OTHER READ I/O            0.003111      3.52    #NORMAL TERMINAT:       29
 AGENT          0.004499    0.004212         N/A    OTHER WRTE I/O            0.000002      0.00    #DDFRRSAF ROLLUP:    82834
  NONNESTED     0.002150    0.002102    0.000333    SER.TASK SWTCH            0.000291      0.05    #ABNORMAL TERMIN:        0
  STORED PRC    0.002345    0.002107         N/A     UPDATE COMMIT            0.000014      0.01    #CP/X PARALLEL. :        0
  UDF           0.000001    0.000000         N/A     OPEN/CLOSE               0.000120      0.01    #UTIL PARALLEL. :        0
  TRIGGER       0.000003    0.000003         N/A     SYSLGRNG REC             0.000004      0.00    #IO PARALLELISM :     1402
 PAR.TASKS      0.000000    0.000000         N/A     EXT/DEL/DEF              0.000015      0.00    #PCA RUP COUNT  :      N/A
                                                     OTHER SERVICE            0.000139      0.03    #RUP AUTONOM. PR:      N/A
SE CPU TIME     0.003034    0.002970         N/A    ARC.LOG(QUIES)            0.000000      0.00    #AUTONOMOUS PR  :      N/A
 NONNESTED      0.003032    0.002968         N/A    LOG READ                  0.000000      0.00    #INCREMENT. BIND:       40
 STORED PROC    0.000002    0.000002         N/A    DRAIN LOCK                0.000001      0.00    #COMMITS        :  1650053
 UDF            0.000000    0.000000         N/A    CLAIM RELEASE             0.000000      0.00    #ROLLBACKS      :     1457
 TRIGGER        0.000000    0.000000         N/A    PAGE LATCH                0.000002      0.16    #SVPT REQUESTS  :        0
                                                    NOTIFY MSGS               0.000002      0.00    #SVPT RELEASE   :        0
 PAR.TASKS      0.000000    0.000000         N/A    GLOBAL CONTENTION         0.000191      0.11    #SVPT ROLLBACK  :        0
                                                    COMMIT PH1 WRITE I/O      0.000000      0.00    MAX SQL CASC LVL:        3
SUSPEND TIME    0.000117    0.014248         N/A    ASYNCH CF REQUESTS        0.000038      0.52    UPDATE/COMMIT   :     1.54
 AGENT               N/A    0.014248         N/A    TCP/IP LOB XML            0.000071      0.05    SYNCH I/O AVG.  : 0.001623
 PAR.TASKS           N/A    0.000000         N/A    ACCELERATOR               0.000000      0.00
 STORED PROC    0.000116         N/A         N/A    AUTONOMOUS PROCEDURE           N/A       N/A
 UDF            0.000002         N/A         N/A    PQ SYNCHRONIZATION             N/A       N/A
                                                    TOTAL CLASS 3             0.014248     10.94
NOT ACCOUNT.         N/A    0.001201         N/A
DB2 ENT/EXIT         N/A        8.33         N/A
EN/EX-STPROC         N/A       46.96         N/A
EN/EX-UDF            N/A        0.00         N/A
DCAPT.DESCR.         N/A         N/A    0.000000
LOG EXTRACT.         N/A         N/A    0.000000