When I run SQL queries, especially when retrieving system information, it is useful to include the IBM i partition name. I have been using a User Defined Function, UDF, I created to call a CL program to get this information. But looking at my trusty poster of Db2 for i I found the View that gives me the partition's host name and more interesting information.
This View also gives me the IBM i operating system version and release numbers, so I don't have to use the QSS1MRI data area and the DATA_AREA_ table function to retrieve it.
The View ENV_SYS_INFO is found in the library SYSIBMADM, and it has the following columns:
| Column name | Description |
| OS_NAME | Operating system name |
| OS_VERSION | Operating system version |
| OS_RELEASE | Operating system release |
| HOST_NAME | Name of the partition |
| TOTAL_CPUS | Maximum number of virtual processor defined with LPAR configuration |
| CONFIGURED_CPUS | Number of virtual processors currently available to the partitions |
| CONFIGURED_MEMORY | Total amount of configured memory on the system |
| TOTAL_MEMORY | Total amount of memory on the system |
I could just use the following SQL statement:
SELECT * FROM SYSIBMADM.ENV_SYS_INFO |
But as the first four columns of the view are VARCHAR(256), depending on the SQL tool you are using that could mean a lot of paging right and left to see the information.
What I did was to resize those columns using CAST.
SELECT CAST(OS_NAME AS CHAR(5)) AS "O/S",
CAST(OS_VERSION AS CHAR(2)) AS "Ver",
CAST(OS_RELEASE AS CHAR(2)) AS "Rel",
CAST(HOST_NAME AS CHAR(15)) AS "Host name",
TOTAL_CPUS AS "Tot CPUs",
CONFIGURED_CPUS AS "Config CPUs",
CONFIGURED_MEMORY AS "Tot config mem",
TOTAL_MEMORY AS "Tot mem"
FROM SYSIBMADM.ENV_SYS_INFO
|
There is just a single row of results returned for the current partition, DEV740:
O/S Ver Rel Host name Tot CPUs Config CPUs
----- --- --- -------------- -------- -----------
IBM i 7 4 DEV740.RZKH.DE 1 1
Tot config mem Tot mem
-------------- -------
4096 32768
|
The next question I had was how does this compare to their other partitions I have a signon to. As DEV740 is a new partition I need to add the Remote Database Entries to the other partitions. Once they were in place I could use the three part name to connect to the partition running IBM i 7.3:
SELECT CAST(OS_NAME AS CHAR(5)) AS "O/S",
CAST(OS_VERSION AS CHAR(2)) AS "Ver",
CAST(OS_RELEASE AS CHAR(2)) AS "Rel",
CAST(HOST_NAME AS CHAR(15)) AS "Host name",
TOTAL_CPUS AS "Tot CPUs",
CONFIGURED_CPUS AS "Config CPUs",
CONFIGURED_MEMORY AS "Tot config mem",
TOTAL_MEMORY AS "Tot mem"
FROM DEV730.SYSIBMADM.ENV_SYS_INFO ;
|
The only difference between this statement and the previous one is the FROM part. As I am using the three part name I need to use the remote database name before the name of the library.
The returned results are:
O/S Ver Rel Host name Tot CPUs Config CPUs
----- --- --- -------------- -------- -----------
IBM i 7 3 DEV730.RZKH.DE 1 1
Tot config mem Tot mem
-------------- -------
2048 8192
|
There is one more partition I have access to, DEV720. I am not going to repeat the whole statement, just know that I changed the remote database name from DEV730 to DEV720 in FROM part of the previous SQL statement. The results returned are:
O/S Ver Rel Host name Tot CPUs Config CPUs
----- --- --- -------------- -------- -----------
IBM i 7 2 DEV720.RZKH.DE 1 1
Tot config mem Tot mem
-------------- -------
3072 4096
|
If I wanted to I could create a result that formats the host name, operating system name, release and version in a more human friendly way. I just use the RTRIM to remove the trailing spaces from the strings in the returned columns, and the double pipes ( || ) to concatenate everything together:
SELECT RTRIM(HOST_NAME) || ' is at ' ||
RTRIM(OS_NAME) || ' ' ||
RTRIM(OS_VERSION) || '.' || RTRIM(OS_RELEASE)
FROM SYSIBMADM.ENV_SYS_INFO
|
Which returns:
00001 ------------------------------ DEV740.RZKH.DE is at IBM i 7.4 |
You can learn more about the ENV_SYS_INFO View from the IBM website here.
This article was written for IBM i 7.4, and should work for some earlier releases too.
This is great and it made me think it would be nice to have a query that showed all my partitions in one query.
ReplyDeleteI tried to do that with the union statement but I kept getting an error. The SQL query processor did not let me union select statements from more than one system.
But it does let me use a global temporary table to store the results from another system.
So I did this...
DECLARE GLOBAL TEMPORARY TABLE TEMP_sysinfo LIKE SYSIBMADM.ENV_SYS_INFO;
insert into session.temp_sysinfo
select * from system1.SYSIBMADM.ENV_SYS_INFO
;
insert into session.temp_sysinfo
select * from system2.SYSIBMADM.ENV_SYS_INFO
;
select * from session.temp_sysinfo;
I use a register to get the system name. That gives me just the name which I like better.
ReplyDeletevalues current_server
--------
DEV740
You can get 'V7R4M0' from various other services, like:
Select distinct RLS_LVL from license_info where LICPGM = '5770SS1';
------
V7R4M0
Now we just need to add type, model, processor feature, processor group, machine serial number, # of processors, total DASD, DASD% used and total memory etc.etc. to get everything we need in one shot. I am sure someone with better SQL skills than me can make this a lot prettier and then stick the source in a View, but it does work:
with TMP1 as (
select current_server system, substr(host_name,1,10) server2,
TOTAL_JOBS_IN_SYSTEM jobs_in_system, MAXIMUM_JOBS_IN_SYSTEM max_jobs,
CONFIGURED_CPUS #CPUS1 from table(system_status())
),
TMP2 as (
SELECT current_server system,
decimal(sum(TOTCAP/1000),9,0) total_DASD_GB,
decimal(sum(TOTCAP-TOTCAPA)/1000,9,0) DASD_used_GB,
decimal((sum(TOTCAP)-sum(totcap-TOTCAPA))/1000,9,0) DASD_Avl_GB,
decimal(decimal(sum(totcap-totcapa),9,0)/decimal(sum(totcap),9,0) * 100, 4,1) as Pct_DASD_Used
FROM asp_info
),
TMP3 as (
SELECT distinct current_server system, RLS_LVL Release, PROC_GROUP ProcGroup
FROM license_info WHERE LICPGM = '5770SS1'
),
TMP4 as (
SELECT current_server system, decimal(TOTAL_CPUS,4,0) #CPUs,
decimal(configMEM/1000,9,0) Total_Mem_GB FROM sysibmadm/env_sys_info
),
TMP5 as (
select DOSNAM system, DOSTYP type, DOSMOD Model
FROM QPFRDATA.QAPMHDWR
FETCH FIRST ROW ONLY --TY Simon :)
),
TMP6 as (
SELECT current_server system, curcharval Model
FROM system_value_info WHERE sysvalname = 'QMODEL'
),
TMP7 as (
SELECT current_server system, curcharval ProcFeat
FROM system_value_info WHERE sysvalname = 'QPRCFEAT'
),
TMP8 as (
SELECT current_server system, curcharval Serial#
FROM system_value_info WHERE sysvalname = 'QSRLNBR'
)
select current_date date, current_server system, TMP3.Release,
TMP6.Model, TMP5.type, TMP7.ProcFeat, TMP3.ProcGroup, TMP8.Serial#, TMP4.#CPUs, TMP4.Total_Mem_GB, TMP2.total_DASD_GB, TMP2.DASD_used_GB, TMP2.Pct_DASD_Used,
TMP1.jobs_in_system, TMP1.max_jobs
from TMP1
join TMP2 on TMP1.system = TMP2.system
join TMP3 on TMP1.system = TMP3.system
join TMP4 on TMP1.system = TMP4.system
join TMP5 on TMP1.system = TMP5.system
join TMP6 on TMP1.system = TMP6.system
join TMP7 on TMP1.system = TMP7.system
join TMP8 on TMP1.system = TMP8.system
;
DATE............1/15/2020
SYSTEM..........DEV740
RELEASE.........V7R4M0
MODEL...........41A
TYPE............8286
PROCFEAT........EPXK
PROCGRP.........P05
SERIAL#.........681275X
#CPUS...........4
TOTAL_MEM_GB....62
TOTAL_DASD_GB...3490
DASD_USED_GB....1334
PCT_DASD_USED...38.2
JOBS_IN_SYSTEM..1234
MAX_JOBS........163520
Dan
with TMP1 as (select current_server System,
ReplyDeleteMACHINE_MODEL Model, -- (7.3 TR18 7.4 TR 2)
MACHINE_TYPE Type, -- (7.3 TR18 7.4 TR 2)
SERIAL_NUMBER Serial#, -- (7.3 TR18 7.4 TR 2)
CONFIGURED_CPUS #CPUS,
DEFINED_MEMORY / 1000 Total_Mem_GB,
SYSTEM_ASP_STORAGE / 1000 Total_Dasd_GB,
decimal(SYSTEM_ASP_STORAGE * (SYSTEM_ASP_USED) / 100000,9,0)
Dasd_Used_GB,
SYSTEM_ASP_USED Pct_Dasd_Used,
TOTAL_JOBS_IN_SYSTEM Jobs_in_system,
MAXIMUM_JOBS_IN_SYSTEM Max_jobs
from QSYS2.SYSTEM_STATUS_INFO),
TMP3 as (SELECT distinct current_server System,
RLS_LVL Release,
PROC_GROUP ProcGroup
FROM QSYS2.LICENSE_INFO WHERE LICPGM = '5770SS1'),
TMP7 as (SELECT current_server system,
CURCHARVAL ProcFeat
FROM QSYS2.SYSTEM_VALUE_INFO WHERE SYSVALNAME = 'QPRCFEAT')
select current_date date, current_server system,
TMP3.Release,
Model,
Type,
TMP7.ProcFeat,
TMP3.ProcGroup,
SERIAL#,
#CPUS,
Total_Mem_GB,
Total_Dasd_GB,
Dasd_Used_GB,
Pct_Dasd_Used,
Jobs_in_System,
Max_Jobs
from TMP1
join TMP3 on TMP1.system = TMP3.system
join TMP7 on TMP1.system = TMP7.system;
Just a question: When I use the ENV_SYS_INFO View (by the way, it works on IBM i 7.3), the Host name appears as UNKNOWN. Where do i have to enter the relevant host name in order for it to appear in the view?
ReplyDeleteIf you use the exact copy of the code I gave, above, and the host name is "UNKNOWN" I would assume that one has not been set up for your partition. Check with your Sys Admin. If it is not that then I have no idea what could be causing it.
Delete