Introduction
DB2 accounting will be collected from SMF type 101. In this type of record, it doesn’t really matter the SMF subtype of the SMF record because DB2 uses another specification type to distinguish different SMF specializations.
In DB2 there is an identification named IFCID (Instrumentation Facility Component Identifier) each one with different data and purpose. ZSA already extracts SMF 101 IFCID 003, but there is important information not available at IFCID 003 but present in IFCID 239, like package name, collection name, and many other fields.
IFCID 003 and 239 are related and there are several common fields present. For this IFCID 239 of SMF 101, ZSA will produce IFCID 239, a CSV record with qualifier I239 including PROD section and General Package accounting section.
Following the same rules previously established, for each CSV record, a new SQL Table should be created. In this case, we will have 1 new SQL Table
Notes
- The SMF fields used as input to populate the fields of CSV have the same name of CSV fields, excluding the prefix of each CSV field. For instance, CSV field DB2239_QPACRECN is populated by SMF field QPACRECN.
- CSV records I239, produced by SMF 101 IFCID 239 are related to CSV DBA, produced by SMF 101 IFCID 003. having the same information in CSV Header and in QWHS, QWHC, QWHD, QWHA and QWHU sections, which are defined in both DND2QWH macro (IFCID 239) and DNFDBA macro (IFCID 003). These macros will be described here and are located at ZCOSR.DXPV6400.SOURCE at ZDEVL environment.
- When the field type is indicated as HEX, it is normally a counter or a total and should be converted to a decimal value by the windows parser.
Fields
Stored in db2accountings
Note: the package name is a concatenation of the following fields in order (spaced with spaces) :
- location_name
- package_collection_id
- program_name
| SMF Field | DB Column name | Description | Unit |
|---|---|---|---|
| SM101DTE/SMF101TME | smf_buffer_date | record timestamp | |
| QWHSLOCN | local_location_name | Local location name | |
| QWHSNID | luwid_network_id | LUWID Network ID | |
| QWHSLUNM | luwid_luname | LUWID LUNAME | |
| QWHSLUUV | luwid_uniquen_hex | LUWID UNIQUEN | |
| QWHSLUCC | luwid_commit_hex | LUWID COMMIT | |
| QWHSACE | ace_address_hex | ACE address | |
| QWHCAID | authorization_id | Authorization ID | |
| QWHCCV | correlation_id | Correlation ID | |
| QWHCPLAN | plan_name | Plan name | |
| QWHCOPID | orig_operator_id | Original operator ID | |
| QWHCATYP | connect_type_hex | Connection type | |
| QWHCCN | connection_name | Connection name | |
| QWHCEUID | enduser_user_id | Enduser USER-ID | |
| QWHCEUTX | enduser_transaction | Enduser transaction | |
| QWHCEUWN | enduser_workstation | Enduser workstation | |
| QWHCTCXT | trusted_context | Trusted context | |
| QWHCROLE | role_name | Role name | |
| QWHCCTKN | correlation_token | Correlation token | |
| QWHDRQNM | requester_location | Requester location | |
| QWHDSVNM | srvnam_parameter | SRVNAM parameter | |
| QWHDPRID | product_id_parm | Product ID parameter | |
| QWHAMEMN | db2_member_name | DB2 member name | |
| QWHADSGN | db2_group_name | DB2 group name | |
| QWHUCPU | cpu_time_ziip_cp | CPU time ZIIP+CP | |
| QWHUSE | cpu_time_ziip_hex | CPU time ZIIP | |
| QPACRECN | qpac_data_number | Number of this DSNDQPAC data section in the series of DSNDQPAC data sections that may be externalized in IFCID 239. QWACPKGN and QPKGPKGN identify the number of packages for which package accounting has been performed | |
| QPACFLGS | qpac_flag |
|
|
| QPACLOCN | location_name | Location name Truncated if QPACLOCN_OFF=0 |
|
| QPACCOLN | package_collection_id | Package collection ID Truncated if QPACCOLN_OFF=0 |
|
| QPACPKID | program_name | Program name Truncated if QPACPKID_OFF=0 |
|
| QPACCONT | consistency_token | Consistency token | |
| QPACSQLC | sql_statements_number | Number of SQL requests This field may not equal the sum total of all SQL statement counters in the DSNDQXST data section. This is because DSNDQXST does not count all SQL statements |
|
| QPACSCB | clock_value_entry_most_recent_exec | Store clock value at entry to DB2 for most recent execution of this package | |
| QPACSCE | clock_value_exit_most_recent_exec | Store clock value at exit from DB2 after most recent execution of this package | |
| QPACSCT | total_time_all_exec | Total elapsed time spent processing all executions of this package | millisecond |
| QPACBJST | cpu_time_entry_most_recent_exec | CPU time at entry to DB2 for the most recent execution of this package. This time does not include CPU consumed on an IBM specialty engine | |
| QPACEJST | cpu_time_exit_most_recent_exec | CPU time at exit to DB2 for the most recent execution of this package. This time does not include CPU consumed on an IBM specialty engine | |
| QPACTJST | total_cpu_time_all_exec | CPU time for all executions of this package. This time does not include CPU consumed on an IBM specialty engine | millisecond |
| QPACARNA | number_db2_entries_exits_events | Number of DB2 entry or exist events processed while executing this package | |
| QPACARNE | number_wait_for_io | Number of wait trace events processed for waits for I/O under this thread while executing this package | |
| QPACAWTI | accum_io_wait | Accumulated I/O elapsed wait time for I/O done under this thread while executing this package | millisecond |
| QPACAWTL | accum_lock_wait | Accumulated lock elapsed wait time that occured while executing this package | millisecond |
| QPACAWTR | accum_wait_io_read_on_other_thread | Accumulated wait time for read I/O that is done under a thread other than this one while executing this package | millisecond |
| QPACAWTW | accum_wait_io_write_on_other_thread | Accumulated wait time for write I/O that is done under a thread other than this one while executing this package | millisecond |
| QPACAWTE | accum_wait_db2_services | Accumulated wait time due to synchronous executing unit switch to DB2 services from this thread while executing this package. The following DB2 service waits are included in this field
There is no overlap between the elapsed time reported in this field and the other class 8 elapsed times There is no overlap between the elapsed time reported in this field and the elapsed time reported INQLACCPUL for the thread with distributed activity |
millisecond |
| QPACALOG | accum_wait_archive_logs | Accumultad wait time due to processing of archive log mode (QUIESCE) commands while executing this package. This number represents the amount of time that an individual thread was suspended due to an archive log mode (QUIESCE) command and not the time that it took for the entire command to complete | millisecond |
| QPACARNL | number_wait_for_locks | Number of wait trace events processed for waits on locks while executing this package | |
| QPACARNR | number_wait_io_read_on_other_thread | Number of wait trace events processed for waits for read I/O under another thread while executing this package | |
| QPACARNW | number_wait_io_write_on_other_thread | Number of wait trace events processed for waits for write I/O under another thread while executing this package | |
| QPACARNS | number_wait_db2_services | Number of wait trace events processed for waits for synchronous execution unit switching to DB2 service tasks while executing this package | |
| QPACALCT | number_suspensions_archive_log | Number of suspensions due to processing of archive log mode (QUIESCE) commands while executing this package | |
| QPACARND | number_wait_drain_lock | Number of wait trace events processed for waits for a drain lock while executing this package | |
| QPACAWDR | accum_wait_drain_lock | Accumulated wait time for a drain lock while executing this package | millisecond |
| QPACAWCL | accum_wait_drain_release | Accumulated wait time for a drain when waiting for claims to be released while executing this package | millisecond |
| QPACARNC | number_wait_suspensions_release | Number of wait trace events processed for suspensions to wait for claims to be released while executing this package | |
| QPACAWAR | accum_wait_read_archive_active_active-log | Accumulated wait time for
|
millisecond |
| QPACANAR | number_wait_read_archive_active_active-log | Number of wait trace events processed for
|
|
| QPACAWTP | accum_wait_page_latch_contention | Accumulated wait time due to page latch contention while executing this package | millisecond |
| QPACARNH | number_wait_page_latch_contention | Number of wait trace events processed for page latch contention while executing this package | |
| QPACAWTG | accum_wait_sending_msgs_db2_data_sharing_grp | Accumulated wait time due to sending of messages within the DB2 data sharing group | millisecond |
| QPACAWTJ | accum_wait_global_contention_parent_llocks | Accumulated wait time due to global contention for parent L-LOCKS. Global contention occurs when inter-system communication is required to resolved an IRLM lock or change request. QPACAWTL contains the wait time due to local contention. Local contention does not require inter-system communication. It can be resolved by the local subsystem | millisecond |
| QPACARNG | number_wait_msgs_db2_data_sharing_grp | Number of wait trace events processed for messages within the DB2 data sharing group | |
| QPACARNJ | number_wait_global_contention_parent_llocks | Number of wait trace events processed for waits for global lock contention for parent L-LOCKS | |
| QPACSPNS | number_stored_exec_procedures | Number of stored procedures executed | |
| QPACUDNU | number_user_defined_scheduled_fcts | Number of user-defined functions scheduled | |
| QPACASCH | nest_activity_schema | Nested activity schema name | |
| QPACAANM | activity | Name of activity | |
| QPACAAFG | activity_flag | Activity flag
|
|
| QPACAWTK | accum_wait_global_contention_child_llocks | Accumulated wait time due to global contention for child L-LOCKS | millisecond |
| QPACAWTM | accum_wait_global_contention_other_llocks | Accumulated wait time due to globa contention for other L-LOCKS | millisecond |
| QPACAWTN | accum_wait_global_contention_pageset_part_plocks | Accumulated wait time due to global contention for PAGESET/PARTITION P-LOCKS | millisecond |
| QPACAWTO | accum_wait_global_contention_page_plocks | Accumulated wait time due to global contention for page P-LOCKS | millisecond |
| QPACAWTQ | accum_wait_global_contention_other_plocks | Accumulated wait time due to global contention for other P-LOCKS | millisecond |
| QPACARNK | number_wait_global_contention_child-llocks | Number of wait trace events processed for waits for global lock contention for child L-LOCKS | |
| QPACARNM | number_wait_global_contention_other_llocks | Number of wait trace events processed for waits for global lock contention for other L-LOCKS | |
| QPACARNN | number_wait_global_contention_pageset_part_plocks | Number of wait trace events processed for waits for global lock contention for PAGESET/PARTITION P-LOCKS | |
| QPACARNO | number_wait_global_contention_page_plocks | Number of wait trace events processed for waits for global lock contention for page P-LOCKS | |
| QPACARNQ | number_wait_global_contention_other_plocks | Number of wait trace events processed for waits for global lock contention for other P-LOCKS | |
| QPACSWITCH | number_invoked_from_other_package | Number of times package was invoked from a different package. For the first package run by an aplication The initial call counts as a package switch. If this package called a nested package (a trigger, UDF or stored procedure). A switch will not be counted upon return from such a package |
|
| QPACCLS7_ZIIP | total_cpu_time_all_exec_on_specialilty_engine | CPU time for all execution of this package consumed on an IBM special engine | millisecond |
| QPACALBC | number_wait_lob_xml | Number of wait trace events processed for waits for TCP/IP LOB or XML materialization while executing this package | |
| QPACALBW | accum_wait_lob_xml | Accumulated wait time for TCP/IP LOB or XML materialization while executing this package | millisecond |
| QPACAWLH | accum_wait_latch_suspension | Accumulated latch elapsed wait time for latch suspensions that occured while executing this package | millisecond |
| QPACANLH | number_wait_latch | Number of wait trace events processed for waits on latches while executing this package | |
| QPACRLNU | number_threads_roll_data | Number of threads to roll data into this QPAC data section. Non-rollup QPACs will have a value of 1 and rollup QPACS will have a value of 1 or more | |
| QPACAACC | number_wait_accelerator | Number of wait trace events processed for requests to an accelerator while executing this package | |
| QPACAACW | accum_wait_accelerator | Accumulated wait time for requests to an accelerator while executing this package | millisecond |
| QPAC_PQS_WAIT | accum_wait_parallel_synchro_parent_child | Accumulated time waiting for parallel query processing to synchronize between parent and child tasks | millisecond |
| QPAC_PQS_COUNT | number_suspended_parallel_synchro_parent_child | Number of times a parallel query processing suspended waiting for parent/child to synchronize |