Direct access processing in field criteria

Based on unformatted and formatted field selection criteria, there are three ways that File-AID for IMS/ISPF can access primary database root segments during extract processing. Direct access to root segments is performed using qualified GU (Get Unique) DL/I calls. Sequential access is performed using unqualified GN (Get Next) DL/I calls. Direct access using a Secondary Index.

The requirements for direct or sequential database access vary according to the DL/I access methods. File-AID for IMS/ISPF groups DL/I access methods into the following categories to determine a direct access call:

• HIDAM, HISAM, SHISAM, and MSDB
• HDAM and DEDB
• HSAM and SHSAM

See Use of Secondary Indexes for Secondary Index details.

HIDAM, HISAM, SHISAM, and MSDB

One of the following relational operators must be specified for the first position (byte) of the root segment key or first Secondary Index field: BT, EQ, GE, GT, LE, or LT. The following figure is an example of formatted field selection criteria with partial key qualification.

Formatted Criteria Screen

Data Base Access

Indicates direct or sequential access. This field value is based on the segment key entries. The key fields and secondary index fields are noted by a K or S in the last position of the Format column. There are two possible methods of direct access depending on how the key is qualified:

Whole key specified

If all positions of the key are specified with an EQ relational operator, then a fully qualified GU call is used to access the root segment directly.

Partial key specified

If the relational operator BT, GE, GT, LE or LT is specified or not all positions of the key are qualified, the first call is a fully qualified GU call to establish a starting point within the primary database. The remainder of the root calls are sequential GN DL/I calls until the end of the database or the end of a specific range is reached.

Access to the database is sequential using GN DL/I calls in the following situations:

• 
  
  • Key value is not specified.
  • Key value is not specified for the first position of the key.
  • CO, NB, NC, or NE relational operators are specified for the first position of the key.

HDAM and DEDB

All positions of the root segment key must be specified with a relational operator of EQ. The following figure is an example of unformatted field selection criteria with full key qualification.

Unformatted Criteria Screen - Full Key Qualification

Data Base Access

Indicates direct or sequential access. This field value is based on the segment key entries.

If field selection criteria using the EQ relational operator is not specified for all positions of the root key, access to the database is sequential using GN DL/I calls.

HSAM and SHSAM

Access to the root segments for these databases is always sequential whether or not field selection criteria are specified.

Use of Secondary Indexes

If the primary DBD used to drive a File-AID for IMS extract has a secondary index, it may be possible to use the secondary index to drive extract processing. In many cases, use of an index can improve extract performance. This is particularly true when using key file processing with an input file containing secondary index key values. Extracts using a key file process use DIRECT rather than SEQENTIAL database access.

In many cases you may find that applying selection criteria to secondary index key fields will change processing from SEQUENTIAL to DIRECT. When this happens, the secondary index is used to drive extract processing in the same way a primary index is used. In order for this to take effect, the start of the DBD XDFLD must be referenced when specifying selection criteria. The next screen provides an example of what is required for access to be DIRECT when using selection criteria.

Using Selection Criteria on a Secondary Index Field

Suppose the PORDR DBD source contains the following statements:

Suppose the layout associated with segment ORDR020 is defined as:

DBD field PARTKEY spans PART-TYPE and PART-NUMBER. If selection criteria is specified for PART-NUMBER only, database access will be SEQUENTIAL. If selection criteria are specified for PART-TYPE and any other field in the layout, database access for this set of criteria will be DIRECT.

The same consideration applies to unformatted criteria. As long the first unformatted criteria entered starts at an offset that coincides with the start of the PARTKEY field on the ORDR020 segment, database access will be DIRECT. Otherwise it will be SEQUENTIAL.

If additional sets of selection criteria are defined, they must also qualify for DIRECT database access in order for the extract to execute using DIRECT database access.

Selection Criteria Requiring SEQUENTIAL Database Access

When you use secondary indexing and create selection criteria, you may receive a message indicating that the secondary index cannot be used because one or more sets that you have created require SEQUENTIAL database access.

• If you wish to use the secondary index in this case, remove the sets that require SEQUENTIAL access from the selection criteria.
• If, however, you want to use the sets that require SEQUENTIAL access, from the Selection Criteria - Menu Screen, change the Use secondary indexing field to 'N' and you will be able to save the selection criteria you have defined.