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 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.
None
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.
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.