Wednesday, May 3, 2023

Using SQL to retrieve information about hardware resources

Amongst the many new additions to the latest Technology Refreshes, IBM i 7.5 TR1 and 7.4 TR7, comes a new View and Table Function that allows me to retrieve information about a partition's hardware resources, including its status.

Both have the same name, HARDWARE_RESOURCE_INFO, and are in the library QSYS2. The View returns information for all hardware resources, the Table Function returns can be used to only return the information about one type of hardware resource or another. Both return the same columns in their results.

Before I start showing you examples of the results from these two I recommend you run the following SQL statement. This will allow you to see all the available columns, and help you decide what is useful to you.

01  SELECT * FROM QSYS2.HARDWARE_RESOURCE_INFO

The resources are grouped by the CATEGORY column, by type. The first statement I ran was what are the different resource categories, and how many there are of each one.

In the statement below I use the View to give me that list:

01  SELECT CATEGORY,COUNT(CATEGORY) AS "Count"
02    FROM QSYS2.HARDWARE_RESOURCE_INFO 
03   GROUP BY CATEGORY
04   ORDER BY CATEGORY

Line 1: I just want the Category type and the count of the number of rows of each category. I am using the short name, CATEGORY, rather than the long name, RESOURCE_CATEGORY, just because it is quicker to type.

Line 3: The GROUP BY is needed to sub-total by category.

Line 4: And I want my results in order by category.

The results for the test partition I am using are:

CATEGORY       Count
------------   -----
COMM              11
PROCESSOR         31
STORAGE           42
WORKSTATION        3

The equivalent statement using the Table Function would be:

01  SELECT RESOURCE_CATEGORY,COUNT(*) AS "Count"
02    FROM TABLE(QSYS2.HARDWARE_RESOURCE_INFO(
03                   RESOURCE_CATEGORY => 'ALL',
04                   DETAILED_INFO => 'NO',
05                   IGNORE_ERRORS => 'YES'))
06   GROUP BY RESOURCE_CATEGORY
07   ORDER BY RESOURCE_CATEGORY

The results are the same as those from the View.

Let's dive into some of the category types I can retrieve results for. Let me start with the communication resources, using the following statement using the View:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS,
03         DEVICE_TYPE || '-' || LTRIM(DEVICE_MODEL) AS "Type & model"
04    FROM QSYS2.HARDWARE_RESOURCE_INFO 
05   WHERE CATEGORY = 'COMM'
06   LIMIT 5

Lines 1 - 3: I am retrieving the following columns from the results:

  1. ID:  A generated number for the row
  2. PARENT_ID:  If the number is null this resource is a parent. A number here indicates the parent of this resource
  3. RESOURCE_NAME:  Name of the resource
  4. TEXT_DESCRIPTION  Description of the resource
  5. STATUS  The status of the resource. Can be OPERATIONAL, INOPERATIVE, NOT DETECTED, or null. Null indicates either that the device does not have a status, or the status was not determined
  6. DEVICE_TYPE and DEVICE_MODEL:  I think the names are self-explanatory. I have concatenated them into one column.

Line 5: Only select the communication resources.

Line 6: Only return the first five results.

The results are:

                                                             Type &
ID  PARENT_ID  RESOURCE_NAME  TEXT_DESCRIPTION  STATUS       model
--- ---------  -------------  ----------------  -----------  --------
  1    <NULL>  CMB04          Comm Processor    OPERATIONAL  6B03-001  	
  2         1  LIN02          Comm Adapter      OPERATIONAL  6B03-001	
  3         2  CMN01          Comm Port         OPERATIONAL  6B03-001	
  4    <NULL>  CMB05          Comm Processor    OPERATIONAL  6B03-001	
  5         4  LIN01          Comm Adapter      OPERATIONAL  6B03-001

ID 1 and 4 are parents. 2 and 3 are children of 1, and 5 is a child of 4.

The equivalent using the Table Function would be:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS,
03         DEVICE_TYPE || '-' || LTRIM(DEVICE_MODEL) AS "Type & model"
04   FROM TABLE(QSYS2.HARDWARE_RESOURCE_INFO(
05                   RESOURCE_CATEGORY => 'COMM',
06                   DETAILED_INFO => 'NO',
07                   IGNORE_ERRORS => 'YES'))
08  LIMIT 5

Lines 1 – 3: The same columns as I selected with the View.

Lines 4 – 7: The definition of the Table Function and its parameters. There are three parameters:

  1. RESOURCE_CATEGORY:  Resource category
  2. DETAILED_INFO:  If 'YES' all of the columns from the results are returned. When 'NO' only some are returned, those not returned are null, this makes using 'NO' faster
  3. IGNORE_ERRORS:  When 'YES' an error is encounter it a warning error is issued, and the statement continues. With 'NO' the statement returns a hard error

Line 8: Return only the first five results.

I am not going to show the results as they are identical to those returned from the View.

Let's swap our sources of information around for listing the hardware information about Storage. This time I am going to start with the Table Function. The resource category is 'STORAGE'.

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS,
03         DEVICE_TYPE || '-' || LTRIM(DEVICE_MODEL) AS "Type & model"
04  FROM TABLE(QSYS2.HARDWARE_RESOURCE_INFO(
05                    RESOURCE_CATEGORY => 'STORAGE',
06                    DETAILED_INFO => 'NO',
07                    IGNORE_ERRORS => 'YES'))
08  LIMIT 7

Lines 1 – 3: Same columns as before.

Line 5: The RESOURCE_CATEGORY parameter must be 'STORAGE'.

Line 6: The columns I want are not in the detailed information, therefore, I can speed up my SQL statement by saying I don't want the detailed information.

               RESOURCE_                                     Type &
ID  PARENT_ID  NAME       TEXT_DESCRIPTION      STATUS       model
--- ---------  ---------  --------------------  -----------  --------
  1    <NULL>  CMB07      Comm Processor        OPERATIONAL  268C-001
  2        46  DC04       Storage Controller    OPERATIONAL  6B02-001
  3        47  OPTVRT04   Optical Storage Unit  OPERATIONAL  632B-001
  4    <NULL>  CMB01      Comm Processor        OPERATIONAL  298A-001
  5        49  DC01       <NULL>                OPERATIONAL  298A-001
  6    <NULL>  CMB11      Comm Processor        OPERATIONAL  6B25-001
  7        51  DC08       <NULL>                OPERATIONAL  6B25-001

If I was to use the View to get the same information my SQL statement would be:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS,
03         DEVICE_TYPE || '-' || LTRIM(DEVICE_MODEL) AS "Type & model"
04  FROM QSYS2.HARDWARE_RESOURCE_INFO 
05  WHERE CATEGORY = 'STORAGE'
06  LIMIT 7

Next up is the resource information for processors. Again I am using the Table Function first, as it is the faster way to retrieve the results.

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS
03    FROM TABLE(QSYS2.HARDWARE_RESOURCE_INFO(
04                   RESOURCE_CATEGORY => 'PROCESSOR',
05                   DETAILED_INFO => 'NO',
06                   IGNORE_ERRORS => 'YES'))

Lines 1 and 2: I reduced the number of columns in the results.

Line 4: resource category needs to be 'PROCESSOR'.

The results are:

               RESOURCE_
ID  PARENT_ID  NAME       TEXT_DESCRIPTION           STATUS
--- ---------  ---------  -------------------------  -----------
  1    <NULL>  CEC01      Main Card Enclosure        OPERATIONAL
  2    <NULL>  PN02       System Control Panel       OPERATIONAL
  3        16  PN03       System Control Panel       OPERATIONAL
  4    <NULL>  MP05       System Processor Card      OPERATIONAL

 15    <NULL>  PV02       Processor Capacity Card    OPERATIONAL
 16    <NULL>  SP02       Service Processor Card     OPERATIONAL
 17    <NULL>  BCC02      Bus Adapter                OPERATIONAL

 19    <NULL>  MS05       65536MB Main Storage Card  OPERATIONAL

If I was to get the same results from the View my statement would be:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS
03  FROM QSYS2.HARDWARE_RESOURCE_INFO 
04  WHERE CATEGORY = 'PROCESSOR'

Next are the workstation resources. Using the Table Function my statement would be:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS
03  FROM TABLE(QSYS2.HARDWARE_RESOURCE_INFO(
04                    RESOURCE_CATEGORY => 'WORKSTATION',
05                    DETAILED_INFO => 'NO',
06                    IGNORE_ERRORS => 'YES'))

Line 4: For workstation hardware resources I need to the resource category parameter be 'WORKSTATION'.

The results are:

               RESOURCE_
ID  PARENT_ID  NAME       TEXT_DESCRIPTION     STATUS
--- ---------  ---------  -------------------  -----------
  1    <NULL>  CMB06      Comm Processor       OPERATIONAL
  2        12  CTL01      Virtual Controller   OPERATIONAL
  3        13  DSP001     Work Station Device  <NULL>

The equivalent for the View would be:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS
03  FROM QSYS2.HARDWARE_RESOURCE_INFO 
04  WHERE CATEGORY = 'WORKSTATION'

I can use the View to check if there are any hardware resources that are not operational. There are two columns I need to check: STATUS and EXTENDED_STATUS. If either columns is not 'OPERATIONAL' then there is an issue with the hardware resource. To check I would use the following statement:

01  SELECT ID,PARENT_ID,RESOURCE_NAME,TEXT_DESCRIPTION,
02         STATUS,EXTENDED_STATUS
03    FROM QSYS2.HARDWARE_RESOURCE_INFO 
04   WHERE STATUS <> 'OPERATIONAL'
05      OR EXTENDED_STATUS <> 'OPERATIONAL'

Lines 1 and 2: I have used, and described, these columns before.

Lines 4 and 5: I am checking for any rows where either of the columns contains a value that is not operational.

No results are returned, therefore, all the hardware resources are working as they should.

The documentation mentions three other types of hardware resources:

  • CRYPTO:  Cryptographic resources
  • OPTICAL:  Optical resources
  • TAPE:  Tape resources

They were not listed when I performed a count of the types of resources in an earlier statement. So what is going on?

If I use the Table Function I can get a listing of tape resources, using:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS
03  FROM TABLE(QSYS2.HARDWARE_RESOURCE_INFO(
04                    RESOURCE_CATEGORY => 'TAPE',
05                    DETAILED_INFO => 'NO',
06                    IGNORE_ERRORS => 'YES'))
07  LIMIT 5

Which returned results:

              RESOURCE_
ID PARENT_ID  NAME       TEXT_DESCRIPTION     STATUS
-- ---------  ---------  -------------------  -----------
 1    <NULL>  CMB02      Comm Processor       OPERATIONAL
 2         1  DC02       <NULL>               OPERATIONAL
 3         2  TAPMLB07	Tape Library          <NULL>
 4         3  TAP11     Tape Unit             <NULL>
 5         2  TAPMLB08	Tape Library          <NULL>

If the View does not recognize these resources as tape what does it categorize them as?

01  SELECT RESOURCE_NAME,CATEGORY
02    FROM QSYS2.HARDWARE_RESOURCE_INFO 
03   WHERE RESOURCE_NAME LIKE 'TAP%'

Line 3: I am looking for all resources where its name starts with the characters 'TAP'.

The results surprised me:

RESOURCE_NAME  CATEGORY
-------------  --------
TAPMLB07       STORAGE
TAP11          STORAGE
TAPMLB08       STORAGE
TAP12          STORAGE
TAPMLB06       STORAGE
TAP10          STORAGE

All of the tape resources have been recognized as storage resources. I checked with IBM, and they confirmed that this was correct as a tape is a storage device. Personally, I am not happy with that explanation.

What about optical resources? Using the Table Function I created the following SQL statement:

01  SELECT ID,PARENT_ID,
02         RESOURCE_NAME,TEXT_DESCRIPTION,STATUS
03    FROM TABLE(QSYS2.HARDWARE_RESOURCE_INFO(
04                    RESOURCE_CATEGORY => 'OPTICAL',
05                    DETAILED_INFO => 'NO',
06                    IGNORE_ERRORS => 'YES'))

Line 4: I only want to select optical resources.

The results returned are:

              RESOURCE_
ID PARENT_ID  NAME       TEXT_DESCRIPTION      STATUS
-- ---------  ---------  --------------------  -----------
 1    <NULL>  CMB08      Comm Processor        OPERATIONAL
 2         1  DC06       <NULL>                OPERATIONAL
 3         2  OPT05      Optical Storage Unit  OPERATIONAL

How are they categorized in the View?

01  SELECT RESOURCE_NAME,CATEGORY
02    FROM QSYS2.HARDWARE_RESOURCE_INFO 
03   WHERE RESOURCE_NAME IN ('CMB08','DC06','OPT05')

Line 3: I only want to return the results for the resources listed.

The results are:

RESOURCE_NAME  CATEGORY
-------------  --------
CMB08          STORAGE
DC06           STORAGE
OPT05          STORAGE

Optical devices are also recognized as storage in the View.

None of the partitions I use had any cryptographic resources.

I am sure many will find these two useful additions for learning about the hardware resources assigned to their partitions. I am still not sure if I agree with IBM's reasoning that the tape and optical resources are given as storage in the View.

 

You can learn more about this from the IBM website:

 

This article was written for IBM i 7.5 TR1 and 7.4 TR7.

No comments:

Post a Comment

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.