The SNAPBP administrative view and the SNAP_GET_BP_V95 table function return information about buffer pools from a bufferpool snapshot, in particular, the bufferpool logical data group.
This administrative view allows you to retrieve bufferpool logical group snapshot information for the currently connected database.
Used with the SNAPBP_PART administrative view, the SNAPBP 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,POOL_DATA_WRITES,POOL_INDEX_WRITES
FROM SYSIBMADM.SNAPBP
DB_NAME BP_NAME POOL_DATA_WRITES POOL_INDEX_WRITES
-------- --------------- -------------------- --------------------
TEST IBMDEFAULTBP 0 0
TEST IBMSYSTEMBP4K 0 0
TEST IBMSYSTEMBP8K 0 0
TEST IBMSYSTEMBP16K 0 0
TEST IBMSYSTEMBP32K 0 0
5 record(s) selected
The SNAP_GET_BP_V95 table function returns the same information as the SNAPBP 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_PART table function, the SNAP_GET_BP_V95 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_V95--(--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_V95 table function takes a snapshot for the currently connected database and database partition number.
SELECT SUBSTR(T.DB_NAME,1,10) AS DB_NAME,
SUBSTR(T.BP_NAME,1,20) AS BP_NAME,
(T.POOL_DATA_L_READS+T.POOL_INDEX_L_READS) AS TOTAL_LOGICAL_READS,
(T.POOL_DATA_P_READS+T.POOL_INDEX_P_READS) AS TOTAL_PHYSICAL_READS,
T.DBPARTITIONNUM
FROM TABLE(SNAP_GET_BP_V95(CAST(NULL AS VARCHAR(128)), -1)) AS T
DB_NAME BP_NAME TOTAL_LOGICAL_READS ...
---------- ------------...- -------------------- ...
SAMPLE IBMDEFAULTBP 0 ...
TOOLSDB IBMDEFAULTBP 0 ...
TOOLSDB BP32K0000 0 ...
3 record(s) selected.
... TOTAL_PHYSICAL_READS DBPARTITIONNUM
... -------------------- --------------
... 0 0
... 0 0
... 0 0