One of the newer features of the OPNQRYF command on the AS/400 is the use of Dynamic Record Selection. OPNQRYF will automatically use Dynamic Record Selection when it finds an existing access path that it can use. This works fine if you're running over a small file or are selecting at least 90 percent of the file. However, when executed over a large file with a small selection group, the results are anything but good.

We discovered this when we moved from the S/38 to the AS/400. Several of our jobs started taking much more time to execute than on the S/38.

To get around this, we decided to change the OPTIMIZE parameter to *MINWAIT. This tells the optimizer to minimize the total time it takes to read the records, or in other words, create an efficient Open Data Path. In this case, the optimizer will ignore using Dynamic Record Selection. When we did this, our job went from executing in 1 1/2 hours to 10 minutes. In another example -- not at our shop -- a job went from executing in 9 hours to only 45 minutes. This is a dramatic improvement.

The Programming: Database Guide (SC21-9659-2) has scattered bits of information regarding the use of Dynamic Record Selection and performance considerations on the following pages: 6-14, 9-9, 9-32, 9-33, 9-37, and 9-38. Even after reading this information it is not entirely clear what the *MINWAIT will truly do for and to you. In this case, the desired result was achieved by eliminating the Dynamic Record Selection.

This is especially important to remember for those who are moving from the S/38 to the AS/400. Since the S/38 did not use Dynamic Record Selection during OPNQRYF, this change can have an immense impact on the users when you move to the AS/400.