PRF0515E SPILL FILE AND MEMORY ARE FULL using Report Facility

Products

• Report Facility

Issue/Introduction

How is the Report Facility (PRF) Spill file allocated? Is there something in the user profile that affects the spill file allocation?

Resolution

The environment variable for the SPILL quantities carries a calculated value derived from the User Profile. The calculation takes the User Profile value (Primary Extent KBYTES), multiplies by 1024
and then divides by 32760. This is the size of the SPILL records. The calculated value then is the number of SPILL records that are to be allocated.

In this example,
If you look at the IDCAMS audit in the Job output it shows RECORDS(2,2) s o it allocates a file size of 2 records, 64K, plus a secondary of 2 records, 64K * 121 secondary extents for a grand sum
total of 7808K.

Only whole rows will be placed in the SPILL file plus 32 bytes of overhead so the number of query rows that fit in the 32760 byte records is:
TRUNC(32760 / (length of row + 32))

So a 300 byte row will fit about 98 rows to a SPILL record accounting for 765,184 rows before the spill file runs out of space. (This is on top of the SPILL memory allocation which will allow for that
many more rows that are kept in memory.)

Also, the number of secondary extents may be limited by the amount of space on the selected DASD packs.