DB2 Version 9.7 for Linux, UNIX, and Windows

ENV_SYS_RESOURCES administrative view - Return system information

Important: Starting with Version 9.7 Fix Pack 5, the ENV_SYS_RESOURCES administrative view has been deprecated and replaced by the ENV_GET_SYSTEM_RESOURCES table function - Return system information.

The ENV_SYS_RESOURCES administrative view returns operating system, CPU, memory and other information related to the system.

The schema is SYSIBMADM.

Authorization

One of the following authorizations is required:

SELECT privilege on the ENV_SYS_RESOURCES administrative view
CONTROL privilege on the ENV_SYS_RESOURCES administrative view
DATAACCESS authority

Example

  SELECT SUBSTR(NAME,1,20) AS NAME, SUBSTR(VALUE,1,10) AS VALUE, 
    SUBSTR(DATATYPE,1,10) AS DATATYPE, DBPARTITIONNUM 
  FROM SYSIBMADM.ENV_SYS_RESOURCES 
  WHERE SUBSTR(NAME,1,8)='CPU_LOAD' OR NAME='CPU_USAGE_TOTAL'

The following example is a sample output from this query.

NAME                    VALUE      DATATYPE   DBPARTITIONNUM
----------------------- ---------- ---------- --------------
CPU_LOAD_SHORT          0.044052   DECIMAL                 0
CPU_LOAD_MEDIUM         0.087250   DECIMAL                 0
CPU_LOAD_LONG           0.142059   DECIMAL                 0
CPU_USAGE_TOTAL         7          SMALLINT                0

  4 record(s) selected.

ENV_SYS_RESOURCES administrative view metadata

Table 1. ENV_SYS_RESOURCES administrative view metadata
Column name	Data type	Description
NAME	VARCHAR(128)	Name of the attribute. See Table 2 for possible values. Note: Some attributes might not be available depending on the operating system and hardware configuration at the server.
VALUE	VARCHAR(1024)	The value of the attribute.
DATATYPE	VARCHAR(128)	Attribute data type.
UNIT	VARCHAR(128)	Unit used for the VALUE column if applicable. NULL is returned if not applicable.
DBPARTITIONNUM	SMALLINT	The database partition from which the data was retrieved for this row.

Table 2. Possible values for the NAME column
Information type	Name	Data Types	Description	Platforms that return this information	UNIT
Operating system	OS_NAME	VARCHAR(256)	Name of the operating system software.	All	NULL
	HOST_NAME	VARCHAR(256)	Host name of the system.	All	NULL
	OS_VERSION	VARCHAR(256)	Version of the operating system. For example, AIX: 4.3 version = 4.	All	NULL
	OS_RELEASE	VARCHAR(256)	Release of the operating system. For example, AIX: 4.3 release = 3.	All	NULL
	MACHINE_IDENTIFICATION	VARCHAR(256)	Machine hardware identification.	All	NULL
	OS_LEVEL	VARCHAR(256)	Maintenance level of the current version and release. For example, LINUX: 2.4.9, level = 9.	Linux	NULL
CPU	CPU_TOTAL	BIGINT	Total number of CPUs.	All	NULL
	CPU_ONLINE	BIGINT	Number of CPUs online.	All	NULL
	CPU_CONFIGURED	BIGINT	Number of CPUs configured.	All	NULL
	CPU_SPEED	BIGINT	Speed of CPUs.	All	MHz
	CPU_TIMEBASE	BIGINT	Frequency of timebase register increment.	Linux running on PowerPC®	Hz
	CPU_HMT_DEGREE	BIGINT	On systems that support hardware multithreading (HMT), this is the number of processors that a physical processor will appear to the operating system as. On non-HMT systems, this value is 1. On HMT systems, "total" will reflect the number of logical CPUs. To get the number of physical CPUs, divide the "total" by "threadingDegree".	All	NULL
	CPU_CORES_PER_SOCKET	BIGINT	Number of CPU cores per socket. On single core systems this value is 1.	All	NULL
Physical memory	MEMORY_TOTAL	BIGINT	Total size of physical memory.	All	MB
	MEMORY_FREE	BIGINT	Amount of free physical memory.	All	MB
	MEMORY_SWAP_TOTAL	BIGINT	Total amount of swap space.	All	MB
	MEMORY_SWAP_FREE	BIGINT	Amount of free swap space.	All	MB
Virtual memory	VIRTUAL_MEM_TOTAL	BIGINT	Total amount of virtual memory on the system.	All	MB
	VIRTUAL_MEM_RESERVED	BIGINT	Amount of reserved virtual memory.	All	MB
	VIRTUAL_MEM_FREE	BIGINT	Amount of virtual memory free.	All	MB
CPU load	CPU_LOAD_SHORT	DECIMAL	Shortest period duration. For example, load samples over last 5 minutes.	All except Windows operating systems	NULL
	CPU_LOAD_MEDIUM	DECIMAL	Medium period duration. For example, load samples over last 10 minutes.	All except Windows operating systems	NULL
	CPU_LOAD_LONG	DECIMAL	Long period duration. For example, load samples over last 15 minutes.	All except Windows operating systems	NULL
	CPU_USAGE_TOTAL	SMALLINT	Percentage of overall CPU usage of the machine.	All	Percent