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.

Note: This table function has been deprecated and replaced by the ADMIN_GET_MEM_USAGE table function - Get total memory consumption for instance.

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

Syntax

The schema is SYSPROC.

Table function parameters

member

An optional input argument of type integer that specifies the database member 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 member.

Authorization

One of the following authorities is required to execute the routine:

EXECUTE privilege on the routine
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

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

Information returned

Table 1. The result set for ADMIN_GET_DBP_MEM_USAGE
Column Name	Data Type	Description
DBPARTITIONNUM	SMALLINT	dbpartitionnum - Database partition number monitor element
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 member (assuming the user is connected to the database at member 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 members

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.