DSQSPILL (acquiring extra storage)

Parameter name

DSQSPILL

Short form

L

Valid values

YES or NO

Default

YES

Because large amounts of report data in storage might affect the operation of other programs, QMF lets you allocate a spill file.

A spill file can improve performance in an interactive QMF session. Buffers in memory can store data so that QMF does not need to return to the database for multiple copies of the same data. Data the user needs to view multiple times does not need to be retrieved from the database several times; the spill file can be used to store it.

The spill file is activated automatically unless you specify NO:

DSQQMFn L=NO

Data is written to the spill file until:

• You use the RESET DATA command to reset the data object
• You replace the data object by running another query
• Your query has finished (all rows requested have been retrieved) and the data object is complete
• Your defined storage for the spill file (DFHTEMP in CICS, DSQSPILL is full

Allocating a spill file for non-CICS users

You can allocate a spill file through a FILEDEF statement or through a DD statement in the user's logon procedure, JCL, or CLIST. An example of this appears in the sample procedure, where the spill file is allocated through the DD statement, DSQSPILL. The statement looks like this:

//DSQSPILL  DD DSN=&&SPILL,DISP=(NEW,DELETE),
//             UNIT=SYSVIO,SPACE=(TRK,(1,9),RLSE),
//             DCB=(RECFM=F,LRECL=4096,BLKSIZE=4096)

The statement:

• Allocates the spill file as a temporary data set, unique to the user's session
• Allocates the spill file to virtual I/O (UNIT=SYSVIO). You can allocate the spill file to other DASD storage instead.
• Specifies the DSQSPILL file with fixed-length records, one record for each block. The records must always be unblocked. (A block is the size of an OS/390 page: 4096 bytes.)

The statement's SPACE operand can minimize spill file storage requirements during a session:

• The small primary extent keeps the space held by the spill file to a single track during sessions when a spill file is not needed.
• The much larger secondary extents are used only when a spill file is required.
• The RLSE keyword lets QMF release all secondary extents when the user's DATA object is reset. This happens, for example, when the user begins a new report.

To allocate a spill file in a CLIST, use the following example:

ATTR SPILL RECFM(F) LRECL(4096) BLKSIZE(4096)
ALLOC FILE(DSQSPILL) UNIT(SYSVIO) SPACE(1,19) RELEASE + 
  NEW DELETE USING(SPILL)

If the user waits to do this until a report is being generated, the spill file is not used for that report. The spill file is used during the session only when the underlying DATA object has been replaced (for example, through a DISPLAY command).

Estimating the space required for a spill file

If the data written to the spill file goes over the set limit (becoming full or unusable), QMF does not use the data from the spill file, but instead retrieves it again from the database, using virtual storage to hold it. You can exceed TSO DASD storage.

To accommodate QMF's storage requirements, ensure the TSO DASD storage storage is large enough to hold the individual spill files for all concurrent QMF users, in addition to any other transaction requirements for auxiliary temporary storage.

Use the following procedure to calculate the amount of space required for an individual spill file. Enlarge DFHTEMP storage according to how many individual spill files you will need to accommodate all concurrent users of QMF.

1. Calculate the width (W) of one row of the largest table that can appear in the data object by adding field widths in bytes (use Table 35).
   • All rows of an individual table are the same width, regardless of the data each row contains. A row cannot be wider than 32,768 bytes.
   • Defined columns do not get written to the spill file.
2. If W is 4,096 or less, calculate the number of rows per page (R) using R = 4096/W, and round the result down to the next lowest integer.

   When W is 4,096 or less, QMF fits as many rows as it can into a page, without spanning pages.

3. If W is greater than 4,096, calculate the number of pages per row (P), using P = W/4096, and round up to the next highest integer.

   When W is greater than 4,096, QMF uses the minimum number of pages to hold a row, spanning pages regardless of column boundaries. Each row begins at the start of a page.

4. Calculate the number of pages required for the spill file, according to the value of W:
   • If W is 4,096 or less, calculate the number of pages required for the spill file by dividing the number of rows in the table by R.
   • If W is greater than 4,096, calculate the number of pages required for the spill file by multiplying the number of rows in the table by P.

If a row contains LONG VARCHAR or LONG VARGRAPHIC fields, space is first allotted for all other fields. Then the remaining space is divided by the number of fields, and each LONG VARCHAR or LONG VARGRAPHIC field is truncated to that length.

Table 36 shows a sample calculation for a spill file.

The following sample calculations provide two ways to calculate the spill file space.

When R=4096/540 = 7 multiple rows/buffer:

600,000 rows    1 track    1 cylinder
------------ * --------- * ---------- = 571 cylinders
     7         10 blocks   15 tracks

When R=6000, 2 buffers/row:

6000 rows * 2 blocks/row *  1 track     1 cylinder
                           --------- *  ---------- = 800 cylinders
                           10 blocks    15 tracks

Using a spill file in a noninteractive QMF session

A spill file is most useful for improving performance in an interactive QMF session, when the DSQSMODE parameter is set to I. If you are running QMF noninteractively (the DSQSMODE parameter is set to B), using a spill file can also improve performance when multiple passes of the data are required to produce the report. A spill file might also be necessary to complete the data object, as when a RUN QUERY command is followed by a SAVE DATA command.

Multiple passes of the data are required when:

• You need to print several reports with different formats for the same data.
• You use PCT, CPCT, TCPCT, or TPCT edit codes with the report.
• You print a report that requires QMF to split the pages, because the report is wider than the print width.

When QMF is running in batch, the QMF program parameter DSQSPILL(YES/NO) should be set based on the work to be done. If the job is producing a large data object for printing, then allocating a spill file can have a negative effect on performance. In most cases when running in batch, DSQSPILL=NO is the best choice.

QMF Reference explains each of the QMF forms used to format reports and provides examples of how to use the forms.

Solving some spill file problems

Creating a spill file for your users can solve the user's storage problem, but can cause other problems. You might encounter problems with DASD space or create problems for other users.

Too little space on a DASD volume

If several users with the same QMF logon procedure are experiencing spill file problems, and their common logon procedure allocates all their spill files to a single specific DASD volume, the problems could be due to insufficient space on this volume. If this is the case, you can solve the problem by changing the spill file DD statement in their logon procedure. The new DD statement might make a nonspecific volume reference instead of the current reference to a specific volume.

Creating spill file problems for others

Increasing the spill file's secondary allocation could solve a user's spill file problem, but in doing this, you might create spill file problems for others. If you need to increase the secondary allocation, consider moving the user's spill file to a volume not used for the spill files of others.

A user can unknowingly create spill file problems for others. For example, a user might scroll to the bottom of a large table and overflow the spill file, but do nothing to bring about the incomplete data condition. This would be true if the user failed to issue certain types of commands between the time the table was first displayed and the time it was replaced by another. In the interim, the user's spill file might unnecessarily hold space that others need.

Performance problems

If you are not using conditional formatting or column definitions (which use REXX and have additional performance considerations), the performance you observe is the result of accessing data in the database.

If you have enough storage available to QMF after your data is retrieved the first time, QMF will not need to reaccess the database to obtain rows a second time.

Part of the processing time is devoted to writing the data to DSQSPILL so that it can be fetched later.

Performance is affected by several factors:

• The value of DSQSIROW (initial number of rows to fetch). This primarily affects the initial display of the report only.
• Whether you perform a task that requires multiple passes of the data. (Certain usage codes, such as PCT, require that all the data be read before the first report screen displayed.) This primarily affects the initial display of the report only.
• The amount of memory required to hold one row of data.
• Whether or not if data is fetched from the database the second time when multiple passes are required (not all data fits in memory and DSQSPILL), or from memory and DSQSPILL, or just from virtual memory.
• Whether you are scrolling backward or forward. Successive FORWARD commands usually perform best. BACKWARD commands might require starting over at the start of the answer set. This depends on the amount of memory, how far back you want to scroll, and the complexity of the report.

  For very large answer sets with little memory and insufficient DSQSPILL allocation, the entire answer set could be read from row 1 to the new current row, every time the BACKWARD command is used.

The best performance is attained when there is sufficient memory to hold all data and DSQSPILL is not used.

If you can get the complete answer set into virtual memory before the first display (DSQSIROW is large), the database locks will be released. You will be able to scroll around the displayed report faster. This also slows the display of the first report screen. Releasing the locks could also improve performance for other users.