Wednesday, November 16, 2022

Using SQL QCMDEXC to simplify cleanup

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.

11 comments:

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

    ReplyDelete
  2. Thank you for your articles.
    A little curiosity: why is the last concat done with a double pipe?

    ReplyDelete
    Replies
    1. Sue uses proper ANSI compliant SQL in her statement, which is why she used CONCAT.
      My 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.

      Delete
  3. This is awesome. This technique can be used for many things. Thank you for this.

    ReplyDelete
  4. This is great, and I will definitely find some use cases for this.

    ReplyDelete
  5. QCMDEXC 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 ;)

    ReplyDelete
  6. graet idea to cleanup. Is it possible to cleanup folders in IFS, for example

    with 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?

    ReplyDelete
    Replies
    1. You can run SQL statements in a CL program using the RUNSQL command. Warning: It does not support all SQL statements.

      Delete
  7. Nice article. can we get this query sorted by size? Please suggest.

    ReplyDelete
    Replies
    1. The size of what?
      Size of object? Size of library? etc.

      Delete
  8. This comment has been removed by a blog administrator.

    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.