Because sometimes I get the most interesting challenges to play with! On a dark and stormy night, or if we want to be precise a grey and miserable afternoon…
I have a partition with about 4000 QPDZDTALOG spool files dating back over a number of years. For those not familiar, this print file is generated if someone is using the UPDDTA command to manually insert/delete/update data in a physical file. I’m sure most of you use more modern/efficient methods for your data entry but sometimes you just decide “I want that data, in that file, now.”
The spool file will show you information on what was updated/created/deleted, importantly for my purposes today the file is created whether you change any records or not.
I’d like to keep any prints where at least 1 record has been added/deleted/changed but delete any with no changes. One solution would be to sit here and manually check each print to see which ones I want to delete…. Perhaps if I had a desperate lack of anything better to do.
My first useful thought was to use the size of the spool file through interrogating QSYS2.OUTPUT_QUEUE_ENTRIES, once I know the size of a base print then I can select all that match. There are a number of guides out there, personally I found an article from RPGPGM very helpful.
I know the output queue the prints are on, and if nothing has changed then the file will only be 1 page. So, my opening gambit becomes a SQL command such as:
select * from qsys2.output_queue_entries
where outq = ‘ARCSPLF’
and total_pages = 1
This is where I hit a problem, the size of a single page print always reports as 32k. I can’t use it to tell the difference between a single page print, with changes, and a single page print without.
Coming at it from another angle then, I know how many lines are on the print. If no changes have happened then there will be 8 (4 header, 3 totals, end of report line.) So, if I use my SQL to filter down to just the 1 page reports I can then count the lines on each one. If number of lines <> 8 then I want to keep it, otherwise it can go in the bin.
This is where, if the customer was on V7R3 or above, I might consider using SYSTOOLS.SPOOLED_FILE_DATA which is a function doing exactly what it says. You point it at a spool file, and it will spit out the data in that file. The potential advantage is keeping more of the code in SQL rather than mixing and matching with CL.
The customer is on V7R2, so we’ll have to do it with CPYSPLF. In this example I’m going to create a file in QTEMP with the appropriate attributes, then copy my print into it. Once that’s done, we can use RTVMBRD to get the number of records and if 8 then delete it.
In the below example &FILE_NUMB is populated from the FILE_NUMBER column from my original SQL above, I’m then sub stringing JOB_NAME to build my three parameters for the job number/user/name.
CRTPF FILE(QTEMP/TEMPFILE) RCDLEN(133) IGCDTA(*YES)
CPYSPLF FILE(QPDZDTALOG) TOFILE(QTEMP/TEMPFILE) +
RTVMBRD FILE(QTEMP/TEMPFILE) MBR(*FIRST) +
IF COND(&NBRCURRCD *EQ 8) THEN(DO)
DLTSPLF FILE(QPDZDTALOG) +
Depending on how I want to run the program, it might also make sense to include the SQL in the CL? Save me having to manually re-generate the list each time I want to run it.
RUNSQL SQL(‘CREATE OR REPLACE TABLE qtemp.spllist +
(JOB_NAME, FILE_NUMBER) AS (SELECT +
JOB_NAME, FILE_NUMBER FROM +
QSYS2.OUTPUT_QUEUE_ENTRIES where OUTQ = +
”ARCSPLF” and TOTAL_PAGES = 1) WITH +
DATA ON REPLACE DELETE ROWS’) +
I’ve grabbed the two columns I’m interested in and put them into a QTEMP file. I could substring at this stage for JOB_NAME but I prefer to do it with some CHGVAR commands in the CL later. Another point to note here, I’m using a DCLF statement at the start of the CL so I need SPLLIST to exist before I can compile the program.
There are other ways I could try and verify the print as well, perhaps take the 3 total lines and if they add up to 0 then delete it? Count the number of instances of the words ‘Changed’ / ‘Deleted’ / ‘Added’ are on the left-hand side of the print, if 0 then delete. Both seem over complicated for what I’m trying to achieve today, but could be useful if I need to run a similar process over some other prints at a later date.
When I ran this, my 4000 odd prints dropped down to just under 3000. Hardly going to break the bank in terms of disk usage but having 1000 less prints each time I do a system save can’t hurt either!
Is there a more obvious method I missed? If so, feel free to share with the crowd, never hurts to learn something new.