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.