Wednesday, January 15, 2020

Retrieving the Host's name using SQL view

getting the system name from a sql view

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.

3 comments:

  1. This is great and it made me think it would be nice to have a query that showed all my partitions in one query.
    I 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;

    ReplyDelete
  2. I use a register to get the system name. That gives me just the name which I like better.

    values 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

    ReplyDelete
  3. with TMP1 as (select current_server System,
    MACHINE_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;

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.