Wednesday, June 5, 2024

List unused virtual devices

This was a first: I was asked by the auditors for a list of virtual devices that had not been used in the past six months. In all my years of dealing with auditors this was the first time I had been asked this question.

It is not a big deal to get this information. All virtual devices are just device descriptions, object type '*DEVD', with the object attribute of 'DSPVRT'. As they are objects I can get this information from the SQL Table function OBJECT_STATISTICS.

What columns of data am I interested in?

  • Object's attribute
  • Object's name
  • The date the object was created
  • The date the object was last used
  • Object's description

My SQL statement to retrieve the data the auditors are interested in would be:

01  SELECT OBJATTRIBUTE AS "Attrib",
02         OBJNAME,
03         DATE(OBJCREATED) AS "Created",
04         DATE(LAST_USED_TIMESTAMP) AS "Last used",
05         OBJTEXT
06    FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
07   WHERE OBJATTRIBUTE = 'DSPVRT'
08     AND DATE(LAST_USED_TIMESTAMP) < CURRENT_DATE - 6 MONTHS

Line 1: I feel the name of the column is too long for displaying, therefore, I am giving it a short column name.

Line 2: The object name column.

Line 3: The request was for dates, not timestamps, therefore, I am using the Date scalar function to return the date part of the timestamp. I am also giving this column a new name.

Line 4: Ditto.

Line 5: Object's text.

Line 6: I am using the OBJECT_STATISTICS Table function, and passing to it that I want to look in the library QSYS for all device descriptions.

Line 7: I am only interested in virtual devices being in my results.

Line 8: And only those where the last used date of the object is older than six months ago.

The results are:

Attrib  OBJNAME     Created     Last used   OBJTEXT
------  ----------  ----------  ----------  --------------------------
DSPVRT  QPADEV0291  2022-08-05  2023-05-16  Device created for DEV750.
DSPVRT  QPADEV0292  2022-08-05  2023-05-16  Device created for DEV750.
DSPVRT  QPADEV0293  2022-08-05  2023-05-16  Device created for DEV750.
DSPVRT  QPADEV0294  2022-08-05  2023-05-16  Device created for DEV750.
DSPVRT  QPADEV0295  2022-08-05  2023-05-16  Device created for DEV750.

What about any virtual devices that were created over six months ago and have never been used?

The statement below gives me that information.

01  SELECT OBJATTRIBUTE AS "Attrib",
02         OBJNAME,
03         DATE(OBJCREATED) AS "Created",
04         DATE(LAST_USED_TIMESTAMP) AS "Last used",
05         OBJTEXT
06    FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
07   WHERE OBJATTRIBUTE = 'DSPVRT'
08     AND DATE(OBJCREATED) < CURRENT_DATE - 6 MONTHS
09     AND LAST_USED_TIMESTAMP IS NULL

Lines 1 – 7: These are the same as the lines in my previous statement.

Line 8: I need to check if the virtual device was created more than six months ago.

Line 9: If the virtual device has never been used then the last used timestamp is null.

There is only one result from this statement:

Attrib  OBJNAME     Created     Last used   OBJTEXT
------  ----------  ----------  ----------  --------------------------
DSPVRT  QPADEV0300  2022-08-05  <NULL>      Device created for DEV750.

The auditors wanted this information in an Microsoft Excel spreadsheet. I decided to create an output file from the results, so I would be able to use the data if they wanted any further actions.

I need to combine the results from the two statements, which I can do using a Union clause.

01  CREATE OR REPLACE TABLE MYLIB.AUDIT007
02  (DEVICE,CREATED,LAST_USED,OBJTEXT)
03  AS
04  (SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP),OBJTEXT
05     FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
06    WHERE OBJATTRIBUTE = 'DSPVRT'
07      AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 6 MONTHS
08   UNION
09   SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP),OBJTEXT
10     FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
11    WHERE OBJATTRIBUTE = 'DSPVRT'
12      AND OBJCREATED < CURRENT_TIMESTAMP - 6 MONTHS
13      AND LAST_USED_TIMESTAMP IS NULL)
14  WITH DATA ON REPLACE DELETE ROWS ;

Line 1: I have used the Create or Replace Table which will replace a file with the same name if it is present in my library.

Line 2: I am using a columns list so the names of the columns are what I want them to be.

Line 4 – 7: This is the same as the first SQL statement I showed above.

Line 8: The Union clause to join the two sets of results.

Lines 9 – 13: This is the same as the second SQL statement from earlier.

Line 10: The With data means that my Table will contain data. The On replace delete rows tells the SQL engine what to do with any existing data if the Table is being replaced.

After executing the above, I can then look my results with the following statement:

01  SELECT * FROM MYFILE.AUDIT007
02   ORDER BY 3,1

Line 2: I want to order the results by the Last used date and then the Device name.

The results are:

DEVICE      CREATED     LAST_USED   OBJTEXT
----------  ----------  ----------  --------------------------
QPADEV0291  2022-08-05  2023-05-16  Device created for DEV750.
QPADEV0292  2022-08-05  2023-05-16  Device created for DEV750.
QPADEV0293  2022-08-05  2023-05-16  Device created for DEV750.
QPADEV0294  2022-08-05  2023-05-16  Device created for DEV750.
QPADEV0295  2022-08-05  2023-05-16  Device created for DEV750.
QPADEV0300  2022-08-05  <NULL>      Device created for DEV750.

Now I have my results I can either save the results from my ACS's Run SQL Scripts, RSS, or use the Generate Spreadsheet Scalar function or the CPYTOIMPF command to generate a file in the IFS that I can then email to myself.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.

1 comment:

  1. Hello,
    I seem to have been informed that a large number of unused *DEVD DSPVRTs can have the side effect of slowing down the IPL, hence the need to “clean up” among them. Can anyone confirm this?
    Thank you.

    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.