DB2 Version 9.7 for Linux, UNIX, and Windows

ENV_GET_SYS_RESOURCES table function - return system information

The ENV_GET_SYS_RESOURCES table function returns operating system, CPU, memory, and other information related to the system.

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

Syntax


>>-ENV_GET_SYS_RESOURCES--(--)---------------------------------><

The schema is SYSPROC.

Authorization

EXECUTE privilege on the ENV_GET_SYS_RESOURCES table function.

Examples

The following example describes a sample query that uses the ENV_GET_SYS_RESOURCES table function, and the resulting output.

SELECT SUBSTR(NAME,1,20) AS NAME, SUBSTR(VALUE,1,10) AS VALUE,
SUBSTR(DATATYPE,1,10) AS DATATYPE, DBPARTITIONNUM
FROM TABLE(SYSPROC.ENV_GET_SYS_RESOURCES()) AS T

                NAME      VALUE   DATATYPE DBPARTITIONNUM
-------------------- ---------- ---------- --------------
             OS_NAME      Linux VARCHAR(25              0
           HOST_NAME      host1 VARCHAR(25              0
          OS_VERSION          2 VARCHAR(25              0
          OS_RELEASE          6 VARCHAR(25              0
MACHINE_IDENTIFICATI     x86_64 VARCHAR(25              0
            OS_LEVEL          5 VARCHAR(25              0
           CPU_TOTAL          4     BIGINT              0
          CPU_ONLINE          4     BIGINT              0
      CPU_CONFIGURED          4     BIGINT              0
           CPU_SPEED       2599     BIGINT              0
      CPU_HMT_DEGREE          1     BIGINT              0
CPU_CORES_PER_SOCKET          1     BIGINT              0
        MEMORY_TOTAL       7778     BIGINT              0
         MEMORY_FREE       2474     BIGINT              0
   MEMORY_SWAP_TOTAL       2056     BIGINT              0
    MEMORY_SWAP_FREE       1964     BIGINT              0
   VIRTUAL_MEM_TOTAL       9834     BIGINT              0
    VIRTUAL_MEM_FREE       4438     BIGINT              0
      CPU_LOAD_SHORT   0.020000    DECIMAL              0
     CPU_LOAD_MEDIUM   0.030000    DECIMAL              0
       CPU_LOAD_LONG   0.040000    DECIMAL              0
     CPU_USAGE_TOTAL          0   SMALLINT              0

  22 record(s) selected.

ENV_SYS_RESOURCES administrative view metadata

Table 1. ENV_SYS_RESOURCES table function 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 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