General (Short Report)

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

This block is part of the Accounting Short Report.

Accounting - General (Short Report)

The field labels shown in the following sample layout of "Accounting - General (Short Report)" are described in the following section.

...
                            #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
...

#OCCURS

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

This is the case, for example, in query CP and sysplex query parallelism, where several accounting records (IFCID 003 and, optionally, 239) are generated, namely 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.

Field Name: ASOCCURS

#DISTRS

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

Field Name: ASDISTRS

#ROLLBK

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

Field Name: QWACABRT

This is an exception field.

#COMMIT

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.

Field Name: QWACCOMM

This is an exception field.

SELECTS

The number of SQL SELECT statements executed.

Field Name: QXSELECT

This is an exception field.

FETCHES

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).

Field Name: QXFETCH

INSERTS

The number of INSERT statements executed.

Field Name: QXINSRT

UPDATES

The number of UPDATE statements executed.

Field Name: QXUPDTE

MERGES

The number of times a MERGE statement was executed.

Field Name: QXMERGE

OPENS

The number of OPEN statements executed.

Field Name: QXOPEN

CLOSES

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).

Field Name: QXCLOSE

DELETES

The number of DELETE statements executed.

Field Name: QXDELET

PREPARE

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).

Field Name: QXPREP

CLASS1 EL.TIME

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.

Field Name: ADRECETT

This is an exception field.

CLASS1 CPUTIME

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.

Field Name: ADCPUT

This is an exception field.

CLASS2 EL.TIME

The class 2 elapsed time of the allied agent accumulated in DB2.

Field Name: ADDB2ETT

This is an exception field.

CLASS2 CPUTIME

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.

Field Name: ADDBCPUT

This is an exception field.

GETPAGES

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.

Start of change 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. End of change

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.

Field Name: QBACGET

This is an exception field.

BUF.UPDT

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.

Field Name: QBACSWS

This is an exception field.

SYN.READ

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.

Field Name: QBACRIO

This is an exception field.

TOT.PREF

The number of sequential, dynamic, and list prefetch requests.

Field Name: ABCLSPR

This is an exception field.

LOCK SUS

The total number of all lock suspensions. This includes local and global lock suspensions.

Field Name: ALTSUSP

This is an exception field.

#LOCKOUT

The number of deadlocks and timeouts.

Field Name: ADTIMDLK

This is an exception field.

Feedback