Several months ago I publishing a post about finding the detached journal receivers in a partition. I mentioned if I want to delete those receivers I would create an output table, and then read that in a CL program and use the Delete Journal Receiver command, DLTJRNRCV, to delete the receivers one at a time.
Shortly after I received an email from Sue Romano, who is member of the Db2 for i development team, giving me a simpler alternative. Her examples used the QCMDEXC scalar function.
The QCMDEXC scalar function allows me to execute a CL command every time a row is returned in the results of a SQL statement.
She gave two examples, the first is what I would call a “sanity check”, to make sure that my statement returns the results I desire. And then a statement to delete the journal receivers.
Both of these are Common Table Expressions, CTE, which are a combination of SQL statements to produce one final set of results. The first part of the statement generates the a “virtual table” of results from the SQL view JOURNAL_RECEIVER_NAME, and the second does the action I want to do with those results.
The code I show below is Sue’s. My additions are in lower case.
Below is the “sanity check” statement:
01 WITH OLD_JRNRCV AS(
02 SELECT JOURNAL_RECEIVER_LIBRARY AS LIB,JOURNAL_RECEIVER_NAME AS NAME,DETACH_TIMESTAMP
03 FROM QSYS2.JOURNAL_RECEIVER_INFO
04 WHERE DETACH_TIMESTAMP IS NOT NULL
05 AND DETACH_TIMESTAMP < CURRENT_TIMESTAMP – 1 YEAR)
06 SELECT ‘DLTJRNRCV ‘ CONCAT LIB CONCAT ‘/’ CONCAT NAME
07 FROM OLD_JRNRCV ;
Lines 1 – 5: This is the part of the CTE that generated the “virtual table”, OLD_JRNRCV. Nothing really special here. I just select the journal receiver library, that is renamed to LIB, journal receiver name, renamed to NAME, and the detach timestamp. The detach timestamp is used to determine if the receiver is detached, not null, and is older than a year.
Lines 6 and 7: In the second part I just concatenate the LIB and NAME columns from the “virtual table” to create a prototype delete statement. I do not have to trim the columns are they are both VARCHAR, variable length character, type.
When this statement is run I get the following results:
00001
—————————–
DLTJRNRCV LIBRARY1/JOURNR0116
DLTJRNRCV LIBRARY1/JOURNR0117
DLTJRNRCV LIBRARY1/JOURNR0118
DLTJRNRCV LIBRARY1/JOURNR0119
DLTJRNRCV LIBRARY1/JOURNR0120
The second statement, that actually does the deleting of the journal receivers, looks pretty similar to the one above.
01 WITH OLD_JRNRCV AS(
02 SELECT JOURNAL_RECEIVER_LIBRARY AS LIB,JOURNAL_RECEIVER_NAME AS NAME,DETACH_TIMESTAMP
03 FROM QSYS2.JOURNAL_RECEIVER_INFO
04 WHERE DETACH_TIMESTAMP IS NOT NULL
05 AND DETACH_TIMESTAMP < CURRENT_TIMESTAMP – 1 YEAR)
06 SELECT QSYS2.QCMDEXC(‘DLTJRNRCV ‘ CONCAT LIB CONCAT ‘/’ CONCAT NAME
07 || ‘ dltopt(*igntgtrcv *igninqmsg *ignexitpgm)’)
08 FROM OLD_JRNRCV ;
Lines 1 – 5: Same as above.
Lines 6 – 8: I am using the QCMDEXC scalar function to perform the delete of the journal receivers listed in the “virtual table”. I added line 7 as I have found that adding these to the DLTJRNRCV command prevents warning errors.
Thank you Sue for these examples of how simple it is to use the QCMDEXC scalar function within a CTE. I am now thinking of other processes I have created that I could replace with a CTE like this.
This article was written for IBM i 7.5, and should work for some earlier releases too.
