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 I002A for SMF 100 IFCID 002, which includes PROD section, SQL stats.
Fields
Stored in dbsi002a
| SMF Field | DB Column name | Description | Unit |
|---|---|---|---|
| DBSHD_RTY | record_type | Value is always I002A | |
| 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 | |
| DBS002A_QXSELECT | selects | Number of selects | |
| DBS002A_QXINSRT | inserts | Number of inserts | |
| DBS002A_QXUPDTE | updates | Number of updates | |
| DBS002A_QXDELET | deletes | Number of deletes | |
| DBS002A_QXDESC | describes | Number of describes | |
| DBS002A_QXPREP | prepares | Number of describes | |
| DBS002A_QXOPEN | opens | Number of opens | |
| DBS002A_QXCLOSE | closes | Number of closes | |
| DBS002A_QXCRTAB | create_table | Number of create table | |
| DBS002A_QXCRINX | create_index | Number of create index | |
| DBS002A_QXCTABS | create_tablespace | Number of create tablespace | |
| DBS002A_QXCRSYN | create_synonym | Number of create synonym | |
| DBS002A_QXCRDAB | create_database | Number of create database | |
| DBS002A_QXCRSTG | create_storage_group | Number of create storage group | |
| DBS002A_QXDEFVU | create_view | Number of create view | |
| DBS002A_QXDRPIX | drop_index | Number of drop index | |
| DBS002A_QXDRPTA | drop_table | Number of drop table | |
| DBS002A_QXDRPTS | drop_tablespace | Number of drop tablespace | |
| DBS002A_QXDRPDB | drop_database | Number of drop database | |
| DBS002A_QXDRPSY | drop_synonym | Number of drop synonym | |
| DBS002A_QXDRPST | drop_storage_group | Number of drop storage group | |
| DBS002A_QXDRPVU | drop_view | Number of drop view | |
| DBS002A_QXALTST | alter_storage_group | Number of alter storage group | |
| DBS002A_QXFETCH | fetchs | Number of fetchs | |
| DBS002A_QXALTTS | alter_tablespace | Number of alter tablespace | |
| DBS002A_QXALTTA | alter_table | Number of alter table | |
| DBS002A_QXALTIX | alter_index | Number of alter index | |
| DBS002A_QXCMTON | comment_on | Number of comment on | |
| DBS002A_QXLOCK | lock_table | Number of lock table | |
| DBS002A_QXGRANT | number_of_ rename to number_of_grants in 4.1.0 | Number of grant | |
| DBS002A_QXREVOK | number_of_revoke | Number of revoke | |
| DBS002A_QXINCRB | incremental_binds_excluded_prepares | Number of incremental binds excluded prepares | |
| DBS002A_QXLABON | label_on | Number of label on | |
| DBS002A_QXSETSQL | set_current_sqlid | Number of set current SQLID | |
| DBS002A_QXCRALS | create_alias | Number of create alias | |
| DBS002A_QXDRPAL | drop_alias | Number of drop alias | |
| DBS002A_QXMIAP | times_rid_list_processing_used | Number of times RID list processing used | |
| DBS002A_QXNSMIAP | times_rid_list_was_not_used | Number of times RID list was not used because no storage was available to hold the list of RIDs | |
| DBS002A_QXMRMIAP | qxmrmiap | Number of times one or more RID list were not used because the number of RID exceeded one or more internal limits | |
| DBS002A_QXSETHV | set_host_variable_statements | Number of set host-variable statements we don't trace the contents of the special register | |
| DBS002A_QXALDAB | alter_database | Number of alter database | |
| DBS002A_QXDRPPKG | drop_package_statements | Number of drop package statements | |
| DBS002A_QXDSCRTB | describe_table_statements | Number of describe table statements | |
| DBS002A_QXMAXDEG | qxmaxdeg | Maximum degree of parallelism among the parallel groups to indicate the extent to which query parallelism applies | |
| DBS002A_QXTOTGRP | total_parallel_groups_executed | Number of total number number of parallel groups executed | |
| DBS002A_QXDEGCUR | qxdegcur | Total number of parallel groups which fell back to sequential mode because the cursor may be used in UPDATE/DELETE | |
| DBS002A_QXDEGESA | qxdegesa | Total number of parallel groups which fell back to sequential mode due to lack of ESA sort support | |
| DBS002A_QXDEGBUF | qxdegbuf | Total number of parallel groups that have a planned degree greater than one at run time, but fell back to sequential mode because of storage shortage or contention on the buffer pool | |
| DBS002A_QXREDGRP | qxredgrp | Total number of parallel groups that have a planned degree greater than one at run time, but were processed to a parallel degree less than planned because of a storage shortage or contention on the buffer pool | |
| DBS002A_QXNORGRP | qxnorgrp | Total number of parallel groups that have a planned degree greater than one at run time, and were executed to the same degree because of sufficient storage on the buffer pool. | |
| DBS002A_QXCON1 | connect_type1_statements_executed | Number of connect type 1 statements executed | |
| DBS002A_QXCON2 | connect_type2_statements_executed | Number of connect type 2 statements executed | |
| DBS002A_QXREL | release_statements_executed | Number of release statements executed | |
| DBS002A_QXSETCON | set_connection_statements_executed | Number of set connection statements executed | |
| DBS002A_QXSETCDG | set_current_degree_statements_executed | Number of set current degree statements executed | |
| DBS002A_QXSETCRL | set_current_rules_statements | Number of set current rules statements | |
| DBS002A_QXCALL | sql_call_statements_executed | Number of SQL call statements executed | |
| DBS002A_QXCALLAB | stored_procedure_abended | Number of times a stored procedure abended | |
| DBS002A_QXCALLTO | sql_call_statements_timed_out | Number of times an SQL call statement timed out | |
| DBS002A_QXCALLRJ | sql_call_statements_rejected | Number of times an SQL call statement was rejected | |
| DBS002A_QXCOORNO | qxcoorno | Total number of parallel groups executed on a single DB2 due to the coordinator subsystem parameter being set to NO. When the statement was bound, the coordinator subsystem parameter was set to YES | |
| DBS002A_QXISORR | qxisorr | Total number of parallel groups executed on a single DB2 due to repeatable-read or read-stability isolation | |
| DBS002A_QXCRGTT | create_global_temporary_table | Number of create global temporary table | |
| DBS002A_QXXCBPNX | qxxcbpnx | Total number of parallel groups that were intended to run across the data sharing group. This count is only incremented on the parallelism coordinator at run time | |
| DBS002A_QXXCSKIP | qxxcskip | Number of times that the parallelism coordinator had to bypass a DB2 when distributing tasks because there was not enough buffer pool storage on one or more DB2 members | |
| DBS002A_QXALOCL | associate_locator_statements_executed | Number of associate locator statements | |
| DBS002A_QXALOCC | allocate_cursor_statements_executed | Number of allocate cursor statements | |
| DBS002A_QXSTFND | qxstfnd | Number of times a PREPARE request was satisfied by making a copy from the prepared statement cache | |
| DBS002A_QXSTNFND | qxstnfnd | Number of times a PREPARE request was received but a matching statement was not found in the prepared statement cache. Cache search is only done for DML SQL and only if cache option is active | |
| DBS002A_QXSTIPRP | qxstiprp | Number of times that an implicit prepare was performed because the KEEPDYNAMIC(YES) option was used and an OPEN, EXECUTE or DESCRIBE of a dynamic statement occurred after a COMMIT but DB2 no longer had a valid copy of the executable version of the prepared statement | |
| DBS002A_QXSTNPRP | qxstnprp | Number of times that a prepare was avoided because KEEPDYNAMIC(YES) was used along with prepared statement caching and DB2 still had the application process copy of the executable version of the prepared statement | |
| DBS002A_QXSTDEXP | qxstdexp | Number of times that an application process executable copy of a prepared statement was discarded due to the MAXKEEPD system limit being exceeded | |
| DBS002A_QXSTDINV | qxstdinv | Number of times that a prepared statement was purged from the cache because a DROP, ALTER, or REVOKE statement was issued on a dependent object | |
| DBS002A_QXRNTAB | rename_table | Number of rename table | |
| DBS002A_QXCTRIG | create_trigger | Number of create trigger | |
| DBS002A_QXDRPTR | drop_trigger | Number of drop trigger | |
| DBS002A_QXSETPTH | set_current_path_statements | Number of set current path statements | |
| DBS002A_QXDRPFN | drop_user_defined_function | Number of drop user defined function | |
| DBS002A_QXDRPPR | drop_procedure | Number of drop procedure | |
| DBS002A_QXCDIST | create_distinct_type_statements | Number of create distinct type statements | |
| DBS002A_QXDDIST | drop_distinct_type_statements | Number of drop distinct type statements | |
| DBS002A_QXCRUDF | create_function_statements | Number of create function statements | |
| DBS002A_QXCRPRO | create_procedure_statements | Number of create procedure statements | |
| DBS002A_QXHOLDL | hold_locator_statements | Number of hold locator statements | |
| DBS002A_QXFREEL | free_locator_statements | Number of free locator statements | |
| DBS002A_QXREPOP1 | qxrepop1 | Total number of parallel groups for which DB2 reformulated the parallel portion of the access path because the sysplex configuration was different from the sysplex configuration at bind time | |
| DBS002A_QXREPOP2 | qxrepop2 | Total number of parallel groups for which DB2 reformulated the paralllel portion of the access path because there was not enough buffer pool resource | |
| DBS002A_QXCRATB | create_aux_table_statements | Number of create AUX table statements | |
| DBS002A_QXSTLOBV | max_storage_used_for_lob_values | Maximum storage used for LOB values | megabyte |
| DBS002A_QXALUDF | alter_funtion_statements | Number of alter function statements | |
| DBS002A_QXALPRO | alter_procedure_statements | Number of alter procedure statements | |
| DBS002A_QXROIMAT | direct_row_access_successful | Number of times that direct row access was successful | |
| DBS002A_QXROIIDX | direct_row_reverted_index | Number of times an attempt to use direct row access reverted to using an index to locate a record | |
| DBS002A_QXROITS | direct_row_reverted_table_space | Number of times an attempt to use direct row access reverted to using a table space scan to locate a record | |
| DBS002A_QXSTTRG | statement_trigger_activated | Number of times a statement trigger was activated | |
| DBS002A_QXROWTRG | row_trigger_activated | Number of times a row trigger was activated | |
| DBS002A_QXTRGERR | sqk_error_during_triggered_action | Number of times an SQL error occured during execution of a triggered action | |
| DBS002A_QXCASCDP | max_level_nested_sql_cascading | Maximum level of nested SQL cascading | |
| DBS002A_QXCAUD | user_defined_function_executed | Number of user defined function executed | |
| DBS002A_QXCAUDAB | udf_abended | Number of times a UDF abended | |
| DBS002A_QXCAUDTO | udf_timed_out | Number of times a UDF timed out waiting to be scheduled | |
| DBS002A_QXCAUDRJ | udf_rejected | Number of times a UDF was rejected | |
| DBS002A_QXSETCPR | set_current_precision_statements_executed | Number of set current precision statements | |
| DBS002A_QXDCLGTT | declare_global_temporary_table_stmts | Number of declare global temporary table STMTS | |
| DBS002A_QXDEGDTT | qxdegdtt | Total number of parallel groups that are part of a query block using a UDF and executed on a single DB2 due to the existence of a Declared Temporary Table in the application process | |
| DBS002A_QXCRESEQ | create_sequences | Number of create sequences | |
| DBS002A_QXALTSEQ | alter_sequences | Number of alter sequences | |
| DBS002A_QXDROSEQ | drop_sequences | Number of drop sequences | |
| DBS002A_QXPRRESI | qxprresi | Number of prepares for which use of an index/indexes were restricted because the index was in a pending state | |
| DBS002A_QXALTVW | alter_view | Number of alter view | |
| DBS002A_QXALTJR | alter_jar | Number of alter jar | |
| DBS002A_QXMERGE | merge_statements_executed | Number of times merge statement was executed | |
| DBS002A_QXTRTBL | truncate_table_executed | Number of times truncate table was executed | |
| DBS002A_QXCRROL | create_role | Number of create role | |
| DBS002A_QXDRPROL | drop_role | Number of drop role | |
| DBS002A_QXCRCTX | create_trusted_context | Number of create trusted context | |
| DBS002A_QXALTCTX | alter_trusted_context | Number of alter trusted context | |
| DBS002A_QXDRPCTX | drop_trusted_context | Number of drop trusted context | |
| DBS002A_QXRNIX | rename_index | Number of rename index | |
| DBS002A_QXSTXMLV | max_storage_for_xml | Number of max storage used for XML values | |
| DBS002A_QXRWSFETCHD | row_fetched | Number of rows fetched | |
| DBS002A_QXRWSINSRTD | row_inserted | Number of rows inserted | |
| DBS002A_QXRWSUPDTD | row_updated | Number of rows updated | |
| DBS002A_QXRWSDELETD | row_deleted | Number of rows deleted | |
| DBS002A_QXSTCWLP | db2_parsed_dynamic_statements | Number of times DB2 parsed dynamic statements because concentrate statements with literals behavior was in effect for the prepare of the statement for the dynamic statement cache | |
| DBS002A_QXSTCWLR | db2_relaced | Number of times DB2 replaced at least one literal in a dynamic statement because concentrate statements with literals was in effect for the prepare of the statement for dynamic statement cache | |
| DBS002A_QXSTCWLM | db2_found_matching_reusable | Number of times DB2 found a matching reusable copy of a dynamic statement in statement cache during prepare of a statement that had literals replaced because of concentrate statements with literals | |
| DBS002A_QXSTCWLD | db2_created_duplicate_stmt | Number of times DB2 created a duplicate stmt instance in the the statement cache for a dynamic statement that had literals replaced by concentrate statements with literals behavior and the duplicate stmt instance was needed because a cache match failed solely due to literal reusability criteria | |
| DBS002A_QXWFRIDS | qxwfrids | Number of times a RID list was overflown to a work file because no RIDPOOL storage was available to hold the list of RIDs | |
| DBS002A_QXWFRIDT | qxwfridt | Number of times a RID list was overflown to a work file because the number of RIDs exceeded one or more internal limits | |
| DBS002A_QXHJINCS | qxhjincs | Number of times a RID list append for a Hybrid join was interrupted because no RIDPOOL storage was available to hold the list of RIDs | |
| DBS002A_QXHJINCT | qxhjinct | Number of times a RID list append for a Hybrid join was interrupted because the number of RIDs exceeded one or more internal limits | |
| DBS002A_QXRSMIAP | qxrsmiap | Number of times a RID list retrieval for multiple index access was skipped because it was not necessary due to DB2 being able to predetermine the outcome of index AND or OR | |
| DBS002A_QXCREMP | create_mask_or_permission | Number of create mask or permission | |
| DBS002A_QXDRPMP | drop_mask_or_permission | Number of drop mask or permission | |
| DBS002A_QXALTMP | alter_mask_or_permission | Number of alter mask or permission | |
| DBS002A_QXCRTSV | create_variable | Number of create variable | |
| DBS002A_QXDRPSV | drop_variable | Number of drop variable | |
| DBS002A_QXDEGAT | qxdegat | Total number of parallel groups that fell to sequential mode | |
| DBS002A_QXSTARRAY_EXPANSIONS | array_expand_beyond_32k | Number of times an array var is expanded | |
| DBS002A_QXSTOREDGRP | parallel_group_degree_reduced | Number of parallel group reduced | |
| DBS002A_QXSTODGNGRP | parallel_group_degenerated | Number of parallel group degenerated | |
| DBS002A_QXMAXESTIDG | max_parallel_group_estimated_degree | Maximum parallel group estimated degree | |
| DBS002A_QXMAXPLANDG | max_parallel_group_planned_degree | Maximum parallel group planned degree | |
| DBS002A_QXSISTOR | sparse_index_disabled | Number of times that sparse index was disabled | |
| DBS002A_QXSIWF | sparse_index_built_qtst | Number of times sparse index built workflow for probing |