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.
Very cool. I've been a developer on the i for over 30 years and I always learn something from your articles. Thank you very much for doing this.
ReplyDeleteThank you for your articles.
ReplyDeleteA little curiosity: why is the last concat done with a double pipe?
Sue uses proper ANSI compliant SQL in her statement, which is why she used CONCAT.
DeleteMy code is only ever going to run in IBM i, therefore, I use the IBM i specific shortcut (||) instead of CONCAT. I prefer it as when I concatenate a lot of columns together IMHO it is a lot easier to quickly see which columns are being concatenated when I use ||. If I use CONCAT I find it becomes too verbose.
This is awesome. This technique can be used for many things. Thank you for this.
ReplyDeleteThis is great, and I will definitely find some use cases for this.
ReplyDeleteQCMDEXC scalar function even returns an error indicator as result... useful to check those if this command is launched on hundreds of rows in one shot for eventual error handling ;)
ReplyDeletegraet idea to cleanup. Is it possible to cleanup folders in IFS, for example
ReplyDeletewith oldfiles as (
select path_name as name, date(create_timestamp) as datum
from table(qsys2.IFS_OBJECT_STATISTICS('/Shop/archiv/Shopware/pricebooks', 'NO' , '*STMF'))
where date(create_timestamp) < current date - 2 Month
select qsys2.qcmdexc('RMVLNK (''' concat name concat ''')' )
from oldfiles
;
)
is it also possible to programm the sql-statements in a CL-Programm?
You can run SQL statements in a CL program using the RUNSQL command. Warning: It does not support all SQL statements.
DeleteNice article. can we get this query sorted by size? Please suggest.
ReplyDeleteThe size of what?
DeleteSize of object? Size of library? etc.
This comment has been removed by a blog administrator.
ReplyDelete