The SNAPAGENT_MEMORY_POOL administrative view and the SNAP_GET_AGENT_MEMORY_POOL table function return information about memory usage at the agent level.
This administrative view allows you to retrieve the memory_pool logical data group snapshot information about memory usage at the agent level for the currently connected database.
Used with the SNAPAGENT, SNAPAPPL, SNAPAPPL_INFO, SNAPSTMT and SNAPSUBSECTION administrative views, the SNAPAGENT_MEMORY_POOL administrative view provides information equivalent to the GET SNAPSHOT FOR APPLICATIONS ON database-alias CLP command.
The schema is SYSIBMADM.
Refer to Table 1 for a complete list of information that can be returned.
SELECT AGENT_ID, POOL_ID, POOL_CUR_SIZE FROM SYSIBMADM.SNAPAGENT_MEMORY_POOL
AGENT_ID POOL_ID POOL_ CUR_SIZE
--------...--- -------------- ---------...------
48 APPLICATION 65536
48 OTHER 65536
48 APPL_CONTROL 65536
47 APPLICATION 65536
47 OTHER 131072
47 APPL_CONTROL 65536
46 OTHER 327680
46 APPLICATION 262144
46 APPL_CONTROL 65536
9 record(s) selected.
The SNAP_GET_AGENT_MEMORY_POOL table function returns the same information as the SNAPAGENT_MEMORY_POOL 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_AGENT, SNAP_GET_APPL_V95, SNAP_GET_APPL_INFO_V95, SNAP_GET_STMT and SNAP_GET_SUBSECTION table functions, the SNAP_GET_AGENT_MEMORY_POOL table function provides information equivalent to the GET SNAPSHOT FOR ALL APPLICATIONS CLP command.
Refer to Table 1 for a complete list of information that can be returned.
>>-SNAP_GET_AGENT_MEMORY_POOL--(--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_AGENT_MEMORY_POOL table function takes a snapshot for the currently connected database and database partition number.
SELECT SUBSTR(DB_NAME,1,8) AS DB_NAME, AGENT_ID, POOL_ID, POOL_CUR_SIZE
FROM TABLE(SNAP_GET_AGENT_MEMORY_POOL(CAST (NULL AS VARCHAR(128)), -1))
AS T
DB_NAME AGENT_ID POOL_ID POOL_CUR_SIZE
-------- --------...--- -------------- --------------------
SAMPLE 48 APPLICATION 65536
SAMPLE 48 OTHER 65536
SAMPLE 48 APPL_CONTROL 65536
SAMPLE 47 APPLICATION 65536
SAMPLE 47 OTHER 131072
SAMPLE 47 APPL_CONTROL 65536
SAMPLE 46 OTHER 327680
SAMPLE 46 APPLICATION 262144
SAMPLE 46 APPL_CONTROL 65536
TESTDB 30 APPLICATION 65536
TESTDB 30 OTHER 65536
TESTDB 30 APPL_CONTROL 65536
TESTDB 29 APPLICATION 65536
TESTDB 29 OTHER 131072
TESTDB 29 APPL_CONTROL 65536
TESTDB 28 OTHER 327680
TESTDB 28 APPLICATION 65536
TESTDB 28 APPL_CONTROL 65536
18 record(s) selected.
Column name | Data type | Description or corresponding monitor element |
---|---|---|
SNAPSHOT_TIMESTAMP | TIMESTAMP | snapshot_timestamp - Snapshot timestamp monitor element |
DB_NAME | VARCHAR(128) | db_name - Database name |
AGENT_ID | BIGINT | agent_id - Application handle (agent ID) |
AGENT_PID | BIGINT | agent_pid - Engine dispatchable unit (EDU) |
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_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 | The database partition from which the data was retrieved for this row. |