Keyed Retrieval From HOLD Files

How to:

Control Keyed Retrieval for a HOLD File

Keyed retrieval is supported with any single-segment SUFFIX=FIX data source or HOLD file that is sorted based on the key. Keyed retrieval can reduce the IOs incurred in reading extract files, by using the SEGTYPE parameter in the Master File to identify which fields comprise the logical key for sequential files. When FIXRETRIEVE is:

ON, the retrieval process stops when an equality or range test on the key holds true.
OFF, all of the records from the sequential file are read and screening conditions are applied when creating the final report.

The ON TABLE HOLD command enables you to read one of the many supported data sources and create extract files. You can then join these fixed-format sequential files to other data sources to narrow your view of the data. The concept of a logical key in a fixed-format file enables qualified keyed searches for all records that match IF/WHERE tests for the first n KEY fields identified by the SEGTYPE attribute. Retrieval stops when the screening test detects values greater than those specified in the IF/WHERE test.

When a Master File is created for the extract file, a SEGTYPE of either Sn or SHn is added, based on the BY fields in the request. For example, PRINT field BY field creates a HOLD Master File with SEGTYPE=S1. Using BY HIGHEST field creates a Master with SEGTYPE=SH1.

Syntax: How to Control Keyed Retrieval for a HOLD File

SET FIXRET[RIEVE] = {ON|OFF}

where:

ON: Enables keyed retrieval. ON is the default setting.
OFF: Disables keyed retrieval.

Example: Master File for Keyed Retrieval From a HOLD File

The following Master File describes a fixed-format sequential file with sorted values of SEQ_NO, in ascending order from 1 to 100,000.

FILE=SORTED,SUFFIX=FIX,$
SEGNAME=ONE,SEGTYPE=S1,$
 FIELD=MYKEY,MK,I8,I8,$
 FIELD=MFIELD,MF,A10,A10,$
 
TABLE FILE SORTED 
 PRINT MFIELD
 WHERE MYKEY EQ 100
END

In this instance, with FIXRETRIEVE=ON, retrieval stops when MYKEY reaches 101, vastly reducing the potential number of IOs, as only 101 records are read out of a possible 100,000.

Example: Selection Criteria for Keyed Retrieval From an Extract File

Selection criteria that include lists of equality values use keyed retrieval. For example,

{IF|WHERE} MYKEY EQ x OR y OR z

IF and WHERE tests can also include range tests. For example,

{IF|WHERE} MYKEY IS-FROM x TO y

The maximum number of vertical (BY) sort fields remains 32.

In using this feature, keep in mind that when adding unsorted records to a sorted HOLD file, records that are out of sequence are not retrieved. For example, suppose that a sorted file contains the following three records:

Key

1 1200

2 2340

3 4875

and you add the following record at the bottom of the file:

1 1620

With FIXRETRIEVE=ON, the new record with a key value of 1 is omitted, as retrieval stops as soon as a key value of 2 is encountered.