General query optimization tips

Here are some tips to help your queries run as fast as possible:

• Create indexes whose leftmost key columns match your selection predicates to help supply the optimizer with selectivity values (key range estimates).
• For join queries, create indexes that match your join columns to help the optimizer determine the average number of matching rows.
• Minimize extraneous mapping by specifying only columns of interest on the query. For example, specify only the columns you need to query on the SQL SELECT statement iinstead of specifying SELECT *. Also, you should specify FOR FETCH ONLY if the columns do not need to be updated.
• If your queries often use arrival sequence, use the RGZPFM (Reorganize Physical File Member) command to remove deleted rows from tables or the CHGPF (Change Physical File) REUSEDLT (*YES) command to reuse deleted rows.

For embedded SQL, consider using the following precompile options:

• Specify ALWCPYDTA(*OPTIMIZE) to allow the query optimizer to create temporary copies of data so better performance can be obtained.
• Use CLOSWLCSR(*ENDJOB) or CLOSQLCSR(*ENDACTGRP) to allow open data paths to remain open for future invocations.
• Specify DLYPRP(*YES) to delay SQL statement validation until an OPEN, EXECUTE, or DESCRIBE statement is run. This option improves performance by eliminating redundant validation.
• Use ALWBLK(*ALLREAD) to allow row blocking for read-only cursors.

For OPNQRYF (Open Query File) queries, consider using the following parameters:

• Use ALWCPYDTA(*OPTIMIZE) to let the query optimizer create temporary copies of data if it can obtain better performance by doing so.
• Use OPTIMIZE(*FIRSTIO) to bias the optimizer to use an existing index instead of creating a temporary index.