DSQSPILL (acquiring extra storage)

Parameter name

DSQSPILL

Short form

L

Valid values

YES or NO

Default

NO

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.

Set the DSQSPILL parameter to YES to activate the spill file:

QMFn L=YES

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 data in the spill file exceeds the maximum of 32 ,767 rows (each row holds 4 KB of data)

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 CICS storage. In CICS, temporary storage for the spill file is limited to 32,767 buffers that are each 4 KB.

To accommodate QMF's storage requirements, ensure the CICS temporary storage file DFHTEMP 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.
   • 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.

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.

Solving some spill file problems

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.