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:

• HARDWARE_RESOURCE_INFO Table Function
• HARDWARE_RESOURCE_INFO View

 

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