Too many spool files: Which outqs, users, and the oldest

At work we monitor the percentage of ASP used to make sure we do not cross the threshold into "critical storage". Recently the amount of ASP has be slowly climbing, and I was part of a trio asked to determine what could be done to reduce the percentage. One looked for unapplied PTFs, another for exceedingly large files, and I was told to look at "everything else".

I know that one long neglected aspect, that is often overlooked, are the number, size, and age of the spool files on this IBM i partition. There are some spool files that need to keep, certain financial period end reports, but others, compile listing, job logs, do not.

First thing is to determine the size of the problem. Fortunately there is a SQL View that allows me to do this easily, OUTPUT_QUEUE_ENTRIES. I can just run the following statement and have the total number of spool files and total size returned to me:

01 SELECT COUNT(*) AS "Total splf", 02 SUM(SIZE) AS "Total size" 03 FROM QSYS2.OUTPUT_QUEUE_ENTRIES

Line 1: The COUNT, as you would assume, gives the count of rows (= spool files) in the View.

Line 2: SUM totals the size column for the rows (= spool files).

The results shocked me:

Total splf Total size ---------- ---------- 346,914 57,605,628

Note: From this point on all of the results are not from my work IBM i partition. They are taken from the partition I use to develop the code used in these posts: DEV730.RZKH.DE

I am going to generate three tables to list the information from OUTPUT_QUEUE_ENTRIES in different ways:

1. Output queues with the number of spool files in them
2. The number of spool files belonging to each user
3. All spool files that are more than one year old

With 346,914 spool files generating these tables is going to take a long time, therefore, I created three CL program, one for each table, and one additional CL program to the others to batch.

Let me start with the program to submit the others to batch:

01 PGM 02 DCL VAR(&JOBQ) TYPE(*CHAR) LEN(10) VALUE('QSPL') 03 SBMJOB CMD(CALL PGM(SPLF001)) JOB(SPLF_OUTQ) JOBQ(&JOBQ) 04 SBMJOB CMD(CALL PGM(SPLF002)) JOB(SPLF_USER) JOBQ(&JOBQ) 05 SBMJOB CMD(CALL PGM(SPLF003)) JOB(SPLF_YEAR) JOBQ(&JOBQ) 06 ENDPGM

I think this program does not need any description as it is so simple. Except for line 2.

Line 2: I wanted to submit these jobs to a job queue that allows more than one job to process at a time. At work I used the QPGMR at it allows five jobs to be run at the same time. On DEV730.RZKH.DE the QPGMR subsystem was not active, therefore, I used the QSPL job queue, which will run these jobs in the QSPL subsystem.

 

SPLF001: Spool files by output queue

01 PGM 02 RUNSQL SQL('CREATE TABLE QTEMP.SPLF001T + 03 (OUTQLIB,OUTQ,COUNT,SIZE) AS + 04 (SELECT OUTQLIB,OUTQ,COUNT(*),SUM(SIZE) + 05 FROM QSYS2.OUTPUT_QUEUE_ENTRIES + 06 GROUP BY OUTQLIB,OUTQ + 07 ORDER BY 3 DESC,1,2) + 08 WITH DATA') + COMMIT(*NC) NAMING(*SQL) 09 RUNSQL SQL('LABEL ON COLUMN QTEMP.SPLF001T + 10 (OUTQLIB IS ''Outq library'',+ 11 OUTQ IS ''Outq'',+ 12 COUNT IS ''No. spool files'',+ 13 SIZE IS ''Total size'')') + COMMIT(*NC) NAMING(*SQL) 14 ENDPGM

By using this SQL View I can generate this output file in just two statements. I could have done this in a SQLRPGLE program, but decided to do it in a CL program using the RUNSQL command.

Line 2: In past posts I have shown this way to create a SQL table without needing source file member.

Line 3: As this table contains columns that do not exist in the file used, line 5, I need to give a "column list", i.e. list of names for the columns that this table will contain.

Line 4: List of the columns selected from the SQL View.

Line 5: The source of the data.

Line 6: Using the GROUP BY allows me to group all of the rows for a particular output queue together into a summary row.

Line 7: I am being a bit lazy here. Rather than list the columns to order the results from I am using their relative position in the results. This means that the sort order is by: Count of spool files - descending, output queue library, output queue name.

Line 8: I need the WITH DATA so that the results are inserted into this new table for me.

Line 9 – 13: This is giving the columns the equivalent of column headings, which will be shown when the contains of the table is displayed.

The results show the output queue with the largest number of spool files first.

Outq Outq No. spool Total library files size -------- ---------- --------- ------- QUSRSYS QEZJOBLOG 1,071 100,668 QGPL QPRINT 49 2,628 QGPL GRPRAMON 36 1,088 MYLIB MYOUTQ 10 352

 

SPLF002: Spool files by user

This is the "name and shame" section. I want to list all the users and how many spool files each one has. This way I can talk to the worse offenders to try to understand why they fell they must have all their spool files.

This program looks very similar to the last. Two RUNSQL commands, the first to build the table, and the second to give the columns within descriptive headings.

01 PGM 02 RUNSQL SQL('CREATE TABLE QTEMP.SPLF002T + 03 (USER_NAME,COUNT,SIZE) AS + 04 (SELECT USER_NAME,COUNT(*),SUM(SIZE) + 05 FROM QSYS2.OUTPUT_QUEUE_ENTRIES + 06 GROUP BY USER_NAME + 07 ORDER BY 2 DESC) + 08 WITH DATA') + COMMIT(*NC) NAMING(*SQL) 09 RUNSQL SQL('LABEL ON COLUMN QTEMP.SPLF002T + 10 (USER_NAME IS ''User'',+ 11 COUNT IS ''No. spool files'',+ 12 SIZE IS ''Total size'')') + COMMIT(*NC) NAMING(*SQL) 13 ENDPGM

Line 3: This table will only have three columns: User, number of spool files belonging to the user, size of those spool files.

Lines 4 – 7: The statement is very similar to the last example. The results are grouped by User profile, and ordered by the number of spool files the use has.

The results show who the worse spool file "hogs" are:

User No. spool Total files size ---------- --------- --------- QSECOFR 1,021 99,476 QSYS 53 1,484 M******** 23 692 SIMON 13 484 K******** 11 876 QTCP 10 440 R******** 8 384 D******** 8 248 H******** 7 220 QUSER 5 220 L******** 5 148 T******** 2 64

 

SPLF003: Spool files that are more than a year old

Unlike the other two tables, which are summaries, I want the detail in this table. I chose to list all the spool files that are over a year old, but this could really be any time period.

01 PGM 02 RUNSQL SQL('CREATE TABLE QTEMP.SPLF003T + 03 (CRTDATE,OUTQLIB,OUTQ,SPOOLNAME,STATUS,PAGES,+ 04 SIZE,JOB_NAME,FILENUM) AS + 05 (SELECT DATE(CREATED),OUTQLIB,OUTQ,SPOOLNAME,+ 06 STATUS,PAGES,SIZE,JOB_NAME,FILENUM + 07 FROM QSYS2.OUTPUT_QUEUE_ENTRIES + 08 WHERE CREATED < (CURRENT_TIMESTAMP - 1 YEAR) + 09 ORDER BY 1,2,3,4) + 10 WITH DATA') + COMMIT(*NC) NAMING(*SQL) 11 RUNSQL SQL('LABEL ON COLUMN QTEMP.SPLF003T + 12 (CRTDATE IS ''Date created'',+ 13 OUTQLIB IS ''Outq lib'',+ 14 OUTQ IS ''Outq'',+ 15 SPOOLNAME IS ''Splf name'',+ 16 STATUS IS ''Splf sts'',+ 17 PAGES IS ''Pages'',+ 18 SIZE IS ''Size'',+ 19 JOB_NAME IS ''Job name'',+ 20 FILENUM IS ''Splf File#'')') + COMMIT(*NC) NAMING(*SQL) 21 ENDPGM

I wanted to be able to use this table in a program where I could fetch rows from the table and then delete the spool file. Therefore, this table contains more columns than both of the previous two examples.

Lines 3 and 4: I want the following columns:

1. Date the spool file was created
2. Output queue library
3. Output queue
4. Spool file name
5. Spool file status
6. Number of pages
7. Size
8. Full job name
9. Spool file number

Line 5: The column CREATED is a timestamp. I did not want a timestamp in my results as I do not care what time a spool file was created, just the date. Here I convert the timestamp to a date by using DATE.

Line 8: In this example I only want all spool files that are younger than a year ago. I could change the date to months or days by replacing line 8 with either of these two lines:

08a WHERE CREATED < (CURRENT_TIMESTAMP - 2 MONTHS) + 08b WHERE CREATED < (CURRENT_TIMESTAMP - 10 DAYS) +

The results look like:

Date Outq Outq Splf Splf Pages Size Job name Splf created lib name sts File# ---------- ---- ------ -------- ----- ----- ---- ----------------------- ----- 2018-02-28 QGPL QPRINT QSYSPRT READY 1 28 263871/QPGMR/QPADEV0002 1 2018-03-11 QGPL QPRINT QPDSPJOB READY 7 44 286281/QTCP/QTVTELNET 3 2018-03-12 QGPL QPRINT QPDSPJOB READY 7 44 287609/QUSER/QRWTSRVR 3 2018-03-17 QGPL QPRINT QPDSPJOB READY 7 44 296444/QTCP/QTVTELNET 3 2018-03-18 QGPL QPRINT QPDSPJOB READY 7 44 300675/QTCP/QTVTELNET 3 2018-04-03 QGPL QPRINT QPDSPJOB READY 7 44 334146/QTCP/QTVTELNET 3 2018-04-16 QGPL QPRINT QPDSPJOB READY 7 44 361967/QTCP/QTVTELNET 3

On the work IBM i partition the oldest spool file was a compile listing from October 14, 2005! The programmer who performed this compile is still with the company and claims he needs all of his compile listings as a record, for the auditors, of what he changed. In my humble opinion this is a ridiculous reason for keeping a spool file for almost 14 years.

I will be running these reports on a regular basis so I can work to reduce that excessive number of spool files down to a more reasonable number.

For those of you curious how we monitor the percentage ASP used I built a program that checks the SYSTEM_ASP_USED column in the SYSTEM_STATUS_INFO view. If the percentage exceeds a fixed number emails are sent to all IT managers alerting them.

 

This article was written for IBM i 7.3, and should work for some earlier releases too.