Retrieving PTF cover letters using SQL

In preparation for forthcoming annual audits I was asked if there was a way to be download the PTF cover letters for all the PTFs that had been applied in 2023 in a format that could be searched?

In previous years all of the PTF cover letters were printed out and retained. If anyone had a question about whether a PTF was applied to a certain feature the printed letters were manually searched. This could take a long time.

Fortunately the last Technology Refreshes. IBM i 7.5 TR3 and 7.4 TR9, comes with a something I can use. Within them is a new Db2 for i, SQL, table function to View PTF cover letters, PTF_COVER_LETTER. If I can view a cover letter I can also insert it into a table. Which is a lot better than using the output from Display PTF Cover Letter command, DSPPTFCVR.

The PTF_COVER_LETTER table function has two parameters:

1. PTF_ID:  The PTF number of the PTF I want the cover letter for.
2. IGNORE_ERRORS:  If an error is encountered should the table function ignore it? Valid values are 'NO' and 'YES', which is the default.

I can just use the table function in a Select statement, passing it a PTF number:

01 SELECT * 02 FROM TABLE(SYSTOOLS.PTF_COVER_LETTER('MF68675'))

The first few lines of the results are:

LINE_ NUMBER PTF_COVER_LETTER_LINE ------ ----------------------------------------------------- 1 .. 5770999 5050 0000 MF68675 2924 R04M00 0007 2 ., LIC-DB-OTHER-F/RMSLRELEASESEIZENOTOKEN-T/QQQOOO... 3 4 PTF/FIX #: MF68675 - Licensed Internal Code 5 LICENSED PROGRAM: 5770999 6 7 --------------------------------------------------... 8 : : RELEASE : LEVEL : RECOMPILE : LIBRA... 9 : IBM : : MIN/MAX : : ... 10 : : : : : ...

The results consist of two columns:

1. LINE_NUMBER
2. PTF_COVER_LETTER_LINE

I am sure you have noticed that I am working on a IBM i 7.4 partition. For some reason the IBM i 7.5 partition I usually work is not doing what I need it to.

It is not practical to have someone enter all the PTFs one at a time and run this table function. Fortunately I can get a list of all those PTFs from the PTF_INFO View. I can use this to get a count of the number of PTFs that were permanently applied in 2023:

01 SELECT COUNT(*) 02 FROM QSYS2.PTF_INFO 03 WHERE PTF_COVER_LETTER = 'YES' 04 AND PTF_LOADED_STATUS = 'PERMANENTLY APPLIED' 05 AND PTF_STATUS_TIMESTAMP BETWEEN '2023-01-01-00.00.00.000000' AND '2024-01-01-00.00.00.000000'

Line 1: I want a count of the number of PTFs.

Line 2: From the PTF_INFO view.

Line 3: Only for PTFs that have a cover letter.

Line 4: And are permanently applied.

Line 5: And the status timestamp is in last year. I know that '2024-01-01-00.00.00.000000' is not in 2023, I doubt any PTFs were applied at that time.

My result is:

00001 ------ 481

What I need to do is to create a SQL statement that gets the PTF numbers from PTF_INFO and uses that with PTF_COVER_LETTER to fetch the PTF cover letter. This is the statement I used:

01 WITH T0 (PTF_ID,TSTAMP) AS 02 (SELECT PTF_IDENTIFIER,PTF_STATUS_TIMESTAMP 03 FROM QSYS2.PTF_INFO 04 WHERE PTF_COVER_LETTER = 'YES' 05 AND PTF_LOADED_STATUS = 'PERMANENTLY APPLIED' 06 AND PTF_STATUS_TIMESTAMP BETWEEN '2023-11-01-00.00.00.000000' AND '2023-12-01-00.00.00.000000' 07 ORDER BY PTF_STATUS_TIMESTAMP) 08 SELECT T0.PTF_ID,A.*,T0.TSTAMP 09 FROM T0, TABLE(SYSTOOLS.PTF_COVER_LETTER(PTF_ID)) A 10 ORDER BY T0.PTF_ID,A.LINE_NUMBER

I am using a Common Table Expression, CTE, to get what I want. The statement is in two parts.

Lines 1 – 7: The first part creates the virtual table T0 which contains all of the PTFs that were permanently applied in November 2023. In this example I just selected the PTFs for November 2023 to reduce the number results and reduce the amount of time this example would take to run.

Lines 8 – 10: Is the second part of the CTE. Here I use the results in my virtual table, T0, as the parameter for the PTF_COVER_LETTER.

The results of this statement are the following columns:

1. PTF number
2. PTF cover letter line number
3. PTF cover letter line text
4. When the PTF was permanently applied

The first ten lines of my results looks something like:

LINE_ PTF_ID NUMBER PTF_COVER_LETTER_LINE PTF_STATUS_TIMESTAMP ------- ------ ------------------------------------- -------------------------- MF68675 1 .. 5770999 5050 0000 MF68675... 2023-11-28 08:31:02.000000 MF68675 2 ., LIC-DB-OTHER-F/RMSLRELEASESEIZ... 2023-11-28 08:31:02.000000 MF68675 3 2023-11-28 08:31:02.000000 MF68675 4 PTF/FIX #: MF68675 - Licensed Int... 2023-11-28 08:31:02.000000 MF68675 5 LICENSED PROGRAM: 5770999 2023-11-28 08:31:02.000000 MF68675 6 2023-11-28 08:31:02.000000 MF68675 7 ---------------------------------... 2023-11-28 08:31:02.000000 MF68675 8 : : RELEASE : LEVEL : ... 2023-11-28 08:31:02.000000 MF68675 9 : IBM : : MIN/MAX : ... 2023-11-28 08:31:02.000000 MF68675 10 : : : : ... 2023-11-28 08:31:02.000000

Now I need to design a way to create an output file of all the PTF cover letters. I wrote a CL program to submit to batch the process that would create the output file, as it will take sometime to gather all of the PTF cover letters and insert them into a table.

This is the CL program I created, I called it PTFLETPGM:

01 PGM 02 SBMJOB CMD(RUNSQLSTM SRCFILE(MYLIB/DEVSRC) + 03 SRCMBR(PTFLETSQL) + 04 COMMIT(*NONE) + 05 MARGINS(*SRCFILE)) + 06 JOB(PTF_LETTER) + 07 JOBQ(QBATCH) 08 ENDPGM

Lines 2 – 7: All the program performs is a submit job command of the Run SQL statement command, RUNSQLSTM. The source member PTFLETSQL is found in the source file DEVSRC, which is in the library MYLIB. My thought was why should I create a program just with the SQL statement within it, when the RUNSQLSTM would do what was needed.

01 CREATE OR REPLACE TABLE MYLIB.PTFLETTERS 02 (PTF_ID,LINE,LETTER_LINE,PTF_STATUS_TIMESTAMP) AS 03 (WITH T0 (PTF_ID,TSTAMP) AS 04 (SELECT PTF_IDENTIFIER,PTF_STATUS_TIMESTAMP 05 FROM QSYS2.PTF_INFO 06 WHERE PTF_COVER_LETTER = 'YES' 07 AND PTF_LOADED_STATUS = 'PERMANENTLY APPLIED' 08 AND PTF_STATUS_TIMESTAMP BETWEEN '2023-01-01-00.00.00.000000' AND '2024-01-01-00.00.00.000000' 09 ORDER BY PTF_STATUS_TIMESTAMP) 10 SELECT T0.PTF_ID,A.*,T0.TSTAMP 11 FROM T0, TABLE(SYSTOOLS.PTF_COVER_LETTER(PTF_ID)) A 12 ORDER BY T0.PTF_ID,A.LINE_NUMBER) 13 WITH DATA 14 ON REPLACE DELETE ROWS ;

Line 1: I am going to create this table in my library "on the fly", based upon the SQL statement that follows. I have used the CREATE OR REPLACE TABLE as if I need to rerun this for another date range I don't have to delete the existing table, rather this SQL statement would replace it.

Line 2: I want to give the table's columns my own names, rather than use those from PTF_INFO and PTF_COVER_LETTER.

Lines 3 – 12: This is the same as the CTE statement I used previously.

Line 13: I want output data, therefore, I need this line so the table is built with data within it.

Line 14: Only need this as I have used the CREATE OR REPLACE TABLE, this is so the create statement knows what to do with the existing data when it replaces this table.

I am not going to show you the data as it looks like the output from the first CTE statement.

I did use the following statement to see how much data I had:

01 SELECT COUNT(*) FROM MYLIB.PTFLETTERS

The count of the number of rows in the output table is:

00001 ------ 60509

With this file I, or someone else, can use SQL, or even Query, to find the PTFs the auditors want.

 

You can learn more about the SQL PTF_COVER_LETTER table function from the IBM website here.

 

This article was written for IBM i 7.5 TR3 and 7.4 TR9.