Index considerations
Indexes may be recovered at IPL time based on the RECOVER parameter of CRTPF, CRTLF, CHGPF, or CHGLF commands. Indexes that are based on a column that has a field procedure have special considerations.
Use of PASE(QSH) and JAVA within field procedures must be avoided if the index keys are built over expressions that contain columns with field procedures or the sparse index criteria references a column with an associated field procedure. If use of PASE or JAVA is required, consider changing indexes to RECOVER(*NO) so that they are not recovered during the IPL process and recovered during an open operation instead.
The following restrictions apply to keys
for both SQL indexes and DDS keyed files.
- If the column has a field procedure, the index key must be a column. No expressions (derivations) are allowed. This includes DDS keywords like SST and CONCAT.
- Sort sequence cannot be applied to the column.
- If the field procedure column is part of a foreign key, the corresponding parent key column must use the same field procedure.
- The WHERE clause of the SQL Create Index or the Select/Omit criteria of a DDS logical file cannot reference a column that has a field procedure.