The ADMIN_GET_DBP_MEM_USAGE table function gets the total memory consumption for a given instance.
>>-ADMIN_GET_DBP_MEM_USAGE--(--+----------------+--)----------->< '-dbpartitionnum-'
The schema is SYSPROC.
EXECUTE privilege on the ADMIN_GET_DBP_MEM_USAGE function.
Column Name | Data Type | Description |
---|---|---|
DBPARTITIONNUM | SMALLINT | The database partition number from which memory usage statistics is retrieved. |
MAX_PARTITION_MEM | BIGINT | The maximum amount of instance memory (in bytes) allowed to be consumed in the database partition if an instance memory limit is enforced. |
CURRENT_PARTITION_MEM | BIGINT | The amount of instance memory (in bytes) currently consumed in the database partition. |
PEAK_PARTITION_MEM | BIGINT | The peak or high watermark consumption of instance memory (in bytes) in the database partition. |
Example 1: Retrieve memory usage statistics from database partition 3
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE(3)) AS T
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM PEAK_PARTITION_MEM
-------------- ----------------- --------------------- ------------------
3 500000000 381000000 481000000
1 record(s) selected.
Example 2: Retrieve memory usage statistics from the currently connected partition (assuming the user is connected to the database at partition 2.)
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE(-1)) AS T
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM PEAK_PARTITION_MEM
-------------- ----------------- --------------------- ------------------
2 500000000 381000000 481000000
1 record(s) selected.
SELECT * FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T
DBPARTITIONNUM MAX_PARTITION_MEM CURRENT_PARTITION_MEM PEAK_PARTITION_MEM
-------------- ----------------- --------------------- ------------------
0 500000000 381000000 481000000
1 500000000 381000000 481000000
2 500000000 381000000 481000000
3 500000000 381000000 481000000
4 record(s) selected.
Example 4: Retrieve memory usage statistics in megabyte (MB) values
SELECT DBPARTITIONNUM, MAX_PARTITION_MEM/1048576 AS MAX_MEM_MB,
CURRENT_PARTITION_MEM/1048576 AS CURRENT_MEM_MB, PEAK_PARTITION_MEM/1048576
AS PEAK_MEM_MB FROM TABLE (SYSPROC.ADMIN_GET_DBP_MEM_USAGE()) AS T
DBPARTITIONNUM MAX_MEM_MB CURRENT_MEM_MB PEAK_MEM_MB
-------------- ---------- -------------- -----------
0 4590 1107 1107
1 4590 1108 1108
2 4590 1106 1106
3 record(s) selected.