Wednesday, October 30, 2019

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

spool file reporting using output_queue_entires sql view

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.

9 comments:

  1. I strongly recommend using the OUTPUT_QUEUE_ENTRIES_BASIC version instead. The performance of the OUTPUT_QUEUE_ENTRIES version is atrocious on systems with tens to hundreds of thousand spooled files.

    ReplyDelete
  2. Replies
    1. From the OUTPUT_QUEUE_ENTRIES view the size is in kilobytes.

      Delete
  3. When I tried to run queries over OUTPUT_QUEUE_ENTRIES on my system, they took a significant amount of time (>2 minutes) to complete. Not practical for use in a program to regularly check how many spool files it has generated (a program that is triggered by a message queue and can process a thousand messages before ending, each generating a dozen or more spool files - yes, it should be rewritten). Is there a faster or better way to obtain the current number a spool files a job has created?

    ReplyDelete
  4. Thanks for this. Though my example queries were still running 45 seconds or so (down from 2 minutes with the full logical).

    ReplyDelete
    Replies
    1. If you have that many spool files it is going to take a long time to retrieve the results. This I would try are:
      1. Use OUTPUT_QUEUE_ENTRIES_BASIC, as it returns less columns it is faster.
      2. Check your index advisor. I assume that the job got faster as the Db2 engine built a MTI, which is deleted at IPL. A new index could help.

      Delete

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.