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.