DB2 Version 9.7 for Linux, UNIX, and Windows

ADMIN_GET_DBP_MEM_USAGE table function - Get total memory consumption for instance

The ADMIN_GET_DBP_MEM_USAGE table function gets the total memory consumption for a given instance.

The ADMIN_GET_DBP_MEM_USAGE table function takes an optional input argument dbpartitionnum (INTEGER type), which specifies a valid database partition number, and returns only statistics for that single database partition. If the argument is omitted, statistics are returned for all active database partitions. When in a partitioned database environment, if you specify -1 or a NULL value for dbpartitionnum, data is returned from the currently connected partition.

Syntax


>>-ADMIN_GET_DBP_MEM_USAGE--(--+----------------+--)-----------><
                               '-dbpartitionnum-'

The schema is SYSPROC.

Table function parameters

dbpartitionnum: An optional input argument of type integer that specifies the database partition from which the memory usage statistics will be retrieved. If -1 or the NULL value is specified, data will be returned from the currently connected partition.

Authorization

EXECUTE privilege on the ADMIN_GET_DBP_MEM_USAGE function.

Information returned

Table 1. The result set for ADMIN_GET_DBP_MEM_USAGE
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.

Examples

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.

Example 3: Retrieve memory usage statistics from all partitions

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.