SNAPDBM_MEMORY_POOL administrative view and SNAP_GET_DBM_MEMORY_POOL table function - Retrieve database manager level memory usage information

The SNAPDBM_MEMORY_POOL administrative view and the SNAP_GET_DBM_MEMORY_POOL table function return information about memory usage at the database manager.

Important: Starting in version 9.7 Fix Pack 5, the SNAPDBM_MEMORY_POOL administrative view and SNAP_GET_DBM_MEMORY_POOL table function have been deprecated and replaced by the MON_GET_MEMORY_POOL table function - get memory pool information and MON_GET_MEMORY_SET table function - get memory set information.

Depending on if you are using the administrative view or the table function, refer to one of the following sections:

SNAPDBM_MEMORY_POOL administrative view
SNAP_GET_DBM_MEMORY_POOL table function

SNAPDBM_MEMORY_POOL administrative view

Used with the SNAPSWITCHES administrative view and the MON_GET_FCM, MON_GET_INSTANCE, MON_GET_MEMORY_SET, and MON_GET_MEMORY_POOL table functions, the SNAPDBM_MEMORY_POOL administrative view provides the data equivalent to the GET SNAPSHOT FOR DBM command.

The schema is SYSIBMADM.

Refer to Table 1 for a complete list of information that can be returned.

Authorization

One of the following authorizations is required:

SELECT privilege on the SNAPDBM_MEMORY_POOL administrative view
CONTROL privilege on the SNAPDBM_MEMORY_POOL administrative view
DATAACCESS authority

One of the following is required to use the table function:

EXECUTE privilege on the SNAP_GET_DBM_MEMORY_POOL table function
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Default PUBLIC privilege

In a non-restrictive database, SELECT privilege is granted to PUBLIC when the view is automatically created.

Example

Retrieve a list of the memory pools and their current size for the database manager of the connected database.

SELECT POOL_ID, POOL_CUR_SIZE FROM SNAPDBM_MEMORY_POOL

The following is an example of output from this query.

POOL_ID        POOL_CUR_SIZE 
-------------- --------------------
MONITOR                      65536
OTHER                     29622272
FCMBP                     57606144
...

SNAP_GET_DBM_MEMORY_POOL table function

The SNAP_GET_DBM_MEMORY_POOL table function returns the same information as the SNAPDBM_MEMORY_POOL administrative view, but allows you to retrieve the information for a specific database member, aggregate of all database members or all database members.

Used with the SNAP_GET_DBM, SNAP_GET_FCM, SNAP_GET_FCM_PART and SNAP_GET_SWITCHES table functions, the SNAP_GET_DBM_MEMORY_POOL table function provides the data equivalent to the GET SNAPSHOT FOR DBM command.

Refer to Table 1 for a complete list of information that can be returned.

Syntax

The schema is SYSPROC.

Table function parameter

member

An optional input argument of type INTEGER that specifies a valid database member number. Specify -1 for the current database member, or -2 for an aggregate of all active database members. If this input option is not used, data will be returned from all active database members. An active database member is a member where the database is available for connection and use by applications.

If member is 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_DBM_MEMORY_POOL table function takes a snapshot for the currently connected database and database member number.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the SNAP_GET_DBM_MEMORY_POOL table function
DATAACCESS authority

In addition, to access snapshot monitor data, one of the following authorities is also required:

SYSMON
SYSCTRL
SYSMAINT
SYSADM

Default PUBLIC privilege

In a non-restrictive database, EXECUTE privilege is granted to PUBLIC when the function is automatically created.

Example

Retrieve a list of the memory pools and their current size for all database partitions of the database manager of the connected database.

SELECT POOL_ID, POOL_CUR_SIZE, DBPARTITIONNUM 
   FROM TABLE(SYSPROC.SNAP_GET_DBM_MEMORY_POOL()) 
   AS T ORDER BY DBPARTITIONNUM

The following is an example of output from this query.

POOL_ID        POOL_CUR_SIZE        DBPARTITIONNUM
-------------- -------------------- --------------
MONITOR                       65536              0
OTHER                      29622272              0
FCMBP                      57606144              0
MONITOR                       65536              1
OTHER                      29425664              1
FCMBP                      57606144              1
MONITOR                       65536              2
OTHER                      29425664              2
FCMBP                      57606144              2

Information returned

Table 1. Information returned by the SNAPDBM_MEMORY_POOL administrative view and the SNAP_GET_DBM_MEMORY_POOL table function
Column name	Data type	Description or corresponding monitor element
SNAPSHOT_TIMESTAMP	TIMESTAMP	The date and time that the snapshot was taken.
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: `APP_GROUP` `APPL_CONTROL` `APPLICATION` `BP` `CAT_CACHE` `DATABASE` `DFM` `FCMBP` `IMPORT_POOL` `LOCK_MGR` `MONITOR` `OTHER` `PACKAGE_CACHE` `QUERY` `SHARED_SORT` `SORT` `STATEMENT` `STATISTICS` `UTILITY`
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