The system can select and omit records when using a logical file. This can help you to exclude records in 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 using 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.
| Note: | Select/omit specifications appear after key specifications (if keys are specified). |
Records can be selected and omitted by several types of comparisons:
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
A S ITMNBR VALUES(301542 306902 382101 422109 +
A 431652 486592 502356 556608 590307)
A
|...+....1....+....2....+....3....+....4....+....5....+....6....+....7....+....8
A S ITMNBR RANGE(301000 599999)
A
|...+....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 from 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 simply 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.
In the following 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 (in this example, (3) is the most efficient).
Figure 9. Three Ways to Code Select/Omit Function
|...+....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
As another example, assume that you want to select the following:
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. For details, see DDS Reference .
The following diagram shows the logic included in this example:
The following shows how to code this example using 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.
| Note: | 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.
With access path select/omit, the access path only contains keys that meet the select/omit values specified for the logical file. When you specify key fields for a file, an access path is kept for the file and maintained by the system when you add or update records in the physical file(s) used by the logical file. The only index entries in the access path are those that meet the select/omit values.
With dynamic select/omit, when a program reads records from the file, the system only returns those records that meet the select/omit values. 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. However, the keyed sequence access path contains all the keys, not just keys from selected records. Access paths using dynamic select/omit allow more access path sharing, which can improve performance. For more information about access path sharing, see Using Existing Access Paths.
To specify dynamic select/omit, use the dynamic selection (DYNSLT) keyword. With dynamic select/omit, key fields are not required.
If you have a file that is updated frequently and read infrequently, you may not need to update the access path for select/omit purposes until your program reads the file. In this case, dynamic select/omit might be the correct choice. The following example helps describe this.
You use a code field (A=active, I=inactive), which is changed infrequently, to select/omit records. Your program processes the active records and the majority (over 80%) of the records are active. It can be more efficient to use DYNSLT to dynamically select records at processing time rather than perform access path maintenance when the code field is changed.
Another method of selecting records is using the QRYSLT parameter on the Open Query File (OPNQRYF) command. The open data path created by the OPNQRYF command is like a temporary logical file; that is, it is automatically deleted when it is closed. A logical file, on the other hand, remains in existence until you specifically delete it. For more details about the OPNQRYF command, see Using the Open Query File (OPNQRYF) Command.