The SNAPDB_MEMORY_POOL administrative view and the SNAP_GET_DB_MEMORY_POOL table function return information about memory usage at the database level for UNIX platforms only.
The SNAPDB_MEMORY_POOL administrative view and the SNAP_GET_DB_MEMORY_POOL table function return information about memory usage at the database level for UNIX platforms only.
This administrative view allows you to retrieve database level memory usage information for the currently connected database.
Used with the SNAPDB, SNAPDETAILLOG, SNAPHADR, ADMIN_GET_STORAGE_PATHS and MON_GET_HADR, the SNAPDB_MEMORY_POOL administrative view provides information equivalent to the GET SNAPSHOT FOR DATABASE ON database-alias CLP command.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.
SELECT POOL_ID, POOL_CUR_SIZE FROM SYSIBMADM.SNAPDB_MEMORY_POOL
POOL_ID POOL_CUR_SIZE
------------- --------------------
UTILITY 32768
PACKAGE_CACHE 475136
CAT_CACHE 65536
BP 2097152
BP 1081344
BP 540672
BP 278528
BP 147456
BP 81920
LOCK_MGR 294912
DATABASE 3833856
OTHER 0
12 record(s) selected.
The SNAP_GET_DB_MEMORY_POOL table function returns the same information as the SNAPDB_MEMORY_POOL administrative view, but allows you to retrieve the information for a specific database on a specific database member, aggregate of all database members or all database members.
Used with the SNAP_GET_DB, SNAP_GET_DETAILLOG, SNAP_GET_HADR and ADMIN_GET_STORAGE_PATHS table functions, the SNAP_GET_DB_MEMORY_POOL table function provides information equivalent to the GET SNAPSHOT FOR ALL DATABASES CLP command.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_DB_MEMORY_POOL--(--dbname--+----------+--)--------->< '-, member-'
The schema is SYSPROC.
If both dbname and member 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_DB_MEMORY_POOL table function takes a snapshot for the currently connected database and database member number.
In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, POOL_ID, POOL_CUR_SIZE
FROM TABLE(SNAPSHOT_GET_DB_MEMORY_POOL
(CAST(NULL AS VARCHAR(128)), -1)) AS T
DB_NAME POOL_ID POOL_CUR_SIZE
-------- -------------- --------------------
TESTDB UTILITY 65536
TESTDB PACKAGE_CACHE 851968
TESTDB CAT_CACHE 65536
TESTDB BP 35913728
TESTDB BP 589824
TESTDB BP 327680
TESTDB BP 196608
TESTDB BP 131072
TESTDB SHARED_SORT 65536
TESTDB LOCK_MGR 10092544
TESTDB DATABASE 4980736
TESTDB OTHER 196608
SAMPLE UTILITY 65536
SAMPLE PACKAGE_CACHE 655360
SAMPLE CAT_CACHE 131072
SAMPLE BP 4325376
SAMPLE BP 589824
SAMPLE BP 327680
SAMPLE BP 196608
SAMPLE BP 131072
SAMPLE SHARED_SORT 0
SAMPLE LOCK_MGR 655360
SAMPLE DATABASE 4653056
SAMPLE OTHER 196608
24 record(s) selected.
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | The date and time that the snapshot was taken. |
DB_NAME | VARCHAR(128) | db_name - Database name |
POOL_ID | VARCHAR(14) | pool_id - Memory pool identifier . This interface
returns a text identifier based on defines in sqlmon.h,
and is one of:
|
POOL_SECONDARY_ID | VARCHAR(32) | pool_secondary_id - Memory pool secondary identifier |
POOL_CUR_SIZE | BIGINT | pool_cur_size - Current size of memory pool |
POOL_WATERMARK | BIGINT | pool_watermark - Memory pool watermark |
POOL_CONFIG_SIZE | BIGINT | pool_config_size - Configured size of memory pool |
DBPARTITIONNUM | SMALLINT | dbpartitionnum - Database partition number monitor element |
MEMBER | SMALLINT | member - Database member monitor element |