SMF 100-002C DB2 Statistics

Prev Next

Introduction

DB2 statistics will be collected from SMF type 100. 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.

The statistics trace reports information about how much the Db2 system services and database services are used.

You can use the information that the statistics trace provides to plan Db2 capacity or to tune the entire set of active Db2 programs.

If you specify YES for the SMF STATISTICS entry in panel DSNTIPN, the statistics trace uses classes 1, 3, 4, 5, and 6 as the defaults. If the statistics trace is started using the START TRACE command, the statistics trace uses class 1 as the default.

The following table describes the Db2 statistics trace classes.

If you specify YES for the SMFSTAT subsystem parameter, the statistics trace starts automatically when you start Db2, and sends the default classes (classes 1, 3, 4, 5, and 6) statistics data to SMF. SMF records statistics data in both SMF type 100 and 102 records. IFCIDs 0001, 0002, 0202, 0225, 0230, and 0369 are of SMF type 100. All other IFCIDs in statistics trace classes are of SMF type 102.

When you specify CLASS(7) or IFCID(365) in a START TRACE or MODIFY TRACE command, Db2 writes IFCID 0365 records. These records are written to the specified destination of the statistics trace for remote locations that communicate with the subsystem. Statistics are generated only for those locations that have activity since the record was generated. The location statistics are written each time that the STATIME interval elapses. Statistics can be written for as many as 95 remote locations.

Db2 also writes statistics about all DRDA locations with the other default statistics trace data to a single SMF location named DRDA REMOTE LOCS, whenever other default statistics are written.

Statistics trace classesThe following table shows the IFCIDs that are activated for each statistics trace class.Table 1. Classes for Db2 statistics traceClassDescription of classActivated IFCIDs1Information about system services, database statistics, statistics for the database services address space (ssnmDBM1), and information about the system parameters that were in effect when the trace was started. This default class is also activated when you omit the CLASS keyword from the START TRACE command when you start the statistics trace.0001, 0002, 0105, 0106, 0202, 02252Installation-defined statistics record01523Deadlock, lock escalation, group buffer pool, data set extension information, and indications of long-running uncommitted reads, and active log space shortages.0172, 0196, 0250, 0258, 0261, 0262, 0313, 0330, 03374Db2 exceptional conditions.0173, 0191-0195, 0203-0210, 0235, 0236, 0238, 0267, 02685Db2 data sharing statistics record.02306Storage statistics for the Db2 subsystem.02257DRDA location statistics.03658Data set I/O statistics.01999Aggregated CPU and wait time statistics by connection type.036910 - 29Reserved. 30 - 32Available for local use.

Intervals for collecting Db2 statistics trace records
Db2 collects statistics trace records for the following IFCIDs at the interval specified by the STATIME and SYNCVAL subsystem parameters. For more information, see STATISTICS TIME field (STATIME subsystem parameter) and

STATISTICS SYNC field (SYNCVAL subsystem parameter)

0105 (Db2 performance trace)
0106 (system parameters)
0199 (data set statistics)
0365 (remote location statistics)
0402 (monitor profile warnings or exceptions)
Db2 collects statistics trace records for other IFCIDs at fixed one-minute intervals.

CSV I002C for SMF 100 IFCID 002, including PROD section and Datasharing Buffer Manager stats.

Fields

Stored in dbsi002c

SMF Field DB Column name Type Length Description
DBSHD_RTY record_type Value is always I002C
DBSHD_SID sid SID
DBSHD_DTTM write_datetime Date/Time written into SMF buffer
DBSHD_SSID ssid Subsystem ID
DBSQWH_QWHSLOCN local_location_name Local location name
DBSQWH_QWHSNID luwid_network_id LUWID Network ID
DBSQWH_QWHSLUNM luwid_luname LUWID LUNAME
DBSQWH_QWHSLUUV luwid_uniquen LUWID UNIQUEN
DBSQWH_QWHSLUCC luwid_commit LUWID COMMIT
DBSQWH_QWHSACE ace_address ACE address
DBSQWH_QWHCAID authorization_id Authorization ID
DBSQWH_QWHCCV correlation_id Correlation ID
DBSQWH_QWHCPLAN plan_name Plan name
DBSQWH_QWHCOPID original_operator_id Original operator ID
DBSQWH_QWHCATYP connection_type Connection type
DBSQWH_QWHCCN connection_name Connection name
DBSQWH_QWHCEUID enduser_id Enduser USER-ID
DBSQWH_QWHCEUTX enduser_transaction Enduser transaction
DBSQWH_QWHCEUWN enduser_workstation Enduser workstation
DBSQWH_QWHCTCXT trusted_context Trusted context
DBSQWH_QWHCROLE role_name Role name
DBSQWH_QWHCCTKN correlation_token Correlation token
DBSQWH_QWHDRQNM requester_location Requester location
DBSQWH_QWHDSVNM server_name_parameter SRVNAM parameter
DBSQWH_QWHDPRID product_id_parameter Product ID parameter
DBSQWH_QWHAMEMN db2_member_name DB2 member name
DBSQWH_QWHADSGN db2_group_name DB2 group name
DBSQWH_QWHUCPU cpu_time_ziip_and_cp CPU time ZIIP+CP
DBSQWH_QWHUSE cpu_time_ziip CPU time ZIIP
DBS002B_QBSTPID buffer_pool_id Buffer pool ID
DBS002C_QBGLGG getpages_gbp_dep_pages GETPAGES FOR GBP DEP PAGES
DBS002C_QBGLXD gbp_reads_under_user_xd Number of GBP reads under the user task that were necessary due to buffer cross invalidation (XI) in which data was returned
DBS002C_QBGLXR gbp_reads_under_user_xr Number of GBP reads under the user task that were necessary due to buffer XI in which data was not returned from GBP
DBS002C_QBGLMD gbp_reads_under_user_md Number of GBP reads under the user task that were necessary due to the requested page not being found in the buffer pool in which data was returned from GBP
DBS002C_QBGLMR gbp_reads_under_user_mr Number of GBP reads under the user task that were necessary due to the requested page not being found in the buffer pool in which data was not returned
DBS002C_QBGLSW changed_pages_written Number of changed pages written under a user's execution unit (via WAR or WARM)
DBS002C_QBGLWC clean_pages_written_wc Number of clean pages written under a user's execution unit (via WAR or WARM)
DBS002C_QBGLCT castout_init_ct Number of times castout was initiated due to the castout class threshold being detected
DBS002C_QBGLGT castout_init_gt Number of times castout was initiated due to the group buffer pool castout threshold being detected
DBS002C_QBGLWM write_and_register_wm Number of Write and Register Multiple (WARM) requests. Multiple pages written request
DBS002C_QBGLWS write_and_register_ws Number of Write and Register Multiple (WARM) requests. One page written per request
DBS002C_QBGLEX explicit_cross_invalidate Number of explicit cross invalidate
DBS002C_QBGLAW changed_pages_written_aw Number of changed pages written under a system execution unit (via WAR or WARM)
DBS002C_QBGLRC pages_castout Number of pages castout from the Group Buffer Pool to DASD
DBS002C_QBGLCM read_castout_multiple Read For Castout Multiple RFCOM requests. Multiple pages read per request
DBS002C_QBGLCR read_castout_rfco Number of Read For Castout (RFCO) requests. One page read per request
DBS002C_QBGLWP pages_written_warm Number of pages written via WARM
DBS002C_QBGLWF gbp_write_requests Number of GBP write requests that could not complete due to a lack of GBP storage resources
DBS002C_QBGLOS ixlcache_read_stgstats Number of IXLCACHE "read stgstats" requests
DBS002C_QBGLAC changed_pages_written_ac Number of changed pages written under a system execution unit (via WAR or WARM)
DBS002C_QBGLAX register_page_list_rpl Number of of "register page list" (RPL) requests done by Prefetch
DBS002C_QBGLAY pages_retrieved Number of pages retrieved from the group buffer pool as a result of feedback from RPL
DBS002C_QBGLCK gbp_checkpoints_triggered Number of GBP checkpoints triggered by this member
DBS002C_QBGLUN ixlcache_unlock_castout Number of IXLCACHE "unlock castout" requests
DBS002C_QBGLCC ixlcache_real_castout_class Number of IXLCACHE "read castout class" requests
DBS002C_QBGLCS ixlcache_read_castout_stats_cs Number of IXLCACHE "read castout stats" requests
DBS002C_QBGLDN ixlcache_delete_name_dn Number of IXLCACHE "delete name" requests
DBS002C_QBGLRD ixlcache_read_dirinfo Number of IXLCACHE "read dirinfo" requests
DBS002C_QBGLRG register_page_requests Number of "register page" requests
DBS002C_QBGLDG unregister_page Number of "unregister page" requests
DBS002C_QBGL2F writes_changed_pages Number of writes of changed pages to the secondary GBP that failed due to a lack of storage
DBS002C_QBGL2S completion_checks_for_writes Number of completion checks for writes to the secondary GBP that were suspended because the write had not yet completed
DBS002C_QBGL2D ixlcache_delete_name_list Number of IXLCACHE "delete name list" requests to the secondary GBP
DBS002C_QBGL2R ixlcache_read_castout_stats_2r Number of IXLCACHE "read castout stats" requests to the secondary GBP
DBS002C_QBGL2N ixlcache_delete_name_2n Number of IXLCACHE "delete name" requests to the secondary GBP
DBS002C_QBGLHS ixlcache_invocations_hs Number of IXLCACHE invocations for primary GBP
DBS002C_QBGL2H ixlcache_invocations_2h Number of IXLCACHE invocations for seconday GBP
DBS002C_QBGLP1 p_lock_requests_1 Number of page P lock, lock requests for space map pages
DBS002C_QBGLP2 p_lock_requests_2 Number of page P lock, lock requests for data pages
DBS002C_QBGLP3 p_lock_requests_3 Number of page P lock, lock requests for index leaf pages
DBS002C_QBGLU1 p_unlock_requests_1 Number of page P lock, unlock requests
DBS002C_QBGLS1 p_lock_suspensions_1 Number of page P lock, lock suspensions for space map pages
DBS002C_QBGLS2 p_lock_suspensions_2 Number of page P lock, lock suspensions for data pages
DBS002C_QBGLS3 p_lock_suspensions_3 Number of page P lock, lock suspensions for index leaf pages
DBS002C_QBGLN1 p_lock_negotiation_1 Number of page P lock, lock negotations for space map pages
DBS002C_QBGLN2 p_lock_negotiation_2 Number of page P lock, lock negotations for data pages
DBS002C_QBGLN3 p_lock_negotiation_3 Number of page P lock, lock negotations for index leaf pages
DBS002C_QBGLWA page_write_around Number of page in write around