DB2 10.5 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.

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


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

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.