Both the key length and the relative key position are retrieved.
For example, the following lines define a record layout for keypos=10 and keylen=4:
01 REC.
04 HEADER PIC X(10).
04 KEY.
08 KEY1 PIC 99.
08 KEY2 PIC 99.
04 REST PIC X(200).
For optimization to work, include KEY1 in the WHERE clause.
Use key information to speed processing of WHERE clauses, which provides optimization.
Different WHERE clause formats produce different results.
Line 1 SELECT * FROM TEST.VSAM1.TAB WHERE (KEY1 > 50) AND (KEY1 < 90)
(50 < KEY1 < HIGH-HALUES) AND (LOW-VALUES < KEY1 < 90)
The following example generates the same results as the Line 1 example, but with a different elapsed time:
Line 2 SELECT * FROM TEST.VSAM1.TAB WHERE (KEY1 > 50 AND KEY1 < 90)
(KEY1 > LOW-VALUES AND KEY1 < HIGH-HALUES)
This format is still technically optimized, but when the file is processed, the read start is positioned at LOW-VALUES and read until HIGH-VALUES, which is not ideal.
Line 2 is the preferred syntax. This syntax positions the file at record 50 and reads data until KEY1 is greater than or equal to 90.
Optimization is used for key positioning, the data is selected against the WHERE clause to verify that the correct records are filtered.
If you specify a value greater than 0 in the
Skip First Records from File
box in the
KSDS Access Method
dialog box, the SELECT statement is not optimized even if you specify KEY1.