MON_PKG_CACHE_SUMMARY - Retrieve a high-level summary of the database package cache
The MON_PKG_CACHE_SUMMARY administrative view returns key metrics for both static and dynamic SQL statements in the cache, providing a high-level summary of the database package cache.
The metrics returned are aggregated over all executions of the statement across all members of the database.
The schema is SYSIBMADM.
Authorization
One of the following authorizations
is required:
- SELECT privilege on the MON_PKG_CACHE_SUMMARY administrative view
- CONTROL privilege on the MON_PKG_CACHE_SUMMARY administrative view
- DATAACCESS authority
Default PUBLIC privilege
None
Information returned
| Column name | Data type | Description or Monitor element |
|---|---|---|
| SECTION_TYPE | CHAR(1) | section_type - Section type indicator |
| EXECUTABLE_ID | VARCHAR(32) FOR BIT DATA | executable_id - Executable ID |
| NUM_COORD_EXEC | BIGINT | num_coord_exec - Number of executions by coordinator agent |
| NUM_COORD_EXEC_WITH_METRICS | BIGINT | num_coord_exec_with_metrics - Number of executions by coordinator agent with metrics |
| TOTAL_STMT_EXEC_TIME | BIGINT | The total amount of time, in milliseconds, spent executing the statement, including nested activities, over all executions of the statement where the metrics have been collected. |
| AVG_STMT_EXEC_TIME | BIGINT | The average amount of time, in milliseconds, spent executing the statement, including nested activities, over all executions of the statement where the metrics have been collected. |
| TOTAL_CPU_TIME | BIGINT | The total amount of CPU time, in microseconds, used while within the database manager. This value represents the combined total of both user and system CPU time. It is calculated as the sum of all total_cpu_time - Total CPU time values for the statement. |
| AVG_CPU_TIME | BIGINT | The average amount of CPU time, in microseconds, spent within the database manager over all executions of the statement where the metrics have been collected. |
| TOTAL_LOCK_WAIT_TIME | BIGINT | The total elapsed time, in milliseconds, spent waiting for locks. This value is calculated as the sum of all lock_wait_time - Time waited on locks values for the statement. |
| AVG_LOCK_WAIT_TIME | BIGINT | The average elapsed time, in milliseconds, spent waiting for locks over all executions of the statement where the metrics have been collected. |
| TOTAL_IO_WAIT_TIME | BIGINT | The total elapsed time, in milliseconds, spent on I/O operations. This value is calculated as the sum of the elapsed time required to perform direct reads or direct writes, plus the elapsed time spent physically reading or writing data and index pages from or to the table space containers. |
| AVG_IO_WAIT_TIME | BIGINT | The average elapsed time, in milliseconds, spent on I/O operations over all executions of the statement where the metrics have been collected. |
| PREP_TIME | BIGINT | prep_time - Preparation time |
| ROWS_READ_PER_ROWS_RETURNED | BIGINT | The average number of rows read per rows returned over all executions of the statement where the metrics have been collected. |
| AVG_ACT_WAIT_TIME | BIGINT | Average time spent waiting for database activities per statement execution. |
| AVG_LOCK_ESCALS | BIGINT | Average number of lock escalations per statement execution. |
| AVG_RECLAIM_WAIT_TIME | BIGINT | Average time spent waiting for page reclaims per statement execution. Outside of a Db2® pureScale® environment, this value will always be null. |
| AVG_SPACEMAPPAGE_RECLAIM_WAIT_TIME | BIGINT | Average time spent waiting for reclaims of spacemap pages per statement execution. Outside of a Db2 pureScale environment, this value will always be null. |
| STMT_TEXT | CLOB(2MB) | stmt_text - SQL statement text |