Selecting and omitting records for i5/OS logical files

Selecting and omitting records for logical files

You can select and omit records for a logical file. This helps exclude records from a file for processing convenience or for security.
The process of selecting and omitting records is based on comparisons identified in position 17 of the DDS form for the logical file, and is similar to a series of comparisons coded in a high-level language program.
For example, in a logical file that contains order detail records, you can specify that the only records you want to use are those in which the quantity ordered is greater than the quantity shipped. All other records are omitted from the access path. The omitted records remain in the physical file but are not retrieved for the logical file. If you are adding records to the physical file, all records are added, but only selected records that match the select/omit criteria can be retrieved through the select/omit access path. In DDS, to specify select or omit, you specify an S (select) or O (omit) in position 17 of the DDS form. You then name the field (in positions 19 through 28) that will be used in the selection or omission process. In positions 45 through 80 you specify the comparison.
Select/omit specifications appear after key specifications (if keys are specified). Records can be selected and omitted by several types of comparisons:
VALUES. The contents of the field are compared to a list of not more than 100 values. If a match is found, the record is selected or omitted. In the following example, a record is selected if one of the values specified in the VALUES keyword is found in the Itmnbr field.
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          S ITMNBR                    VALUES(301542 306902 382101 422109 +
     A                                      431652 486592 502356 556608 590307)
     A
RANGE. The contents of the field are compared to lower and upper limits. If the contents are greater than or equal to the lower limit and less than or equal to the upper limit, the record is selected or omitted. In the following example, all records with a range 301000 through 599999 in the Itmnbr field are selected.
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          S ITMNBR                    RANGE(301000 599999)
     A
CMP. The contents of a field are compared to a value or the contents of another field. Valid comparison codes are EQ, NE, LT, NL, GT, NG, LE, and GE. If the comparison is met, the record is selected or omitted. In the following example, a record is selected if its Itmnbr field is less than or equal to 599999:
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          S ITMNBR                    CMP(LE 599999)
     A
The value for a numeric field for which the CMP, VALUES, or RANGE keyword is specified is aligned based on the decimal positions specified for the field and filled with zeros where necessary. If decimal positions were not specified for the field, the decimal point is placed to the right of the farthest right digit in the value. For example, for a numeric field with length 5 and decimal position 2, the value 1.2 is interpreted as 001.20 and the value 100 is interpreted as 100.00.
The status of a record is determined by evaluating select/omit statements in the sequence you specify them. If a record qualifies for selection or omission, subsequent statements are ignored.
Normally the select and omit comparisons are treated independently of one another; the comparisons are ORed together. That is, if the select or omit comparison is met, the record is either selected or omitted. If the condition is not met, the system proceeds to the next comparison. To connect comparisons together, you leave a space in position 17 of the DDS form. Then, all the comparisons that were connected in this fashion must be met before the record is selected or omitted. That is, the comparisons are ANDed together.
The fewer comparisons, the more efficient the task is. So, when you have several select/omit comparisons, try to specify the one that selects or omits the most records first.
The following examples show ways to code select/omit functions. In these examples, few records exist for which the Rep field is JSMITH. The examples show how to use DDS to select all the records before 1988 for a sales representative named JSMITH in the state of New York. All give the same results with different efficiency. 3 shows the most efficient way.
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
      A          S ST                        CMP(EQ 'NY')       1
      A            REP                       CMP(EQ 'JSMITH')
      A            YEAR                      CMP(LT 88)
      A   
 
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
      A          O YEAR                      CMP(GE 88)         2
      A          S ST                        CMP(EQ 'NY')
      A            REP                       CMP(EQ 'JSMITH')
      A  
 
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
      A          O REP                       CMP(NE 'JSMITH')   3
      A          O ST                        CMP(NE 'NY')
      A          S YEAR                      CMP(LT 88)
      A
1

All records must be compared with all of the select fields St, Rep, and Year before they can be selected or omitted.

2

All records are compared with the Year field. Then, the records before 1988 must be compared with the St and Rep fields.

3

All records are compared with the Rep field. Then, only the few for JSMITH are compared with the St field. Then, the few records that are left are compared to the Year field.

As another example, assume that you want to select the following items:

All records for departments other than Department 12.

Only those records for Department 12 that contain an item number 112505, 428707, or 480100. No other records for Department 12 are to be selected.

If you create the preceding example with a sort sequence table, the select/omit fields are translated according to the sort table before the comparison. For example, with a sort sequence table using shared weightings for uppercase and lowercase, NY and ny are equal.
The following diagram shows the logic of this example.

This example uses the DDS select and omit functions:
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
     A          S DPTNBR                    CMP(NE 12)
     A          S ITMNBR                    VALUES(112505 428707 480100)
     A
It is possible to have an access path with select/omit values and process the file in arrival sequence. For example, a high-level language program can specify that the keyed access path is to be ignored. In this case, every record is read from the file in arrival sequence, but only those records meeting the select/omit values specified in the file are returned to the high-level language program.
A logical file with key fields and select/omit values specified can be processed in arrival sequence or using relative record numbers randomly. Records omitted by the select/omit values are not processed. That is, if an omitted record is requested by relative record number, the record is not returned to the high-level language program. The system does not ensure that any additions or changes through a logical file will allow the record to be accessed again in the same logical file. For example, if the selection values of the logical file specifies only records with an A in Fld1 and the program updates the record with a B in Fld1, the program cannot retrieve the record again using this logical file.
You cannot select or omit based on the values of a floating-point field.
The two kinds of select/omit operations are access path select/omit and dynamic select/omit. The default is access path select/omit. The select/omit specifications themselves are the same in each kind, but the system actually does the work of selecting and omitting records at different times.
You can also use the Open Query File (OPNQRYF) command to select or omit records.

Access path select/omit
With the access path select/omit operation, the access path only contains keys that meet the select/omit values specified for the logical file.

Dynamic select/omit
With the dynamic select/omit operation, the system only returns those records that meet the select/omit values when a program reads records from the file. That is, the actual select/omit processing is done when records are read by a program, rather than when the records are added or changed.

Selecting and omitting logical file records using the Open Query File (OPNQRYF) command
As an alternative to using DDS, you can select and omit records for a logical file by specifying the QRYSLT parameter on the Open Query File (OPNQRYF) command.

Parent topic:
Describing access paths for logical files

Related concepts

DDS concepts