DB2 Version 9.7 for Linux, UNIX, and Windows

ENV_GET_SYSTEM_RESOURCES table function - Return system information

The ENV_GET_SYSTEM_RESOURCES table function returns operating system, CPU, memory, and other information that is related to members on the system. The active database can reside on one or more members on the system. This table function returns data only from members where the database that issued the command is active.

The schema is SYSPROC.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the ENV_GET_SYSTEM_RESOURCES table function
DATAACCESS authority

Example

select MEMBER, varchar(HOST_NAME,12) as HOST_NAME, CPU_TOTAL,
MEMORY_TOTAL, CPU_LOAD_SHORT from
table(SYSPROC.ENV_GET_SYSTEM_RESOURCES()) order by MEMBER

Sample output is as follows:

MEMBER HOST_NAME          CPU_TOTAL   MEMORY_TOTAL          CPU_LOAD_SHORT
-----  --------------     ---------   ------------ -----------------------
     0 coralpib23                24          81920  +1.23696899414062E+000
     1 coralpib23                24          81920  +1.23696899414062E+000
     2 coralpib23                24          81920  +1.23696899414062E+000
     3 coralpib23                24          81920  +1.23696899414062E+000

 4 record(s) selected.

Usage Notes

In a virtualized environment (such as VMWare ESX) DB2® may be unable to determine the CPU topology such as CPU_HMT_DEGREE or CPU_CORES_PER_SOCKET. This occurs because of limited information that the hypervisor makes available to the virtualized operating system. In such cases, CPU_CORES_PER_SOCKET and CPU_HMT_DEGREE will have a value of 1.

Information returned

Information is returned for all supported operating systems, except where noted.

Table 1. Information returned for ENV_GET_SYSTEM_RESOURCES
Column name	Data type	Description
MEMBER	SMALLINT	member - Database member monitor element
OS_NAME	VARCHAR(256)	os_name - Operating system name monitor element
HOST_NAME	VARCHAR(255)	host_name - Host name monitor element
OS_VERSION	VARCHAR(256)	os_version - Operating system version monitor element
OS_RELEASE	VARCHAR(256)	os_release - Operating system release monitor element
MACHINE_IDENTIFICATION	VARCHAR(256)	machine_identification - Host hardware identification monitor element
OS_LEVEL	VARCHAR(256)	os_level - Operating system level monitor element
CPU_TOTAL	BIGINT	cpu_total - Number of CPUs monitor element
CPU_ONLINE	BIGINT	cpu_online - Number of CPUs online monitor element
CPU_CONFIGURED	BIGINT	cpu_configured - Number of configured CPUs monitor element
CPU_SPEED	BIGINT	cpu_speed - CPU clock speed monitor element
CPU_TIMEBASE	BIGINT	cpu_timebase - Frequency of timebase register increment monitor element
CPU_HMT_DEGREE	BIGINT	cpu_hmt_degree - Number of logical CPUs monitor element
CPU_CORES_PER_SOCKET	BIGINT	cpu_cores_per_socket - Number of CPU cores per socket monitor element
MEMORY_TOTAL	BIGINT	memory_total - Total physical memory monitor element
MEMORY_FREE	BIGINT	memory_free - Amount of free physical memory monitor element
MEMORY_SWAP_TOTAL	BIGINT	memory_swap_total - Total swap space monitor element
MEMORY_SWAP_FREE	BIGINT	memory_swap_free - Total free swap space monitor element
VIRTUAL_MEM_TOTAL	BIGINT	virtual_mem_total - Total virtual memory monitor element
VIRTUAL_MEM_RESERVED	BIGINT	virtual_mem_reserved - Reserved virtual memory monitor element
VIRTUAL_MEM_FREE	BIGINT	virtual_mem_free - Free virtual memory monitor element
CPU_LOAD_SHORT	DOUBLE	cpu_load_short - Processor load (short timeframe) monitor element
CPU_LOAD_MEDIUM	DOUBLE	cpu_load_medium - Processor load (medium timeframe) monitor element
CPU_LOAD_LONG	DOUBLE	cpu_load_long - Processor load (long timeframe) monitor element
CPU_USAGE_TOTAL	SMALLINT	cpu_usage_total - Processor usage monitor element
CPU_USER¹	BIGINT	cpu_user - Non-kernel processing time monitor element
CPU_IDLE¹	BIGINT	cpu_idle - Processor idle time monitor element
CPU_IOWAIT¹	BIGINT	cpu_iowait - IO Wait time monitor element
CPU_SYSTEM¹	BIGINT	cpu_system - Kernel time monitor element
SWAP_PAGE_SIZE	BIGINT	swap_page_size - Swap page size monitor element
SWAP_PAGES_IN	BIGINT	swap_pages_in - Pages swapped in from disk monitor element
SWAP_PAGES_OUT	BIGINT	swap_pages_out - Pages swapped out to disk monitor element
Note: 1 These metrics have been aggregated across all logical processors on the system. On the AIX® operating system, the metrics are for the Workload partition (WPAR) and Logical partition (LPAR) on which the DB2 server is running.