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

Note: The ENV_GET_SYSTEM_RESOURCES table function replaces the ENV_GET_SYS_RESOURCES table function and the associated ENV_SYS_RESOURCES administrative view.

The ENV_GET_SYSTEM_RESOURCES table function returns all data in one row with multiple columns for each member, instead of multiple rows for each member. Additionally, data that the ENV_GET_SYS_RESOURCES table function returns for the DBPARTITIONNUM column is returned in the MEMBER column by the ENV_GET_SYSTEM_RESOURCES table function.

The schema is SYSPROC.

Authorization

One of the following authorizations is required:

EXECUTE privilege on the table function
DATAACCESS authority
DBADM authority
SQLADM authority

Default PUBLIC privilege

None

Information returned

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
OS_FULL_VERSION	VARCHAR(256)	os_full_version - Operating system full version monitor element
OS_KERNEL_VERSION	VARCHAR(256)	os_kernel_version - Operating system kernel identifier monitor element
OS_ARCH_TYPE	VARCHAR(256)	os_arch_type - Operating system architecture type 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.

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.
To return accurate results, the ENV_GET_SYSTEM_RESOURCES table function takes more than 1 second on AIX, HP, and Windows operating systems.

Examples

Example 1: Obtain operating system information for every member in a three-member DB2 pureScale® setup:

select MEMBER, varchar(HOST_NAME,12) as HOST_NAME, varchar(OS_NAME,8) as OS_NAME, 
	varchar(OS_VERSION,8) as OS_VERSION, varchar(OS_RELEASE,8) 
	as OS_RELEASE from table(SYSPROC.ENV_GET_SYSTEM_RESOURCES()) order by MEMBER

Sample output is as follows:

MEMBER HOST_NAME    OS_NAME  OS_VERSION OS_RELEASE
------ ------------ -------- ---------- ----------
     0 hotel75      Linux    2          6         
     1 hotel78      Linux    2          6         
     2 hotel90      Linux    2          6         

  3 record(s) selected.

Example 2: Obtain memory information for the system hosting this database on a DB2 Enterprise Server Edition system:

select varchar(HOST_NAME,12) as HOST_NAME, MEMORY_TOTAL, MEMORY_FREE, 
   MEMORY_SWAP_TOTAL, MEMORY_SWAP_FREE, VIRTUAL_MEM_TOTAL, VIRTUAL_MEM_FREE 
   from table(SYSPROC.ENV_GET_SYSTEM_RESOURCES())

Sample output is as follows. Because of space constraints, the output is continued on a second line.

HOST_NAME    MEMORY_TOTAL         MEMORY_FREE          MEMORY_SWAP_TOTAL    ...
------------ -------------------- -------------------- -------------------- ... 
hotel75                     32189                 4370                 8198 ...

1 record(s) selected

... MEMORY_SWAP_FREE     VIRTUAL_MEM_TOTAL    VIRTUAL_MEM_FREE      
... -------------------- -------------------- --------------------  
...                 7316                40387                11686

Example 3: Find the load times for all the systems hosting the database, in a four member partitioned database environment.

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

Sample output is as follows:

MEMBER HOST_NAME CPU_LOAD_SHORT          CPU_LOAD_MEDIUM         CPU_LOAD_LONG           
------ --------- ----------------------- ----------------------  ------------------------
     0 hotel75   +5.21000000000000E+000  +5.08000000000000E+000  +4.67000000000000E+000
     1 hotel78   +1.33000000000000E+000  +2.18000000000000E+000  +3.66000000000000E+000
     2 hotel90   +9.02000000000000E+000  +9.08000000000000E+000  +9.47000000000000E+000
     3 hotel32   +1.09000000000000E+000  +1.38000000000000E+000  +1.27000000000000E+000

  4 record(s) selected.

Example 4:

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.