IFCID 346 - Package/DBRM Detail
This topic shows detailed information about "Record Trace - IFCID 346 - Package/DBRM Detail".
Record trace - IFCID 346 - Package/DBRM Detail
The field labels shown in the following sample layout of "Record Trace - IFCID 346 - Package/DBRM Detail" are described in the following section.
PACKAGE/DBRM ACCOUNTING DATA
LOCATION: OMPDB51 COLLECTION : TDKDB PACKAGE ID : AUTONOMOUS_STP_WPA
TOKEN: X'196533D11EA395F9' SECTION NMB : 1 TYPE : ROLLUP SCHEMA NAME : TDKDB
SQL STMTS 2 USED BY STOR.PROC: YES NON-ZERO CLASS 8: YES ACTIVITY NAME: AUTONOMOUS_STP_WPA
SUCC AUTH CHECK : N/P LAST EXECUTED: N/P NON-ZERO CLASS 7: YES ACTIVITY TYPE: NATIVE SQL PROC
PACKAGE SWITCH : 1 ROLLED NBR THRDS: 1
ACE : 3807507019
CLASS 7 BEGINNING STORE CLOCK TIME N/P ENDING STORE CLOCK TIME N/P
BEGINNING TCB CPU TIME N/P ENDING TCB CPU TIME N/P
TOTAL ELAPSED TIME 33.939619 DB2 ENTRY/EXIT 2
TOTAL TCB TIME 0.000623 SE CPU TIME 0.000000
CLASS 8 LOCK/LATCH SUSP TIME N/A LOCK/LATCH SUSP EVENTS N/A
WAIT TIME LOCAL LOCKS 33.937330 LOCAL LOCK WAIT TRACE EVENTS 2
DB2 LATCH SUSP TIME 0.000000 LATCH WAIT TRACE EVENTS 0
SYNCHRONOUS I/O SUSP TIME 0.000000 SYNCHRONOUS I/O SUSP EVENTS 0
OTHER READ SUSP TIME 0.000000 OTHER READ SUSP EVENTS 0
OTHER WRITE SUSP TIME 0.000000 OTHER WRITE SUSP EVENTS 0
SERV.TASK SWITCH SUSP TIME 0.001654 SERV.TASK SWITCH SUSP EVENTS 6
ARCH.LOG(QUIES) SUSP TIME 0.000000 ARCH.LOG(QUIES) SUSP EVENTS 0
ACCUM. READ SUSP TIME 0.000000 WAIT TRACE READ EVENTS 0
DRAIN LOCK SUSP TIME 0.000000 DRAIN LOCK SUSP EVENTS 0
CLAIM RELEASE SUSP TIME 0.000000 CLAIM RELEASE SUSP EVENTS 0
PAGE LATCH SUSP TIME 0.000000 PAGE LATCH SUSP EVENTS 0
NOTIFY MESSAGES SUSP TIME 0.000000 NOTIFY MESSAGE EVENTS 0
GLOBAL CONTENT. PARENT SUSP TIME 0.000000 GLOBAL CONTENT. PARENT EVENTS 0
UDF EXECUTED 0
STORED PROCEDURE EXECUTED 0
TCP/IP LOB XML TIME 0.000000 TCP/IP LOB XML EVENTS 0
ACCELERATOR SUSP TIME 0.000000 ACCELERATOR EVENTS 0
PARALLEL QUERY SYNC WAIT TIME 8:42.707964 PARALLEL QUERY SYNC WAIT EVENTS 0
FAST INSERT PIPE WAIT TIME 0.000000 FAST INSERT PIPE WAIT EVENTS 0- LOCATION
The location name.
If this field is blank in trace or report, the package or DBRM was executed locally. If it is not blank, all times represent the time spent locally to execute the remote package for this APPL_DIR requester.
This field is invalid (N/P) if summary rollup data is present.
Field Name: QPACLOCN
This is an exception field.
- COLLECTION
The package collection ID. This field does not apply to DBRMs. If the program name cannot be identified, this field is not present in report or trace.
This field is invalid if summary rollup data is present. It can have the following value in:- Accounting trace and report: N/P
- The Accounting FILE and SAVE PROGRAM table: blank
Field Name: QPACCOLN
This is an exception field.
- PACKAGE ID
The program name (package ID or DBRM name).
In the case of rollup data (Accounting data of DDF/RRSAF threads and parallel tasks accumulated by DB2), the following value is shown *ROLSUM*.
Field Name: QPACPKID
This is an exception field.
- TOKEN
The program (package or DBRM) consistency token.
This field is invalid (0) if summary rollup data is present.
Field Name: QPACCONT
- SECTION NMB
The number of this particular data section in the series.
Field Name: QPACRECN
- TYPE
The program type. It can be DBRM (field name QPACDBRM) or package (field name QPACPACK).
Field Name: QPACFLGS
- SCHEMA NAME
Schema name of the nested activity.
If the package is defined for a trigger, stored procedure, or user-defined function, then this field contains the name of the schema to which the nested activity belongs. It can have the following value in:- Accounting Trace and Report: N/P
- The Accounting FILE and SAVE PROGRAM tables: blank
This field is invalid if summary rollup data is present.
Field Name: QPACASCH
- SQL STMTS
The number of SQL statements issued in this package or DBRM.
This number may not be equal to the total number of SQL statements in the QXST data section because QXST does not count all SQL statements. For example, it does not count commit or rollback statements.
Note: This field is shown for the following field labels in Accounting trace:- SQL STMT - TOTAL
- SQL STMT - AVERAGE:
Field Name: QPACSQLC
This is an exception field.
- USED BY STOR.PROC
Indicates whether this package was loaded by a stored procedure.
This field is invalid if unique or summary rollup data is present.
Field Name: QPACINSP
- NON-ZERO CLASS 8
Indicates if Class 8 data is in this record.
Field Name: QPACCLS8
- ACTIVITY NAME
The name of the nested activity.
This field contains the name of the nested activity if the package is defined for a:- Trigger
- Stored procedure
- User-defined function (UDF)
- Native SQL procedure
- Non-inline UDF
In a data block that reports totals it is set to ALL NAMES.
This field is invalid if summary rollup data is present.
It can have the following value in:- Accounting Trace and Report: N/P
- The Accounting FILE and SAVE PROGRAM tables: blank
Field Name: QPACAANM
- SUCC AUTH CHECK
Indicates whether a successful package EXECUTE authorization check was made and DB2 catalog access was avoided.
This field is invalid if unique or summary rollup data is present.
Field Name: QPACPAC
- LAST EXECUTED
This package or DBRM is either currently executing or is the most recently executed package or DBRM. This field is invalid if unique or summary rollup data is present.
Field Name: QPACCRNT
- NON-ZERO CLASS 7
There is nonzero accounting class 7 data in this QPAC data instance.
Field Name: QPACCLS7
- ACTIVITY TYPE
- The type of activity. The following values indicate how the package was loaded:
- ALL TYPES
- In a data block that reports totals it is set to ALL TYPES.
- STORED PROC
- When running an external procedure
- TRIGGER
- When running a trigger
- UDF
- When running a user-defined function
- NATIVE SQL PROC
- When running a native SQL procedure
- NATIVE UDF
- When running a native UDF procedure (a non-inline user-defined function)
- NONNESTED
- Indicates that none of the above values is true
- MULTIPLE
- Indicates that packages with the same key but with different activity types were running
- N/P
- Invalidated in case of rollup summary
- S
- For Stored Procedure
- T
- For Trigger
- U
- For UDF
- Q
- For native SQL procedure
- D
- For Native UDF
- N
- For nonnested (other)
- blank
- For invalidated in case of rollup summary
This field is invalid if unique or summary rollup data is present.
Field Name: QPACAAFG
- PACKAGE SWITCH
The number of times package was invoked from a different package. For the first package run by an application, the initial call counts as a package switch. If this package called a nested package (such as a trigger, UDF, or stored procedure), a switch will not be counted upon return from such a package.
Field Name: QPACSWITCH
- ROLLED NBR THREADS
This value can be one of the following:
- In general, the number of threads to roll data into this QPAC data section. Non-rollup QPACs have a value of 1 and rollup QPACs have a value of 1 or more. This number is used as a divisor for calculating averages for package class 7, 8, or 10 times and events.
- If
REPORT ORDER (ACTNAME) is specified, the number of threads to roll
data into this QPAC data section of a special activity type
depends
on the following:
- If IFCID 233 or 380 is available, the number of threads to roll data into this QPAC data section for stored procedures (SP) is counted based on the available IFCID. If both IFCIDs are available, IFCID 380 is the preferred one for SP reporting. Subprograms called by these SPs are not taken into account.
- If IFCID 233 or 381 is available, the number of threads to roll data into this QPAC data section for user-defined functions (UDF) is counted based on the available IFCID. If both IFCIDs are available, IFCID 381 is the preferred one for UDF reporting. Subprograms called by these UDFs are not taken into account.
- If neither IFCID 233, 380, nor 381, is collected, the total number of threads to roll data into this QPAC data section is counted. The sum also includes the number of subprograms.
Field Name: QPACRLNU
- ACE
ACE token. You can use this value to correlate this record with other monitor trace records.
Field Name: QW0346_ACE
- HREF HREF
ACE token. You can use this value to correlate this record with other monitor trace records.
Field Name: HREF
- CLASS 7: BEGINNING STORE CLOCK TIME
The store clock time at entry to DB2 for the most recent execution of this package or DBRM.
This field is invalid if unique or summary rollup data is present.
Field Name: QPACSCB
- CLASS 7: ENDING STORE CLOCK TIME
The store clock time at exit from DB2 after the most recent execution of this package or DBRM.
This field is invalid if unique or summary rollup data is present.
Field Name: QPACSCE
- CLASS 7: BEGINNING TCB CPU TIME
The CPU time at entry to DB2 for the most recent execution of this package or DBRM. This time does not include the CPU time consumed on an IBM specialty engine.
This field is invalid if unique or summary rollup data is present.
Field Name: QPACBJST
- CLASS 7: ENDING TCB CPU TIME
The CPU time at exit from DB2 for the most recent execution of this package or DBRM. This time does not include CPU consumed on an IBM specialty engine.
This field is invalid if unique or summary rollup data is present.
Field Name: QPACEJST
- CLASS 7: TOTAL ELAPSED TIME
The total elapsed time for executing the package or DBRM.
Field Name: QPACSCT
- CLASS 7: DB2 ENTRY/EXIT
The number of DB2 entries or exits processed during the execution of the package or DBRM.
In Accounting reports this is shown twice; as a total and as an average.
Field Name: QPACARNA
- CLASS 7: TOTAL TCB TIME
- The class 7 CPU time for all executions of the package or DBRM. This time does not include the:
- Class 7 time for parallel tasks
- CPU time that is consumed on an IBM specialty engine
Field Name: QPACTJST
This is an exception field.
- CLASS 7: SE CPU TIME
The total CPU time for all executions of this package or DBRM that was consumed on an IBM specialty engine (SE).
Note: All CPU times of an IBM specialty engine that are reported in DB2 trace records are already normalized by DB2 to the speed of the general purpose processor.Field Name: QPACCLS7_ZIIP
- CLASS 8: LOCK/LATCH SUSP TIME
The accumulated lock elapsed wait time that occurred while executing this package.
Background and Tuning Information
OMEGAMON XE for DB2 PE might adjust this value if the thread was suspended when performance data was gathered.
If the suspension time is high, investigate locking activity.
Field Name: QPACAWTL
This is an exception field.
- CLASS 8: LOCK/LATCH SUSP EVENTS
The number of wait trace events processed for waits for lock while executing this package.
Field Name: QPACARNL
- CLASS 8: WAIT TIME LOCAL LOCKS
The accumulated latch elapsed wait time for latch suspensions that occurred while executing this package.
Field Name: QPACAWLH
- CLASS 8: LOCAL LOCK WAIT TRACE EVENTS
The number of wait trace events processed for page latch contention while executing this package.
Field Name: QPACANLH
- CLASS 8: DB2 LATCH SUSP TIME
The accumulated lock elapsed wait time that occurred while executing this package.
Background and Tuning Information
OMEGAMON XE for DB2 PE might adjust this value if the thread was suspended when performance data was gathered.
If the suspension time is high, investigate locking activity.
Field Name: QPACAWTL
This is an exception field.
- CLASS 8: LATCH WAIT TRACE EVENTS
The number of wait trace events processed for waits for lock while executing this package.
Field Name: QPACARNL
- CLASS 8: SYNCHRONOUS I/O SUSP TIME
The accumulated elapsed wait time for I/O suspensions under this thread during the execution of the package or DBRM.
Background and Tuning Information
OMEGAMON XE for DB2 PE might adjust this value if the thread was suspended when its performance data was gathered.
Field Name: QPACAWTI
This is an exception field.
- CLASS 8: SYNCHRONOUS I/O SUSP EVENTS
The number of wait trace events processed for I/O.
Field Name: QPACARNE
- CLASS 8: OTHER READ SUSP TIME
The accumulated waiting time for a read I/O performed under a thread other than this one during the execution of the package or DBRM.
Background and Tuning Information
OMEGAMON XE for DB2 PE might adjust this value if the thread was suspended when performance data was gathered.
This field includes waits caused by sequential prefetch, list prefetch, dynamic prefetch, and synchronous read I/O performed by other threads.
If the value in this field is high, the problem could be an I/O bound query using prefetch or an I/O contention. The application is accessing data from a busy data set, volume, or control unit and is continually being suspended. Consult the DBA and MVS systems programmer.
Field Name: QPACAWTR
This is an exception field.
- CLASS 8: OTHER READ SUSP EVENTS
The number of suspensions due to read I/O.
Field Name: QPACARNR
- CLASS 8: OTHER WRITE SUSP TIME
The accumulated waiting time due to a write I/O performed for another thread during the execution of a package or DBRM.
Background and Tuning Information
If the value in this field is high, the problem could be I/O contention. The application is accessing data from a busy data set, volume, or control unit and is continually being suspended. Consult the DBA and MVS systems programmer to resolve possible data set placement problems.
Field Name: QPACAWTW
This is an exception field.
- CLASS 8: OTHER WRITE SUSP EVENTS
The number of suspensions due to write I/O.
Field Name: QPACARNW
- CLASS 8: SERV.TASK SWITCH SUSP TIME
The accumulated waiting time due to a synchronous execution unit switch to DB2 services from this thread during the execution of the package or DBRM.
Background and Tuning Information
OMEGAMON XE for DB2 PE might adjust this value if the thread was suspended when its performance data was gathered.
This value includes the waits because of an
OPEN/CLOSE data
set, SYSLGRNG update, DATASPACE MANAGER
services,
DEFINE, EXTEND, and DELETE data set, AUTONOMOUS PROCEDURE, and
DDF Requester waiting for Server reply and VSAM Catalog update.
Field Name: QPACAWTE
This is an exception field.
- CLASS 8: SERV.TASK SWITCH SUSP EVENTS
The number of wait trace events processed for DB2 service tasks.
Field Name: QPACARNS
- CLASS 8: ARCH.LOG(QUIES) SUSP TIME
The accumulated waiting time caused by processing ARCHIVE LOG(QUIESCE) commands during the execution of the package or DBRM. This number represents the amount of time that an individual thread was suspended because of the command, not the time it took for the entire command to complete.
Background and Tuning Information
OMEGAMON XE for DB2 PE might adjust this value if the thread was suspended when its performance data was gathered.
Avoid issuing the -ARCHIVE LOG QUIESCE command during peak periods.
Field Name: QPACALOG
- CLASS 8: ARCH.LOG(QUIES) SUSP EVENTS
The number of ARCHIVE LOG MODE (QUIESCE) commands issued.
Field Name: QPACALCT
- CLASS 8: DRAIN LOCK SUSP TIME
The accumulated waiting time due to a drain lock.
Field Name: QPACAWDR
- CLASS 8: DRAIN LOCK SUSP EVENTS
The number of wait trace events processed for waits for drain locks.
Field Name: QPACARND
- CLASS 8: CLAIM RELEASE SUSP TIME
The accumulated waiting time for a drain waiting for claims to be released during the execution of the package or DBRM.
Background and Tuning Information
OMEGAMON XE for DB2 PE might adjust this value if the thread was suspended when its performance data was gathered.
Field Name: QPACAWCL
- CLASS 8: CLAIM RELEASE SUSP EVENTS
The number of wait trace events processed for waits for claims to be released.
Field Name: QPACARNC
- CLASS 8: PAGE LATCH SUSP TIME
The accumulated waiting time caused by a page latch contention.
Field Name: QPACAWTP
- CLASS 8: PAGE LATCH SUSP EVENTS
The number of page latch wait trace events processed.
Field Name: QPACARNH
- CLASS 8: NOTIFY MESSAGES SUSP TIME
The accumulated elapsed waiting time due to suspensions caused by sending notify messages to other members in the data sharing group. Messages are sent, for example, when database descriptors are changed due to DDL.
This value is only calculated if accounting class 8 is active and DB2 is a member of a DB2 data sharing group.
Field Name: QPACAWTG
- CLASS 8: NOTIFY MESSAGES EVENTS
The number of wait trace events processed for sending notify messages to other members in the data sharing group.
Field Name: QPACARNG
- CLASS 8: GLOBAL CONTENT. PARENT SUSP TIME
The accumulated wait time due to global contention for parent L-Locks. Parent L-Locks are any of the following L-Lock types: database, tablespace, table, or partition.
Field Name: QPACAWTJ
- CLASS 8: GLOBAL CONTENT. PARENT EVENTS
The number of wait trace entry/exit events processed for waits for global lock contention for parent L-Locks.
Field Name: QPACARNJ
- CLASS 8: UDF EXECUTED
The number of user-defined functions scheduled.
Field Name: QPACUDNU
- CLASS 8: STORED PROCEDURE EXECUTED
The number of stored procedures scheduled.
Field Name: QPACSPNS
- CLASS 8: TCP/IP LOB XML TIME
The number of wait trace events that were processed for waits for TCP/IP LOB and XML materialization while this package or DBRM was running.
Field Name: QPACALBC
- CLASS 8: TCP/IP LOB XML EVENTS
The accumulated wait time for TCP/IP LOB and XML materialization while running this package or DBRM.
Field Name: QPACALBW
- ACCELERATOR SUSP TIME
The accumulated wait time for requests to an accelerator while executing this package.
Field Name: QPACAACW
- ACCELERATOR EVENTS
The number of wait trace events processed for requests to an accelerator while executing this package.
Field Name: QPACAACC
- PARALLEL QUERY SYNC WAIT TIME
The accumulated time waiting for parallel query processing to synchronize between parent and child tasks.
Field Name: QPAC_PQS_WAIT
- PARALLEL QUERY SYNC WAIT EVENTS
The number of times the parallel query processing suspended because it was waiting for the synchronization of the parent/child.
Field Name: QPAC_PQS_COUNT
- FAST INSERT PIPE WAIT TIME
- The accumulated wait time for a pipe while
this package was executed (DB2 field QPAC_PIPE_WAIT).
Field Name: QPAC_PIPE_WAIT
- FAST INSERT PIPE WAIT EVENTS
- The number of wait trace events that were processed for waits
for a pipe while this package was executed
(DB2 field QPAC_PIPEWAIT_COUNT).
Field Name: QPAC_PIPEWAIT_COUNT