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: The ENV_GET_SYS_RESOURCES
table function has been deprecated and replaced by the ENV_GET_SYSTEM_RESOURCES table function - Return system information.
Syntax
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.
Usage Notes
To return accurate results, the ENV_GET_SYS_RESOURCES table function takes more than 1 second on AIX, HP, and Windows operating systems.
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. |
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 |
Operating system | HOST_NAME | VARCHAR(256) | Host name of the system. | All | NULL |
Operating system | OS_VERSION | VARCHAR(256) | Version of the operating system. For example, AIX®: 4.3 version = 4. | All | NULL |
Operating system | OS_RELEASE | VARCHAR(256) | Release of the operating system. For example, AIX: 4.3 release = 3. | All | NULL |
Operating system | MACHINE_IDENTIFICATION | VARCHAR(256) | Machine hardware identification. | All | NULL |
Operating system | 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 | CPU_ONLINE | BIGINT | Number of CPUs online. | All | NULL |
CPU | CPU_CONFIGURED | BIGINT | Number of CPUs configured. | All | NULL |
CPU | CPU_SPEED | BIGINT | Speed of CPUs. | All | MHz |
CPU | CPU_TIMEBASE | BIGINT | Frequency of timebase register increment. | Linux PowerPC® | Hz |
CPU | 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 | 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 |
Physical memory | MEMORY_FREE | BIGINT | Amount of free physical memory. | All | MB |
Physical memory | MEMORY_SWAP_TOTAL | BIGINT | Total amount of swap space. | All | MB |
Physical memory | 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 memory | VIRTUAL_MEM_RESERVED | BIGINT | Amount of reserved virtual memory. | All | MB |
Virtual memory | 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 | CPU_LOAD_MEDIUM | DECIMAL | Medium period duration. For example, load samples over last 10 minutes. | All except Windows operating systems | NULL |
CPU load | CPU_LOAD_LONG | DECIMAL | Long period duration. For example, load samples over last 15 minutes. | All except Windows operating systems | NULL |
CPU load | CPU_USAGE_TOTAL | SMALLINT | Percentage of overall CPU usage of the machine. | All | Percent |