Wednesday, July 10, 2024

Deleting old device definitions

Last month I wrote about a way to list all of the unused virtual devices in your partition. In response to the article I received a question: "Is there a way to then delete these devices, other than manually going through? One system I just did has over 5,000 devices...."

The answer, of course, is yes there is. Before I give my final result let me go through the steps I did to create a SQL script to delete the unused virtual devices.

I can identify the virtual devices using the Object Statistics SQL Table function. I do not want to repeat myself. For the details read my previous article.

I decide to create a Common Table Expression, CTE, as it was just "neater". In the first part of the statement I would create a virtual table of all the eligible virtual devices, and in the second I would delete them. To start with I just want to make sure I gather the data I expected, therefore, my first statement did not do any deleting.

This was my first statement to gather the eligible virtual devices, and create the command string to delete them:

01  WITH T0(OBJNAME,CRTDATE,LASTUSEDDATE)
02  AS
03  (SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP)
04     FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
05    WHERE OBJATTRIBUTE = 'DSPVRT'
06      AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 6 MONTHS)

07  SELECT OBJNAME,CRTDATE,LASTUSEDDATE,
08         'DLTDEVD DEVD(' || OBJNAME || ')' AS "Command"
09    FROM T0 ;

Line 1: All CTE start with "WITH". I always call my first virtual table T0. It will contain three columns:

  1. OBJNAME:  Virtual device name
  2. CRTDATE:  The date the object was created
  3. LASTUSEDDATE:  The date the object was last used

Lines 3 – 6: This is the SQL statement to create the virtual table T0. Rather than use timestamp values for the Create date and Last Used date I converted the timestamp columns returned from Object Statistics to dates, using the Date scalar function. And I only want to return the virtual devices that have not been used in the last 6 months, line 6.

Lines 7 – 9: In the second part I can use the Select statement to list the contents of T0, and create a new column that contains the command I would use the delete eligible virtual devices, in a column I have called "Command".

The first four results are:

OBJNAME      CRTDATE      LASTUSEDDATE  Command
-----------  -----------  ------------  ------------------------
QPADEV0001   2021-05-14   2023-12-19    DLTDEVD DEVD(QPADEV0001)
QPADEV0002   2021-05-15   2023-12-07    DLTDEVD DEVD(QPADEV0002)
QPADEV0003   2021-05-15   2023-12-07    DLTDEVD DEVD(QPADEV0003)
QPADEV0004   2021-05-15   2023-12-07    DLTDEVD DEVD(QPADEV0004)

I am not going to want to watch my statement delete, say, 5,000 virtual devices so I would want to create a SQL statement I could put in a program. I am going to use the QCMDEXC scalar function to do the deleting. It returns a return code; therefore, I need to create an output file that the return code and other pertinent columns can be in. To accomplish this I need to take the previous SQL statement and wrap it in a "CREATE TABLE" expression that will generate table when the statement is executed.

My statement now looks like:

01  CREATE TABLE QTEMP.OUTFILE
02  (DEVICE_NAME,CREATE_DATE,LAST_USED_DATE,
03   DELETE_COMMAND,RETURN_CODE)
04  AS
05  (WITH T0(OBJNAME,CRTDATE,LASTUSEDDATE)
06   AS
07   (SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP)
08      FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD')) 
09     WHERE OBJATTRIBUTE = 'DSPVRT'
10       AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 6 MONTHS)
   
11  SELECT OBJNAME,CRTDATE,LASTUSEDDATE,
12         'DLTDEVD DEVD(' || OBJNAME || ')',
13         QSYS2.QCMDEXC('DLTDEVD DEVD(' || OBJNAME || ')')
14    FROM T0)
15  WITH DATA ;

Line 1: I am calling this table OUTFILE and placing it in the library QTEMP.

Lines 2 and 3: I am going to give the columns in the table meaningful names, so I do not have to describe what information they contain.

Lines 5 – 12: The same as my previous statement.

Line 13: This is where I am using the QCMDEXC scalar function to perform delete of the virtual device. The scalar function returns "1" if it is successful, and "-1" is it fails.

When I execute the above I generate the table in QTEMP. To view the contents of the table I would use:

01  SELECT * FROM QTEMP.OUTFILE ;

The first four results are:

DEVICE_NAME  CREATE_DATE  LAST_USED_DATE  DELETE_COMMAND            RETURN_CODE
-----------  -----------  --------------  ------------------------  -----------
QPADEV0001   2021-05-14   2023-12-19      DLTDEVD DEVD(QPADEV0001)  1
QPADEV0002   2021-05-15   2023-12-07      DLTDEVD DEVD(QPADEV0002)  1
QPADEV0003   2021-05-15   2023-12-07      DLTDEVD DEVD(QPADEV0003)  1
QPADEV0004   2021-05-15   2023-12-07      DLTDEVD DEVD(QPADEV0004)  1

As the Return Code is all "1" then I know I deleted those virtual devices.

I can now take that statement and put it in a RPG program:

01  **free
02  exec sql DROP TABLE IF EXISTS QTEMP.OUTFILE ;

03  exec sql CREATE TABLE QTEMP.OUTFILE
04           (DEVICE_NAME,CREATE_DATE,LAST_USED_DATE,
05            DELETE_COMMAND,RETURN_CODE)
06           AS
07           (WITH T0(OBJNAME,CRTDATE,LASTUSEDDATE)
08           AS
09           (SELECT OBJNAME,DATE(OBJCREATED),DATE(LAST_USED_TIMESTAMP)
10              FROM TABLE(QSYS2.OBJECT_STATISTICS('QSYS','DEVD'))
11             WHERE OBJATTRIBUTE = 'DSPVRT'
12               AND LAST_USED_TIMESTAMP < CURRENT_TIMESTAMP - 6 MONTHS)

13           SELECT OBJNAME,CRTDATE,LASTUSEDDATE,
14                  'DLTDEVD DEVD(' || OBJNAME || ')',
15                  QSYS2.QCMDEXC('DLTDEVD DEVD(' || OBJNAME || ')')
16             FROM T0)
17          WITH DATA ;

18  *inlr = *on ;

Line 2: Here I use the DROP TABLE statement to delete the Table, if it exists.

Lines 3 – 17: This statement is the same as the previous one.

I can then call the program and know that all of my old unused virtual devices will be deleted.

 

This article was written for IBM i 7.5, and should work for some earlier releases too.

1 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.