Displaying the configuration status using SQL

I don't use the Work with Configure Status command, WRKCFGSTS, frequently, mostly to vary on and off devices and occasionally controllers. I know that others do, and I have been asked to provide information such as a list of all the controllers' status.

As part of the last round of Technology Refreshes, IBM i TR5 and 7.5 TR11, a new View was added that allows us to see the same information in a better way.

With the WRKCFGSTS I am limited with the data I can retrieve.

Work with Configuration Status (WRKCFGSTS) Type choices, press Enter. Type . . . . . . . . . . . . . . CFGTYPE Configuration description . . . CFGD *ALL Output . . . . . . . . . . . . . OUTPUT * Remote location . . . . . . . . RMTLOCNAME *NONE Range . . . . . . . . . . . . . RANGE *NET Status . . . . . . . . . . . . . STATUS *ALL

The valid configuration types, CFGTYPE, are:

• *CTL:  Controllers
• *DEV:  Devices
• *LIN:  Lines
• *NWI:  Network interfaces
• *NWS:  Network server descriptions

I can only select any one of them or '*ALL', not several.

The output is restricted to the current device ( * ) or spool file ( *PRINT ).

If I wanted to use the information in a program I would need to use the spool file, and the data is static.

The new View, CONFIGURATION_STATUS in the library SYSTOOLS, by its nature of being a view allows me more flexibility to select and sort the data I desire, and it is dynamic.

If this is the first time you will be looking at this View I recommend that you use the following statement to see what columns of information are included:

01 SELECT * FROM SYSTOOLS.CONFIGURATION_STATUS

I am only interest in several of the columns, which suit my needs.

The first thing I wanted to know was how many objects are there for each configuration type:

01 SELECT OBJECT_TYPE,OBJECT_ATTRIBUTE, 02 COUNT(*) AS "Count" 03 FROM SYSTOOLS.CONFIGURATION_STATUS 04 GROUP BY OBJECT_TYPE,OBJECT_ATTRIBUTE 05 ORDER BY OBJECT_TYPE,OBJECT_ATTRIBUTE

Line 1: I want to get a count by object type, OBJECT_TYPE, and object attribute, OBJECT_ATTRIBUTE.

Line 2: The Count scalar function.

Lines 4 and 5: I group and order by the there two columns to create the results I want:

OBJECT OBJECT_ _TYPE ATTRIBUTE Count ------ --------- ----- *CTLD LWS 2 *CTLD NET 1 *CTLD VWS 6 *DEVD DSPLCL 2 *DEVD DSPVRT 28 *DEVD NET 1 *DEVD OPT 2 *DEVD TAP 4 *LIND ETH 1

Another good example is a question I was asked several weeks ago: When was the last time the tape drives on the partition were used? This would have been so easy using the CONFIGURATION_STATUS:

01 SELECT OBJECT_NAME,STATUS,STATUS_DESCRIPTION, 02 DATE(LAST_USED_TIMESTAMP) AS "Last used" 03 FROM SYSTOOLS.CONFIGURATION_STATUS 04 WHERE OBJECT_ATTRIBUTE = 'TAP' 05 ORDER BY OBJECT_NAME

Line 2: I don't need the timestamp to answer this question, only the date the tape drives were used.

Line 4: All of the tape drives have the attribute of '*TAP'.

The results are:

OBJECT STATUS_ _NAME STATUS DESCRIPTION Last used ------ ------ ----------- --------- TAP01 0 VARIED OFF 2024-07-14 TAP02 0 VARIED OFF 2024-07-14 TAP03 0 VARIED OFF 2024-08-17 TAP04 0 VARIED OFF 2024-09-07

I would have guessed that the status code of zero means varied off. Thanks to the status description I have my assumption confirmed.

My last example brings up a subject I have written about before, determine when a virtual device was last used. I can do better here by also returning the status of the device:

01 SELECT OBJECT_NAME,TEXT_DESCRIPTION, 02 STATUS,STATUS_DESCRIPTION, 03 DATE(LAST_USED_TIMESTAMP) AS "Last used date" 04 FROM SYSTOOLS.CONFIGURATION_STATUS 05 WHERE OBJECT_NAME LIKE 'QPADEV%' 06 AND DATE(LAST_USED_TIMESTAMP) < (CURRENT_DATE - 14 DAYS)

I have described all of the columns used in this statement before.

Line 5: In my results I only want the virtual devices that start with the character "QPADEV".

Line 6: I only want the devices that have not been used in the past two weeks.

My results are:

OBJECT TEXT_ STATUS_ Last used _NAME DESCRIPTION STATUS DESCRIPTION date ---------- ----------------------- ------ --------------- ---------- QPADEV0001 Device created for PGM. 0 VARIED OFF 2024-04-19 QPADEV0002 Device created for PGM. 20 VARY ON PENDING 2025-01-28 QPADEV0003 Device created for PGM. 20 VARY ON PENDING 2025-01-23

IMHO this is another useful addition to our SQL "tool set". While I would not use it frequently, I can see cases where its results would be interesting to other members in the organization I work for.

 

You can learn more about the CONFIGURATION_STATUS SQL View from the IBM website here.

 

This article was written for IBM i 7.5 TR5 and 7.4 TR11.