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 I002D for SMF 100 IFCID 002, which includes PROD section, Service Controller stats, Locking stats, EDM Pool stats.
Fields
Stored in dbsi002d
| SMF Field | DB Column name | Description | Unit |
|---|---|---|---|
| DBSHD_RTY | record_type | Value is always I002D | |
| 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 | |
| DBS002D_QTALLOCA | allocation_attempts | Allocation attempts | |
| DBS002D_QTALLOC | allocation_succeeded | Successful allocations | |
| DBS002D_QTABINDA | automatic_bind_attempts | Automatic bind attempts | |
| DBS002D_QTABIND | automatic_bind_succeeded | Successful automatic binds | |
| DBS002D_QTINVRID | invalid_resources_ids | Invalid resource-IDs | |
| DBS002D_QTBINDA | bind_add_sub_commands | Bind (ADD) sub-commands | |
| DBS002D_QTBINDR | bind_replace_sub_commands | Bind (REPLACE) sub-commands | |
| DBS002D_QTTESTB | bind_test_no_plan_id | Test binds (no Plan-ID) | |
| DBS002D_QTPLNBD | plans_bound | Plans successfully bound | |
| DBS002D_QTREBIND | rebind_sub_commands | Rebind subcommands | |
| DBS002D_QTRBINDA | rebind_plan_attempts | Attempts to rebind a plan | |
| DBS002D_QTPLNRBD | plans_rebound | Plans rebound | |
| DBS002D_QTFREE | free_sub_commands | Free subcommands | |
| DBS002D_QTFREEA | free_plan_attempts | Attempts to free a plan | |
| DBS002D_QTPLNFRD | freed_plans | Plans freed | |
| DBS002D_QTAUCHK | total_authorization_checks | Number of authorization checks | |
| DBS002D_QTAUSUC | total_successful_authorization_checks | Number of successful authorization checks | |
| DBS002D_QTDSOPN | current_datasets_opened | Number of datasets currently open | |
| DBS002D_QTMAXDS | max_datasets_concurrent_opened | Maximum number of datasets opened concurrently | |
| DBS002D_QTAUCCH | successful_authorization_checks_plans | Number of successful authorization checks for plans using a plan cache or public authorization check | |
| DBS002D_QTAUPUB | successful_authorization_checks_plans_public | Number of successful authorization checks for plans execute authority held by public | |
| DBS002D_QTMAXPB | max_not_in_use_datasets | Maximum not in-use datasets | |
| DBS002D_QTPACAUT | successful_authorization_checks_packages | Number of successful authorization checks for packages using the package authorization cache includes public authorization checks | |
| DBS002D_QTPACPUB | successful_authorization_checks_packages_public | Number of successful authorization checks for package execute authority held by public | |
| DBS002D_QTPACNOT | package_authorization_checks_not_cache | Number of package authorization checks that could not make use of the package authorization cache | |
| DBS002D_QTREOPN | datasets_not_in_use_to_in_use | Number of times that not in use open datasets became in-use | |
| DBS002D_QTPACOW1 | db2_overwrite_authid_package | Number of times DB2 overwrote an authorization ID in the package authorization cache | |
| DBS002D_QTPACOW2 | db2_overwrite_package | Number of times DB2 overwrote a package entry in the package authorization cache | |
| DBS002D_QTDSDRN | datasets_not_in_use_to_close | Number of not in-use datasets closed due to DXMAX/DD limit | |
| DBS002D_QTPCCT | datasets_rw_to_ro | Number of infrequently updated datasets that are converted from R/W to R/O state | |
| DBS002D_QTSLWDD | datasets_not_in_use_and_not_closed | Number of datasets that are not in-use but are not closed | |
| DBS002D_QTBINDPA | bind_add_sub_package_subcommands | Bind (ADD) package subcommands | |
| DBS002D_QTBINDPR | bind_rep_sub_package_subcommands | Bind (REP) package subcommands | |
| DBS002D_QTPKGBD | packages_bound | Number of package bounds | |
| DBS002D_QTRBINDP | rebind_package_subcommands | Rebind package subcommands | |
| DBS002D_QTRBNDPA | rebind_package_attempts | Number of attempts to rebin a package | |
| DBS002D_QTPKGRBD | package_rebound | Number of package rebounds | |
| DBS002D_QTFREEP | free_package_subcommands | Free package subcommands | |
| DBS002D_QTFREEAP | free_package_attempts | Number of attempts to free a package | |
| DBS002D_QTPKGFRD | packages_freed | Number of packages freed | |
| DBS002D_QTAUTOBA | autobind_package_attempts | Number of attempts to autobind a package | |
| DBS002D_QTPKABND | packages_autobound | Number of packages autobound | |
| DBS002D_QTPKALLA | allocate_package_attempts | Number of attempts to allocate a package | |
| DBS002D_QTPKALL | packages_allocated | Number of packages allocated | |
| DBS002D_QTRACAUT | successful_authorization_checks_routines_public | Number of successful authorization checks for routines using the routine authorization cache includes public authorization checks | |
| DBS002D_QTRACPUB | successful_authorization_checks_routines | Number of successful authorization checks for routines execute authority held by public | |
| DBS002D_QTRACNOT | routine_authorization_checks_not_cache | Number of routine authorization checks that could not make use of the routine authorization cache | |
| DBS002D_QTRACOW1 | db2_overwrite_authid_routine | Number of times DB2 overwrote an authorization ID in the routine authorization cache | |
| DBS002D_QTRACOW2 | db2_overwrite_authid_routine_entry | Number of times DB2 overwrote a routine entry in the routine authorization cache | |
| DBS002D_QTRACNAC | db2_cant_add | Number of times DB2 was not able to add an entry to the routine authorization cache | |
| DBS002D_QTXADEA | deadlock_count | Number of deadlock | |
| DBS002D_QTXASLOC | suspends | Number of suspends because of lock conflicts | |
| DBS002D_QTXATIM | timeout_counts | Number of timeout | |
| DBS002D_QTXALES | lock_escal_shared_counts | Number of lock escal shared | |
| DBS002D_QTXALEX | lock_escal_exclusive_counts | Number of lock escal exclusive | |
| DBS002D_QTXARLID | resource_limit_table_id | Resource limit table ID | |
| DBS002D_QTXAPREC | limit_type |
|
|
| DBS002D_QTXASLMT | limit_sus_asutime | Limit in SU's ASU time | |
| DBS002D_QTXACLMT | limit_cpu | CPU limit | 16 microsecond |
| DBS002D_QTXACHUS | highest_cpu_used | Highest CPU used | 16 microsecond |
| DBS002D_QTXASLAT | suspend_count_latch_conflict | Number of suspend latch conflict | |
| DBS002D_QTXASOTH | suspend_count_other_conflict | Number of suspend other conflict | |
| DBS002D_QTXALOCK | lock_request_count | Number of lock request | |
| DBS002D_QTXAUNLK | unlock_request_count | Number of unlock request | |
| DBS002D_QTXAQRY | query_request_count | Number of query request | |
| DBS002D_QTXACHG | change_request_count | Number of change request | |
| DBS002D_QTXAIRLM | other_irlm_request_count | Number of other IRLM request | |
| DBS002D_QTXACLNO | claim_requests | Number of claim request | |
| DBS002D_QTXACLUN | claim_requests_unsusccessful | Number of unsuccessful claim request | |
| DBS002D_QTXADRNO | drain_requests | Number of drain request | |
| DBS002D_QTXADRUN | drain_request_successful | Number of unsuccessful drain request | |
| DBS002D_QISEFAIL | fail_pool_full | Number of fail do to pool full | |
| DBS002D_QISECTG | request_ct_sections | Number of request for CT sections | |
| DBS002D_QISECTL | load_ct_section_dasd | Number of load CT sections from DASD | |
| DBS002D_QISESTMT | stmts_edm_pool | Number of STMTS in EDM pool | |
| DBS002D_QISEDBDG | requests_dbd | Number of requests for DBD | |
| DBS002D_QISEDBDL | loading_dbd_dasd | Number of loading DBD from DASD | |
| DBS002D_QISEKTG | request_pt_sections | Number of requests for PT sections | |
| DBS002D_QISEKTL | load_pt_sections_dasd | Number of load PT sections from DASD | |
| DBS002D_QISEDSI | inserts_dyn_cache | Number of inserts for DYN cache | |
| DBS002D_QISEDSG | requests_dyn_cache | Number of requests for DYN cache | |
| DBS002D_QISEDBD | pages_used_dbd | Number of pages used for DBD | |
| DBS002D_QISEDFAL | fail_dbd_pool_full | Number of fail due to DBD pool full | |
| DBS002D_QISEDPGE | pages_dbd_pool | Number of pages in DBD pool | |
| DBS002D_QISEDFRE | free_pg_dbd_free_chain | Number of free PG in DBD free chain | |
| DBS002D_QISEDYNP | pgs_used_stmt_pool | Number of PGs used in STMT pool | |
| DBS002D_QISECFAL | fail_stmt_pool_full | Number of fail due to STMT pool full | |
| DBS002D_QISECPGE | pgs_stmt_pool | Number of PGs in STMT pool | |
| DBS002D_QISECFRE | free_pg_stmt_free_chain | Number of free PG in STMT free chain | |
| DBS002D_QISESKCT | pages_used_skct | Number of pages used for SKCT | |
| DBS002D_QISESKPT | pages_used_skpt | Number of pages used for SKPT | |
| DBS002D_QISEKFAL | fail_stmt_skel_pool_full | Number of fail due to STMT skeleton pool full | |
| DBS002D_QISEKPGE | pages_skel_edm_pool | Number of pages in skeleton EDM pool | |
| DBS002D_QISEKFRE | free_pg_skel_edm_pool_free | Number of free PG in skeleton EDM pool free cache | |
| DBS002D_QISECTA | pages_used_ct_above_bar | Number of pages used in CT above bar | |
| DBS002D_QISEKTA | pages_used_pt_above_bar | Number of pages used in PT above bar | |
| DBS002D_QISESFAL | fail_stmt_abv_pool_full | Number of fail due to STLT above pool full | |
| DBS002D_QISESPGE | pages_stmt_abv_edm_pool | Number of pages in STMT abobe EDM pool | |
| DBS002D_QISESFRE | free_pg_stmt_abv_edm_pool_free | Number of free PG in STMT above EDM pool | |
| DBS002D_QISEKNFM | cached_not_found_record_located | Number of cached not-found record located | |
| DBS002D_QISEKNFA | not_found_record_added_to_cache | Number of not-found record added to cache | |
| DBS002D_QISEKNFR | not_found_record_removed_from_cache | Number of not-found record removed from cache | |
| DBS002D_QISEKSPG | shareable_static_sql_statement_request | Number of shareable static SQL statement request | |
| DBS002D_QISEKLRU | total_stealable_skel_pages | Number of stealable skeleton pages | |
| DBS002D_QISEDLRU | total_stealable_dbd_pages | Number of stealable DBD pages | |
| DBS002D_QISESQCB | storage_allocated_plan_btb | Storage allocated to plan BTB | |
| DBS002D_QISESQKB | storage_allocated_package_btb | Storage allocated to package BTB | |
| DBS002D_QISESQCA | storage_allocated_plan_atb | Storage allocated to plan ATB | |
| DBS002D_QISESQKA | storage_allocated_package_atb | Storage allocated to package BTB | |
| DBS002D_QISEKSPA8 | total_storage_allocated_shareable | Total storage allocated to shareable |