General (Short Trace)
This topic shows detailed information about Accounting - General (Short Trace)
.
This block is part of the Accounting Short Trace.
Accounting - General (Short Trace)
The field labels shown in the following sample layout of Accounting - General (Short Trace)
are described in the following section.
...
PRIMAUTH CORRNMBR ACCT TIMESTAMP SELECTS DELETES MERGES CPU TIME(CL1) GETPAGES TOT.PREF
PLANNAME CONNECT TERM. CONDITION OPENS INSERTS PREPARE EL. TIME(CL2) BUF.UPDT LOCK SUS
CORRNAME THR.TYPE COMMITS FETCHES UPDATES EL. TIME(CL1) CPU TIME(CL2) SYN.READ LOCKOUTS
-------- -------- --------------- ------- ------- -------------- -------------- -------- --------
ABC "BLANK" 07:39:25.143756 0 0 0 0.003189 42 1
java.exe SERVER NORM TYP2 INACT 1 0 1 0.184705 0 0
java.exe DBAT 1 1 0 0.196676 0.003055 16 0
...
- PRIMAUTH
-
The primary authorization ID from a connection or signon. The connection authorization exit and the signon authorization exit can change the primary authorization ID so that it differs from the original primary authorization ID (ORIGAUTH). Distributed authorization ID translation can also change the primary authorization ID.
Field Name: QWHCAID
- PLANNAME
-
The plan name. It is blank for a DB2 command thread; otherwise:
- DSNESPRR
- For SPUFI with repeatable read.
- DSNESPCS
- For SPUFI with cursor stability.
- DSNUTIL
- For utilities.
- DSNTEP2
- For DSNTEP2.
- DSNBIND
- For binding.
- The application plan name
- For IMS.
- The application plan name
- For CICS®.
- A blank plan name
- For IMS and CICS commands.
- DSQPLAN
- For QMF.
- The first 8 bytes of the application name
- For DRDA connections to the common servers.
Field Name: QWHCPLAN
This is an exception field.
- CORRNAME
-
This field shows the correlation name. It is obtained by translating the correlation ID into correlation name and number. The default translation depends on the connection type of the thread:
- Batch
- Job name
- TSO or CAF
- Original authorization ID
- CICS
- Transaction ID
- IMS
- Application PST
- RRSAF
- Characters 1 to 8 of the parameter correlation ID specified for SIGNON.
You can define your own correlation ID translation, which overrides the default translation.
Field Name: ADCORNME
- CORRNMBR
-
This field shows the correlation number. It is obtained by translating the correlation ID into correlation name and number. The default translation depends on the connection type of the thread:
- Batch
- Blank
- TSO or CAF
- Blank
- CICS
- Pool thread
- IMS
- Application PSBNAME
- RRSAF
- Characters 9 - 12 of the parameter correlation ID specified for SIGNON.
You can define your own correlation ID translation which overrides the default translation.
Field Name: ADCORNMB
- CONNECT
-
The connection name. Possible values are:
- For batch: BATCH
- For TSO: TSO
- For QMF: DB2CALL
- For utilities: UTILITY
- For DB2 private protocol this is the DB2 subsystem ID
- For IMS: the IMS ID
- For CICS, this is the CICS ID
- For DRDA connections from non-DB2 requesters: SERVER
Field Name: QWHCCN
This is an exception field.
- THR.TYPE
-
The type of thread. This field can contain one of the following values:
- ALLIED
- The thread is not involved in any distributed activity.
- ALLDDIST
- The thread is initiated by a DB2 attach and requests data from one or more server locations.
- DBAT
- The thread is initiated, created, and performing work on behalf of a remote (requester) location. The value DBAT also includes DBAT DISTRIBUTED threads that are initiated by a requester location and executed by the server location that in turn requests data from another server location.
Background and Tuning Information
If the thread is involved in distributed activity, some monitored values can produce different results. For example, the class 1 elapsed time for a distributed thread is higher because the network time is also included.
Field Name: ADTHRTYP
- ACCT TIMESTAMP
-
The store clock value of the time when the accounting record was generated.
Field Name: QWHSSTCK
- TERM. CONDITION
-
The reason for termination, that is, for generating a DB2® accounting record.
Field Name: ADCNDRSN
- COMMITS
-
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.
- OPENS
-
The number of OPEN statements executed.
Field Name: QXOPEN
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
- DELETES
-
The number of DELETE statements executed.
Field Name: QXDELET
- 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
- 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
- EL. TIME(CL1)
-
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.
- CPU TIME(CL1)
-
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.
- EL. TIME(CL2)
-
The class 2 elapsed time of the allied agent accumulated in DB2.
Field Name: ADDB2ETT
This is an exception field.
- CPU TIME(CL2)
-
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.
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.
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
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.
- LOCKOUTS
-
The number of deadlocks and timeouts.
Field Name: ADTIMDLK
This is an exception field.