General (Short Report)

This topic shows detailed information about Accounting - General (Short Report).

Accounting - General (Short Report) 


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

ABC                            1164    1163    0.00    0.66           1.33       0.047610   198.13     0.00
 java                          1164    0.00    0.00    0.11       0.050089       0.009510     1.32        0
                                  2    0.66    0.00    0.00       0.009638         327.38    11.30
...

The fields shown in this record block are described below. Db2 field names are shown in brackets next to each report field.

#OCCURS [ASOCCURS]

The number of logical accounting records. A logical accounting record can contain more than one physical record.

For example, in query CP and sysplex query parallelism, several accounting records (IFCID 003 and, optionally, 239) are generated, one for the entire thread and one for each parallel task within the thread.

In case of Distributed Data Facility (DDF) or Recoverable Resource Manager Services Attach Facility (RRSAF) threads, it is the number of accounting intervals rolled up in a record.

This number is used for calculating averages (as a divisor) for class 1, 2, 3, and 5 times and events.

#DISTRS [ASDISTRS]

The number of accounting records with distributed activity, which is the number of accounting records related to allied-distributed, DBAT, or DBAT- distributed threads.

#ROLLBK [QWACABRT]

(Exception field) The number of rollback requests. This is the number of units that were backed out, including rollbacks from attaches.

Special Considerations: This field contains the number of:
  • Application program abends
  • Application rollback requests
  • Application deadlocks on database records
  • Applications canceled by operator
  • Thread abends due to resource shortage
#COMMIT [QWACCOMM]

(Exception field) The number of successful two-phase (units of recovery) or single-phase (syncs) commit requests. It indicates the number of units of recovery that are completed successfully, and for which the associated commit duration locks were released. It represents the total number of commit requests processed by the Db2 subsystem, whether the request was an explicit or implicit external request from an IMS or a CICS connection, or an implicit internal request within Db2 when Db2 was the commit coordinator or conducted read-only commit processing as a commit participant on phase-1 calls from an IMS or CICS connection.

For parallel queries, only the commits from the initiating (parent) thread are recorded by this counter.

SELECTS [QXSELECT]

(Exception field) The number of SQL SELECT statements executed.

FETCHES [QXFETCH]

The number of FETCH statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).

INSERTS [QXINSRT]

The number of INSERT statements executed.

UPDATES [QXUPDTE]

The number of UPDATE statements executed.

MERGES [QXMERGE]

The number of times a MERGE statement was executed.

OPENS [QXOPEN]

The number of OPEN statements executed.

CLOSES [QXCLOSE]

The number of CLOSE statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).

DELETES [QXDELET]

The number of DELETE statements executed.

PREPARE [QXPREP]

The number of SQL PREPARE statements executed. This number at the server location might not match the number at the user application because of the internal processing of the Distributed Data Facility (DDF).

CLASS1 EL.TIME [ADRECETT]

(Exception field) The class 1 elapsed time of the allied agent.

Special Considerations:
  • If the begin time equals zero, or if the end time minus begin time equals zero or is negative, N/C is shown.
  • Threads that can be reused, such as CICS protected threads or IMS/VS wait-for-input message regions, can include time during which the thread was inactive and waiting for work.
  • Elapsed time to process distributed requests is included for allied-distributed threads.
  • This time includes the time for processing SQL statements issued by stored procedures, user-defined functions, or triggers.
  • In query CP, sysplex query, or utility parallelism, this is the time shown in the originating record, which overlaps the elapsed times shown in the parallel records.
CLASS1 CPUTIME [ADCPUT]

(Exception field) The class 1 CPU time in an application. It indicates:

  • The class 1 CPU time of the allied agent, which may include the accumulated class 1 TCB time for processing stored procedures, user-defined functions, and triggers.
  • The accumulated CPU time for processing parallel tasks. This is valid for query CP parallelism, sysplex query parallelism, and parallel tasks generated by utilities.
  • In sysplex query parallelism, the individual CPU times are normalized by the conversion factor of the parallel tasks that is related to the originating task.

    In sysplex query parallelism, only CPU times of parallel tasks, running on the same member of the SYSPLEX group as the originating task, are included.

This CPU time does not include time that is consumed on an IBM specialty engine.

CLASS2 EL.TIME [ADDB2ETT]

(Exception field) The class 2 elapsed time of the allied agent accumulated in Db2.

CLASS2 CPUTIME [ADDBCPUT]
(Exception field) The class 2 CPU time (in Db2). It indicates:
  • The class 2 CPU time for the allied agent. This includes the accumulated class 2 TCB time for processing any stored procedures, user-defined functions, and triggers.
  • The accumulated CPU time for processing parallel tasks. This is valid for query CP parallelism, sysplex query parallelism, and parallel tasks generated by utilities.
  • For batch reporting, in sysplex query parallelism, the individual CPU times are normalized by the conversion factor of the parallel tasks, related to the originating task.

    For online monitoring, in sysplex query parallelism, only CPU times of parallel tasks, running on the same member of the sysplex group as the originating task, are included.

This CPU time does not include time that is consumed on an IBM specialty engine.

GETPAGES [QBACGET]

(Exception field) The number of Getpage requests. This counter is incremented by successful Getpage requests for queries processed in parallel for each thread and for all successful and unsuccessful Getpage requests for queries that are not processed in parallel.

Background and Tuning Information

Reducing the number of Getpages can improve Db2 performance by reducing the number of synchronous page reads. With fewer Getpages, the requested page is more likely to be returned from the buffer pool. CPU usage is also reduced.

Check the ratio of Getpages to SQL DML statements, as a rule of thumb, try and keep this ratio below six for a typical online transaction SQL.

You might need to modify the database and query design, for example:

  • Add indexes to tables to reduce the number of pages scanned.
  • Reassess the number of tables used and denormalize them, if necessary.

    As an example, a large table with many columns can result in several pages being fetched to satisfy a simple query requesting just a few columns. Splitting such a table into several tables with fewer columns, tailored to queries, will result in fewer pages returned for each query.

  • Use correlated rather than non-correlated queries to force the use of an index.
BUF.UPDT [QBACSWS]

(Exception field) The number of times a buffer update occurs. This is incremented every time a page is updated and is ready to be written to DASD. If the same page is updated twice, for example, the number is incremented by 2.

This number is kept for all types of pages including data pages and work-file pages.

Background and Tuning Information

A nonzero value indicates any of the following activities:
  • SQL INSERT, UPDATE, or DELETE
  • Merge scan join
  • Internal sort activity on the work files
Check the access path to determine whether sort activity can be minimized or avoided.
SYN.READ [QBACRIO]

(Exception field) The number of synchronous read I/O operations. Db2 increments this counter for each media manager synchronous physical read. Asynchronous I/O requests are not counted.

TOT.PREF [ABCLSPR]

(Exception field) The number of sequential, dynamic, and list prefetch requests.

LOCK SUS [ALTSUSP]

(Exception field) The total number of all lock suspensions. This includes local and global lock suspensions.

#LOCKOUT [ADTIMDLK]

(Exception field) The number of deadlocks and timeouts.