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 I001 for SMF 100 IFCID 001, including PROD section, Control Address Space stats, Command stats, Storage Manager stats, DDF stats, z/OS System Stats.
Fields
Stored in dbsi001
| SMF Field | DB Column name | Description |
|---|---|---|
| DBSHD_RTY | record_type | Value is always I001 |
| DBSHD_SID | sid | SID |
| DBSHD_DTTM | write_datetime | Datetime 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 | ORIG. 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 | Server name 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 |
| DBS001_QWSAPROC | last_4_char_procedure | Last 4 char of procedure |
| DBS001_QWSAEJST | as_job_step_timer | JOB step timer value of A.S. |
| DBS001_QWSASRBT | as_srb_timer | SRB timer value for A.S. |
| DBS001_QWSAASID | as_asid | ASID value for A.S. |
| DBS001_QWSAASCB | ascb_token | ASCB token |
| DBS001_QWSAPSRB | as_premtable_srb_timer | PREEMTABLE SRB timer for A.S. |
| DBS001_QWSAPSRB_ZIIP | ziip_premtable_srb_timer | PREEMTABLE SRB ZIIP timer |
| DBS001_Q9STCTR0 | display_database | Display database |
| DBS001_Q9STCTR1 | display_thread | Display thread |
| DBS001_Q9STCTR2 | display_utility | Display utility |
| DBS001_Q9STCTR3 | recover_bsds | Recover BSDS |
| DBS001_Q9STCTR4 | recover_indoubt | Recover INDOUBT |
| DBS001_Q9STCTR5 | start_database | Start database |
| DBS001_Q9STCTR6 | start_trace | Start trace |
| DBS001_Q9STCTR7 | start_subsystem | Start subsystem |
| DBS001_Q9STCTR8 | stop_database | Stop database |
| DBS001_Q9STCTR9 | stop_trace | Stop trace |
| DBS001_Q9STCTRA | stop_subsystem | Stop subsystem |
| DBS001_Q9STCTRB | term_utility | Term utility |
| DBS001_Q9STCTRC | dis_trace | DIS trace |
| DBS001_Q9STCTRD | reset_generic_clu | Reset generic clu |
| DBS001_Q9STEROR | unrecognized_command | Unrecognized command |
| DBS001_Q9STCTRE | start_rlimit | Start RLimit |
| DBS001_Q9STCTRF | stop_rlimit | Stop RLimit |
| DBS001_Q9STCTRG | display_rlimit | Display RLimit |
| DBS001_Q9STCTRH | modify_trace | Modifiy trace |
| DBS001_Q9STCTRI | start_ddf | Start DDF |
| DBS001_Q9STCTRJ | stop_ddf | Stop DDF |
| DBS001_Q9STCTRK | cancel_ddf_thread | Cancel DDF thread |
| DBS001_Q9STCTRL | display_location | Display location |
| DBS001_Q9STCTRM | archive_log | Archive log |
| DBS001_Q9STCTRN | alter_bufferpool | Alter bufferpool |
| DBS001_Q9STCTRO | display_bufferpool | Display bufferpool |
| DBS001_Q9STCTRP | set_archive | Set archive |
| DBS001_Q9STCTRQ | display_archive | Display archive |
| DBS001_Q9STCTRR | reset_indoubt | Reset INDOUBT |
| DBS001_Q9STCTRS | alter_gbpool | Alter GB pool |
| DBS001_Q9STCTRT | display_gbpool | Display GB pool |
| DBS001_Q9STCTRU | display_procedure | Display procedure |
| DBS001_Q9STCTRV | start_procedure | Start procedure |
| DBS001_Q9STCTRW | stop_procedure | Stop procedure |
| DBS001_Q9STCTRX | dis_group | DIS group |
| DBS001_Q9STCTRY | alter_utility | Alter utility |
| DBS001_Q9STCTRZ | display_function | Display function |
| DBS001_Q9STCTX0 | start_function | Start function |
| DBS001_Q9STCTX1 | stop_function | Stop function |
| DBS001_Q9STCTX2 | set_log | Set log |
| DBS001_Q9STCTX3 | display_log | Display log |
| DBS001_Q9STCTX4 | set_system_parameter | Set system parameter |
| DBS001_Q9STCTX5 | display_ddf | Display DDF |
| DBS001_Q9STCTAD | access_database | Access database |
| DBS001_Q9STCTSS | start_profile | Start profile |
| DBS001_Q9STCTST | stop_profile | Stop profile |
| DBS001_Q9STCTSD | display_profile | Display profile |
| DBS001_Q9STCTDA | display_acceleration | Display acceleration |
| DBS001_Q9STCTSA | start_acceleration | Start acceleration |
| DBS001_Q9STCTXA | stop_acceleration | Stop acceleration |
| DBS001_Q9STCDMD | modify_ddf | Modify DDF |
| DBS001_QSSTGPLF | get_fixed_pool | Get fixed pool |
| DBS001_QSSTFPLF | free_fixed_pool | Free fixed pool |
| DBS001_QSSTFREF | freemained_fixed_pool | FREEMAINED SEGMENTS IN 31-BIT |
| DBS001_QSSTEXPF | getmained_fixed_pool | EXTENSIONS GETMAINED IN FIXED |
| DBS001_QSSTCONF | contracted_fixed_pool | CONTRACTED SEGMENTS IN 31-BIT |
| DBS001_QSSTGPLV | get_variable_pool | Get variable pool |
| DBS001_QSSTFPLV | free_variable_pool | Free variable pool |
| DBS001_QSSTFREV | freemained_variable_pool | FREEMAINED SEGMENTS IN 31-BIT |
| DBS001_QSSTEXPV | getmained_variable_pool | EXTENSIONS GETMAINED IN 31-BIT |
| DBS001_QSSTCONV | contracted_variable_pool | CONTRACTED SEGMENTS IN 31-BIT |
| DBS001_QSSTGETM | getmained_request | 31-BIT GETMAIN REQUEST |
| DBS001_QSSTFREM | freemained_request | 31-BIT FREEMAIN REQUEST |
| DBS001_QSSTRCNZ | get_request_failure | CONDITIONAL 31-BIT GET REQUEST |
| DBS001_QSSTCONT | full_system_contractions | Full system contractions |
| DBS001_QSSTCRIT | qsstcrit | Critical storage shortage after |
| DBS001_QSSTABND | qsstabnd | Abends due to local storage |
| DBS001_QSST_SGETM | stack_request_required_getmain | 31-BIT STACK REQUEST REQUIRED |
| DBS001_QSST_SGETR | ool_stack_get_requests_31b | 31-BIT OUT OF LINE STACK GET REQUEST |
| DBS001_QSST_SGETEXT | stack_request_satisfied_31b | 31-BIT STACK REQUEST SATISFIED |
| DBS001_QSST_SFREEM | free_request_required | 31-BIT FREE REQUEST REQUIRED |
| DBS001_QSST_SFREER | ool_stack_free_requests_31b | NUMBER OF OUT OF LINE 31-BIT |
| DBS001_QSST_D64POST | ool_stack_deallocation_requests | NUMBER OF OUT OF LINE 64-BIT |
| DBS001_QSST_A64POST | ool_stack_allocation_requests | NUMBER OF OUT OF LINE 64-BIT |
| DBS001_QSST_A64WAIT | ool_stack_allocation_wait_requests | NUMBER OF OUT OF LINE 64-BIT |
| DBS001_QSST_M64DISNUM | stack_segment_required_discard | NUMBER OF 64-BIT STACK SEGMENT |
| DBS001_QSST_M64DISPGS | stack_pages_discarded | NUMBER OF 64-BIT STACK PAGES |
| DBS001_QSST_SGETR64 | ool_stack_get_requests_64b | NUMBER OF OUT OF LINE 64-BIT |
| stack_request_satisfied_64b | ||
| DBS001_QSST_SGETDEXT64 | stack_request_required | 64-BIT STACK REQUEST REQUIRED |
| DBS001_QSST_SFREER64 | ool_stack_free_requests_64b | NUMBER OF OUT OF LINE 64-BIT |
| DBS001_QSST_SFREEDEXT64 | stack_free_required | 64-BIT STACK FREE REQUIRED |
| DBS001_QSST_DISCARDMODE64 | real_frame_discard_mode | Real frame discard mode |
| DBS001_QSST_RSMAX_WARN | dbs001_qsst_rsmax_warn | NUMBER IMES REALSTORAGE_MAX |
| DBS001_QSST_P64DISNUM | pools_contracted | NUMBER OF 64-BIT POOLS |
| DBS001_QSST_P64DISBLK | pool_blocks_required_discard | NUMBER OF 64-BIT POOL BLOCKS |
| DBS001_QSST_P64DISPGS | pool_pages_discarded | NUMBER OF 64-BIT POOL PAGES |
| DBS001_QSST_CONTSTOR_NUM | qsst_contstor_num | NUMBER OF 31-BIT AGENT LOCAL POOLS |
| DBS001_QLSTLOCN | remote_site_location_name | Location name of the remote |
| DBS001_QLSTPRID | remote_location_prdid | PRDID of the remote location |
| DBS001_QLSTCNVS | conversations_site_to_remote | Number of conversation from site to remote |
| DBS001_QLSTCNVT | conversations_deallocated_site_to_remote | Number of conversation deallocated from remote to site |
| DBS001_QLSTCNVR | conversations_remote_to_site | Number of conversation from remote to site |
| DBS001_QLSTMSGS | remote_site_msg_sent | Number of message sent |
| DBS001_QLSTMSGR | remote_site_msg_received | Number of message received |
| DBS001_QLSTSQLS | remote_site_sql_sent | Number of SQL statements sent |
| DBS001_QLSTSQLR | remote_site_sql_received | Number of SQL statements received |
| DBS001_QLSTBYTS | remote_site_data_sent | Number of bytes of data sent |
| DBS001_QLSTBYTR | remote_site_data_received | Number of bytes of data received |
| DBS001_QLSTROWS | remote_site_row_sent | Number of rows of data sent |
| DBS001_QLSTROWR | remote_site_row_received | Number of rows of data received |
| DBS001_QLSTBTBF | blocks_fetch_transmission | Number of blocks transmitted |
| DBS001_QLSTBRBF | blocks_fetch_received | Number of blocks received |
| DBS001_QLSTCOMS | qlstcoms | Number of commit requests sent to the server |
| DBS001_QLSTCOMR | qlstcomr | Number of commit requests received |
| DBS001_QLSTABRS | qlstabrs | Number of abort requests sent to the server |
| DBS001_QLSTABRR | qlstabrr | Number of abort requests received |
| DBS001_QLSTINDT | threads_indoubt | Number of threads that became indoubt |
| DBS001_QLSTCNVQ | conversation_requests_queued | Number of conversation request queued |
| DBS001_QDSTQDBT | qdstqdbt | |
| DBS001_QDSTCSTR | cold_start | Number of cold start |
| DBS001_QDSTWSTR | warm_start | Number of warm start |
| DBS001_QDSTRSAT | resynchronization_connections_attempted | Number of resynchronization connection attempted |
| DBS001_QDSTRSSU | resynchronization_connections_succeeded | Number of resynchronization connection succeeded |
| DBS001_QDSTQCRT | rejected_connection | Number of rejected connection |
| DBS001_QDSTQCIT | type1_inactive_dbats | Number of current inactive dbats |
| DBS001_QDSTQMIT | type1_max_inactive_dbats_existed | |
| DBS001_QDSTCNAT | current_active_disconnected_dbats | Number of current active disconnected dbats |
| DBS001_QDSTHWAT | max_active_disconnected_dbats | QDSTCNAT HIGH WATER MARK. |
| DBS001_QDSTHWDT | max_remote_connections_existed | Maximum number of remote connection |
| DBS001_QDSTNITC | type1_terminated | Number of threads terminated |
| DBS001_QDSTCIN2 | type2_inactive_dbats | |
| DBS001_QDSTMIN2 | type2_max_concurrent_inactive_existed | QDSTCIN2 HIGH WATER MARK. |
| DBS001_QDSTQIN2 | qdstqin2 | Number of received requests |
| DBS001_QDSTNQR2 | qdstnqr2 | |
| DBS001_QDSTMQR2 | qdstmqr2 | QDSTNQR2 HIGH WATER MARK. |
| DBS001_QDSTNADS | qdstnads | |
| DBS001_QDSTMADS | qdstmads | QDSTNADS HIGH WATER MARK. |
| DBS001_QDSTNDBA | qdstndba | |
| DBS001_QDSTPOOL | qdstpool | |
| DBS001_QDSTNCQC | qdstncqc | Number of queued client |
| DBS001_QDSTNARD | current_active_dbats | Current number of active dbats |
| DBS001_QDSTMARD | max_active_dbats | Maximum number of active dbats |
| DBS001_QDSTNQMN | qdstnqmn | RELATIVE TO QDSTNQR2 |
| DBS001_QDSTNQMX | qdstnqmx | RELATIVE TO QDSTNQR2 |
| DBS001_QDSTNQAV | qdstnqav | RELATIVE TO QDSTNQR2 |
| DBS001_QDSTNCCW | qdstnccw | Number of queued client |
| DBS001_QWOSLNCP | cps_on_lpar | Number of CPS on LPAR |
| DBS001_QWOSLPRU | cpu_utilization_lpar | CPU UTILIZATION LPAR |
| DBS001_QWOSDB2U | cpu_utilization_db2_subsys | CPU UTILIZATION DB2 SUBSYS |
| DBS001_QWOSMSTU | cpu_utilization_db2_mstr | CPU UTILIZATION DB2 MSTR |
| DBS001_QWOSDBMU | cpu_utilization_db2_dbm1 | CPU UTILIZATION DB2 DBM1 |
| DBS001_QWOSLPIR | page_in_rate_lpar | PAGE-IN RATE LPAR |
| DBS001_QWOSDPIR | page_in_rate_db2_subsys | PAGE-IN RATE DB2 SUBSYS |
| DBS001_QWOSLRST | real_storage_lpar | Real storage on LPAR |
| DBS001_QWOSLRSF | free_real_storage_lpar | Free real storage on LPAR |
| DBS001_QWOSDRSU | used_real_storage_db2_subsys | Used real storage on DB2 subsystem |
| DBS001_QWOSLVST | virtual_storage_lpar | Virtual storage on LPAR |
| DBS001_QWOSLVSF | free_virtual_storage_lpar | Free virtual storage on LPAR |
| DBS001_QWOSDVSU | used_virtual_storage_db2_subsys | Number of used virtual storage DB2 subsystem |
| DBS001_QWOSLUIC | unreferenced_interval_count | Number of unreferenced interval |