The SNAPBP_PART administrative view and the SNAP_GET_BP_PART table function return information about buffer pools from a bufferpool snapshot, in particular, the bufferpool_nodeinfo logical data group.
This administrative view allows you to retrieve bufferpool_nodeinfo logical data group snapshot information for the currently connected database.
Used with the SNAPBP administrative view, the SNAPBP_PART administrative view provides the data equivalent to the GET SNAPSHOT FOR BUFFERPOOLS ON database-alias CLP command.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,15) AS BP_NAME,
BP_CUR_BUFFSZ, BP_NEW_BUFFSZ, BP_PAGES_LEFT_TO_REMOVE, BP_TBSP_USE_COUNT
FROM SYSIBMADM.SNAPBP_PART
DB_NAME BP_NAME BP_CUR_BUFFSZ BP_NEW_BUFFSZ ...
-------- --------------- -------------------- -------------------- ...
SAMPLE IBMDEFAULTBP 1000 1000 ...
SAMPLE IBMSYSTEMBP4K 16 16 ...
SAMPLE IBMSYSTEMBP8K 16 16 ...
SAMPLE IBMSYSTEMBP16K 16 16 ...
...
4 record(s) selected.
... BP_PAGES_LEFT_TO_REMOVE BP_TBSP_USE_COUNT
... ----------------------- --------------------
... 0 3
... 0 0
... 0 0
... 0 0
...
The SNAP_GET_BP_PART table function returns the same information as the SNAPBP_PART administrative view, but allows you to retrieve the information for a specific database on a specific database partition, aggregate of all database partitions or all database partitions.
Used with the SNAP_GET_BP_V95 table function, the SNAP_GET_BP_PART table function provides the data equivalent to the GET SNAPSHOT FOR ALL BUFFERPOOLS CLP command.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_BP_PART--(--dbname--+------------------+--)-------->< '-, dbpartitionnum-'
The schema is SYSPROC.
If both dbname and dbpartitionnum are set to NULL, an attempt is made to read data from the file created by SNAP_WRITE_FILE procedure. Note that this file could have been created at any time, which means that the data might not be current. If a file with the corresponding snapshot API request type does not exist, then the SNAP_GET_BP_PART table function takes a snapshot for the currently connected database and database partition number.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, SUBSTR(BP_NAME,1,15) AS BP_NAME,
BP_CUR_BUFFSZ, BP_NEW_BUFFSZ, BP_PAGES_LEFT_TO_REMOVE, BP_TBSP_USE_COUNT
FROM TABLE(SNAP_GET_BP_PART(CAST(NULL AS VARCHAR(128)),-1)) AS T
DB_NAME BP_NAME BP_CUR_BUFFSZ BP_NEW_BUFFSZ ...
-------- --------------- -------------------- -------------------- ...
SAMPLE IBMDEFAULTBP 250 250 ...
SAMPLE IBMSYSTEMBP4K 16 16 ...
SAMPLE IBMSYSTEMBP8K 16 16 ...
SAMPLE IBMSYSTEMBP16K 16 16 ...
SAMPLE IBMSYSTEMBP32K 16 16 ...
TESTDB IBMDEFAULTBP 250 250 ...
TESTDB IBMSYSTEMBP4K 16 16 ...
TESTDB IBMSYSTEMBP8K 16 16 ...
TESTDB IBMSYSTEMBP16K 16 16 ...
TESTDB IBMSYSTEMBP32K 16 16 ...
...
... BP_PAGES_LEFT_TO_REMOVE BP_TBSP_USE_COUNT
... ----------------------- --------------------
... 0 3
... 0 0
... 0 0
... 0 0
... 0 0
... 0 3
... 0 0
... 0 0
... 0 0
... 0 0
...
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | snapshot_timestamp - Snapshot timestamp monitor element |
BP_NAME | VARCHAR(128) | bp_name - Buffer pool name |
DB_NAME | VARCHAR(128) | db_name - Database name |
BP_CUR_BUFFSZ | BIGINT | bp_cur_buffsz - current size of buffer pool |
BP_NEW_BUFFSZ | BIGINT | bp_new_buffsz - New buffer pool size |
BP_PAGES_LEFT_TO_REMOVE | BIGINT | bp_pages_left_to_remove - Number of pages left to remove |
BP_TBSP_USE_COUNT | BIGINT | bp_tbsp_use_count - Number of table spaces mapped to buffer pool |
DBPARTITIONNUM | SMALLINT | The database partition from which the data was retrieved for this row. |