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 I002B for SMF 100 IFCID 002, which includes PROD section and Buffer Manager Stats.
Fields
Stored in dbsi002b
| SMF Field | DB Column name | Description |
|---|---|---|
| DBSHD_RTY | record_type | Value is always I002B |
| 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 |
| DBS002B_QBSTGET | page_requests | Number of get page requests |
| DBS002B_QBSTRIO | sync_read_io | Number of synchronize read I/O operations performed |
| DBS002B_QBSTDWV | vertical_times_deferred_write | Number of times vertical deffered write |
| DBS002B_QBSTXFL | page_failures_vpool_full | Number of get page failures due to vpool full |
| DBS002B_QBSTXFV | expansion_failure_shortage | Number of expansion failure due to shortage of virtual storage |
| DBS002B_QBSTSWS | setw_issued_system_pages | Number of setw issued for system pages |
| DBS002B_QBSTPWS | pages_writes_system_pages | Number of page writes for system pages |
| DBS002B_QBSTWIO | async_write_io | Number of asynchronous write I/O operations performed |
| DBS002B_QBSTCBA | current_active_buffers | Number of current active buffers |
| DBS002B_QBSTRPI | pagein_required_read_io | Number of page-in required for read I/O |
| DBS002B_QBSTWPI | pagein_required_write_io | Number of page-in required for write I/O |
| DBS002B_QBSTDSO | datasets_opened_successfully | Number of data sets opened successfully |
| DBS002B_QBSTIMW | immed_write_io | Number of immed (synchronous) write I/O |
| DBS002B_QBSTSEQ | seq_prefetch_requested | Number of seq prefetch requested |
| DBS002B_QBSTSPP | page_read_seq_prefetch | Number of page read due to sequential prefetch |
| DBS002B_QBSTSPD | sec_prefetch_disabled_spd | Number of sequential prefetch disabled due to unavailable buffer resources |
| DBS002B_QBSTREE | sec_prefetch_disabled_ree | Number of sequential prefetch disabled due to unavailable read engine |
| DBS002B_QBSTDWT | deferred_write8threshold | Number of times that deferred write threshold is reached |
| DBS002B_QBSTDMC | data_manager_buffer | Number of times that the data manager buffer critical threshold has been reached |
| DBS002B_QBSTMIG | migrated_datasets | Number of times that migrated data sets encountered |
| DBS002B_QBSTRTO | recall_timeouts | Number of recall timeouts |
| DBS002B_QBSTPIO | seq_prefetch_read_io | Number of seq prefetch (asynchronous) read I/O |
| DBS002B_QBSTWKPD | wk_prefetch_aborted | Number of wk prefetch aborted due to a zero prefetch quantity |
| DBS002B_QBSTMAX | wk_file_not_created | Number of wk file cannot be created due to insufficient buffer resources |
| DBS002B_QBSTWFM | max_workfiles_allocated | Maximum workfiles ever been allocated |
| DBS002B_QBSTWDRP | pages_destructive_read | Number of pages for which destructive read was requested |
| DBS002B_QBSTWBVQ | pages_dequeued_dataset_deferred | Number of pages dequeued from data set deferred write queue for destructive read request |
| DBS002B_QBSTWFR | requests_query_workfiles | Number of requests to query for worfiles |
| DBS002B_QBSTWFT | sum_workfiles_requested | Number of workfiles that have been requested |
| DBS002B_QBSTWFD | sum_workfiles_denied | Number of workfiles that have been denied |
| DBS002B_QBSTWFF | sort_merge_failed | Number of times sort/merge could not be efficiently performed due to the buffer shortage |
| DBS002B_QBSTLPF | list_prefetch_requested | Number of list prefetch requested |
| DBS002B_QBSTDPF | dynamic_prefetch_requested | Number of dynamic prefetch requested |
| DBS002B_QBSTVPA | successful_vpool_exp_contration | Number of successful vpool exp/contration |
| DBS002B_QBSTPCO | pg_unlock_castout | Number of pg on 'unlock castout' |
| DBS002B_QBSTCIO | io_castout | Number of I/O on castout |
| DBS002B_QBSTVPL | buffers_allocated_virtual_pool | Number of buffers allocated for virtual pool |
| DBS002B_QBSTDPP | page_read_dynamic_prefetch | Number of page read due to dynamic prefetch |
| DBS002B_QBSTLPP | page_read_list_prefetch | Number of page read due to list prefetch |
| DBS002B_QBSTDIO | dynamic_prefetch | Number of dynamic prefetch (asynchronous) |
| DBS002B_QBSTLIO | list_prefetch_read_io | Number of list prefetch (asynchronous) read I/O |
| DBS002B_QBSTSGT | get_page_issued_sequential_request | Number of get page issued by sequential request |
| DBS002B_QBSTSIO | sync_read_io_operations_performed | Number of synchronous read I/O operations performed |
| DBS002B_QBSTNGT | unsuccessful_getpage | Number of unsuccessful getpage due to conditional get page request |
| DBS002B_QBSTXIS | highest_prefetch_io_streams | Highest prefetch I/O streams have ever been allocated |
| DBS002B_QBSTJIS | prefetch_io_streams_denied | Number of prefetch I/O streams have been denied due to the shortage of bufferpool |
| DBS002B_QBSTPQO | total_negotiations_between_bm_and_rds | Number of negotiations between BM and RDS |
| DBS002B_QBSTPQF | parallelism | Number of times parallelism has been downgraded due to the shortage of bufferpool |
| DBS002B_QBSTPL1 | total_occurences_prefetch_quantity_1 | Number of occurrences when prefetch quantity decreased from normal to half normal |
| DBS002B_QBSTPL2 | total_occurences_prefetch_quantity_2 | Number of occurrences when prefetch quantity decreased from half normal to one quarter of normal |
| DBS002B_QBSTNSG | confitional_sequential_getpage | Number of conditional sequential getpage |
| DBS002B_QBSTLPL | pages | Number of times one or more pages were |
| DBS002B_QBSTSMIN | min_buffers_slru | Minimum number of buffers on SLRU, low water mark |
| DBS002B_QBSTSMAX | max_buffers_slru | Maximum number of buffers on SLRU, high water mark |
| DBS002B_QBSTHST | length_slru_eq_vpseqt | Number of times length of sequential LRU chain reached sequential steal threshold (VPSEQT) |
| DBS002B_QBSTRHS | random_getpage_buffer_hit | Number of times random getpage buffer hits |