Accounting information provided by the DB2 accounting facility

The DB2® accounting facility provides detailed statistics on the use of DB2 resources by CICS® transactions. You can use DB2 accounting records as the basis for the accounting and tuning of DB2 resources used by CICS transactions. Data types in DB2 accounting records discusses the types of data in DB2 accounting records.

DB2 gathers accounting data on an authorization ID within thread basis. When requested, the accounting facility collects this data and directs it to SMF, GTF, or both when the thread is terminated or when the authorization ID is changed. For information about activating the DB2 accounting facility and directing the output to SMF and GTF, see Starting SMF for DB2 accounting, statistics and tuning, and Starting GTF for DB2 accounting, statistics and tuning. See the DB2 Universal Database™ for OS/390® and z/OS® Administration Guide for information on the general structure of DB2 SMF and GTF records.

The identification section of each DB2 accounting record written to SMF and GTF provides a number of keys on which the data can be sorted and summarized. These include the authorization ID, the transaction ID, the plan name, and the package name.

The DB2 Performance Monitor (DB2PM) program product provides accounting reports taken from the DB2 accounting records. DB2 accounting reports shows examples of these reports.

Data types in DB2 accounting records

The DB2 Universal Database for OS/390 and z/OS Administration Guide has details on the individual fields in the DB2 accounting record. This section provides an overview of the different types of data in DB2 accounting records.

The following data types can be used for accounting:

You have several possibilities for defining a cost formula based on the DB2 accounting records.

Processor usage

The processor usage information given in the DB2 accounting record shows in most cases the greater part of the total processor time used for the SQL calls. The DB2 statistics records report processor time used in the DB2 address spaces that could not be related directly to the individual threads.

You should consider distributing the processor time reported in the DB2 statistics records proportionally between all users of the DB2 subsystem (transactions, batch programs, TSO users).

The amount of processor time reported in the DB2 accounting records is (for the same work) relatively repeatable over time.

See Accounting for processor usage in a CICS DB2 environment for more detail on reporting processor usage in a CICS DB2 environment.

I/O

In a DB2 system, the I/O can be categorized in these types:

Of these five I/O types, only the synchronous read I/O is recorded in the DB2 accounting record.

The number of sequential prefetch read requests is also reported, but the number of read requests is not equal to the number of I/O.

None of the I/O types should be considered as repeatable over time. They all depend on the buffer sizes and the workload activity.

DB2 is not aware of any caches being used. That means that DB2 reports an I/O occurrence, even if the cache buffer satisfies the request.

GETPAGE

GETPAGE represents a number in the DB2 accounting record that is fairly constant over time for the same transaction. It shows the number of times DB2 requested a page from the buffer manager. Each time DB2 has to read or write data in a page, the page must be available, and at least one GETPAGE is counted for the page. This is true for both index and data pages. How often the GETPAGE counter is incremented for a given page used several times depends on the access path selected. However, for the same transaction accessing the same data, the number of GETPAGEs remains fairly constant over time, but the GETPAGE algorithm can change between different releases of DB2.

If the buffer pool contains the page requested, no I/O occurs. If the page is not present in the buffer, the buffer manager requests the page from the media manager, and I/O occurs.

The GETPAGE number is thus an indicator of the activity in DB2 necessary for executing the SQL requests.

Write intents

The number of set write intents is present in the QBACSWS field of the DB2 accounting record, but the number is not related to the actual number of write I/Os from the buffer pools. The number represents the number of times a page has been marked for update. Even in a read-only transaction this number can be present, because the intended writes to the temporary work files used in a DB2 sort are also counted.

The typical case is that the number of set write intents is much higher than the number of write I/Os. The ratio between these two numbers depends on the size of the buffer pool and the workload. It is not a good measurement for write I/O activity, but does indicate the complexity of the transactions.

SQL call activity

The number and type of SQL calls executed in a transaction are reported in the DB2 accounting record. The values are repeatable over time, unless there are many different paths possible through a complex program, or the access path changes. The access path chosen can change over time (for example by adding an index).

A given SQL call can be simple or complex, depending on factors such as the access path chosen and the number of tables and rows involved in the requests.

The number of GETPAGEs is in most cases a more precise indicator of DB2 activity than the number of different SQL calls.

Transaction occurrence

A straightforward way of accounting is to track the number and type of transactions executed. Your accounting is then based on these values.

Storage

The DB2 accounting record does not contain any information about real or virtual storage related to the execution of the transactions. One of the purposes of the DB2 subsystem is to optimize the storage use. This optimization is done at the DB2 level, not at the transaction level.

A transaction uses storage from several places when requesting DB2 services. The most important places are the thread, the EDM pool, and the buffer pools.

Because no information is given in the DB2 accounting record about the storage consumption and because the storage use is optimized at the subsystem level, it is difficult to account for storage in a DB2 environment.

DB2 accounting reports

The DB2 Performance Monitor (DB2PM) program product provides accounting reports taken from the DB2 accounting records. Figure 38 and Figure 39 show examples of long and short accounting reports for a CICS transaction accessing DB2 resources.

Figure 38. Accounting long report for a CICS transaction accessing DB2 resources
   LOCATION: DSN710P2                          DB2 PERFORMANCE MONITOR (V7)                               PAGE: 1-1
      GROUP: DSN710P2                            ACCOUNTING REPORT - LONG                       REQUESTED FROM: NOT SPECIFIED
     MEMBER: DF2D                                                                                           TO: NOT SPECIFIED
  SUBSYSTEM: DF2D                                 ORDER: PRIMAUTH-PLANNAME                       INTERVAL FROM: 11/05/01 10:42:31.25
DB2 VERSION: V7                                       SCOPE: MEMBER                                         TO: 11/05/01 10:51:03.70

PRIMAUTH: JTILLI1  PLANNAME: DSNJDBC

ELAPSED TIME DISTRIBUTION                                           CLASS 2 TIME DISTRIBUTION
----------------------------------------------------------------    ----------------------------------------------------------------
APPL   |=================================================> 98%      CPU    |=> 3%
DB2    |                                                            NOTACC |=> 2%
SUSP   |=> 2%                                                       SUSP   |===============================================> 95%

AVERAGE       APPL(CL.1)  DB2 (CL.2)  IFI (CL.5)    CLASS 3 SUSPENSIONS   AVERAGE TIME  AV.EVENT    HIGHLIGHTS
------------  ----------  ----------  ----------    --------------------  ------------  --------    --------------------------
ELAPSED TIME   25.435644    0.504442         N/P    LOCK/LATCH(DB2+IRLM)      0.000000      0.00    #OCCURRENCES    :        2
 NONNESTED     25.435644    0.504442         N/A    SYNCHRON. I/O             0.085908      6.50    #ALLIEDS        :        2
 STORED PROC    0.000000    0.000000         N/A     DATABASE I/O             0.085908      6.50    #ALLIEDS DISTRIB:        0
 UDF            0.000000    0.000000         N/A     LOG WRITE I/O            0.000000      0.00    #DBATS          :        0
 TRIGGER        0.000000    0.000000         N/A    OTHER READ I/O            0.042337      1.00    #DBATS DISTRIB. :        0
                                                    OTHER WRTE I/O            0.000000      0.00    #NO PROGRAM DATA:        2
CPU TIME        0.016663    0.015404         N/P    SER.TASK SWTCH            0.352902      4.00    #NORMAL TERMINAT:        2
 AGENT          0.016663    0.015404         N/A     UPDATE COMMIT            0.000000      0.00    #ABNORMAL TERMIN:        0
  NONNESTED     0.016663    0.015404         N/P     OPEN/CLOSE               0.206822      1.50    #CP/X PARALLEL. :        0
  STORED PRC    0.000000    0.000000         N/A     SYSLGRNG REC             0.024259      1.00    #IO PARALLELISM :        0
  UDF           0.000000    0.000000         N/A     EXT/DEL/DEF              0.121821      1.50    #INCREMENT. BIND:        0
  TRIGGER       0.000000    0.000000         N/A     OTHER SERVICE            0.000000      0.00    #COMMITS        :        3
 PAR.TASKS      0.000000    0.000000         N/A    ARC.LOG(QUIES)            0.000000      0.00    #ROLLBACKS      :        0
                                                    ARC.LOG READ              0.000000      0.00    #SVPT REQUESTS  :        0
SUSPEND TIME         N/A    0.481147         N/A    STOR.PRC SCHED            0.000000      0.00    #SVPT RELEASE   :        0
 AGENT               N/A    0.481147         N/A    UDF SCHEDULE              0.000000      0.00    #SVPT ROLLBACK  :        0
 PAR.TASKS           N/A    0.000000         N/A    DRAIN LOCK                0.000000      0.00    MAX SQL CASC LVL:        0
                                                    CLAIM RELEASE             0.000000      0.00    UPDATE/COMMIT   :     0.00
NOT ACCOUNT.         N/A    0.007891         N/A    PAGE LATCH                0.000000      0.00    SYNCH I/O AVG.  : 0.013217
DB2 ENT/EXIT         N/A       35.00         N/A    NOTIFY MSGS               0.000000      0.00
EN/EX-STPROC         N/A        0.00         N/A    GLOBAL CONT.              0.000000      0.00
EN/EX-UDF            N/A        0.00         N/A    FORCE-AT-COMMIT           0.000000      0.00
DCAPT.DESCR.         N/A         N/A         N/P    ASYNCH IXL REQUESTS       0.000000      0.00
LOG EXTRACT.         N/A         N/A         N/P    TOTAL CLASS 3             0.481147     11.50
Figure 39. Accounting short report for a CICS transaction accessing DB2 resources
   LOCATION: DSN710P2                          DB2 PERFORMANCE MONITOR (V7)                               PAGE: 1-1
      GROUP: DSN710P2                            ACCOUNTING REPORT - SHORT                      REQUESTED FROM: NOT SPECIFIED
     MEMBER: DF2D                                                                                           TO: NOT SPECIFIED
  SUBSYSTEM: DF2D                                     ORDER: PLANNAME                            INTERVAL FROM: 11/05/01 10:42:31.25
DB2 VERSION: V7                                       SCOPE: MEMBER                                         TO: 11/05/01 10:50:14.53

                            #OCCURS #ROLLBK SELECTS INSERTS UPDATES DELETES CLASS1 EL.TIME CLASS2 EL.TIME GETPAGES SYN.READ LOCK SUS
PLANNAME                    #DISTRS #COMMIT FETCHES   OPENS  CLOSES PREPARE CLASS1 CPUTIME CLASS2 CPUTIME BUF.UPDT TOT.PREF #LOCKOUT
--------------------------- ------- ------- ------- ------- ------- ------- -------------- -------------- -------- -------- --------

DSNJDBC                           1       0    0.00    0.00    0.00    0.00       1.706541       1.003194   249.00    13.00     0.00
                                  0       2    4.00    2.00    2.00    2.00       0.027471       0.025984     0.00     5.00        0

TESTP05                           2       0    0.00    0.50    0.00    0.00      33.283119       0.215656     7.00     0.00     0.00
                                  0       2    1.50    0.50    0.00    0.00       0.001908       0.001389     1.00     0.00        0

***  GRAND TOTAL  ***
                                  3       0    0.00    0.33    0.00    0.00      22.757593       0.478169    87.67     4.33     0.00
                                  0       4    2.33    1.00    0.67    0.67       0.010429       0.009587     0.67     1.67        0

Related concepts
Accounting and monitoring in a CICS DB2 environment
CICS-supplied accounting and monitoring information
DB2-supplied accounting and monitoring information
Accounting for processor usage in a CICS DB2 environment
Relating DB2 accounting records to CICS performance class records
Monitoring a CICS DB2 environment: Overview
[[ Contents Previous Page | Next Page Index ]]