Start of change

OPT1ROWBLOCKSORT in macro DSN6SPRM

Specifies whether DB2® explicitly blocks sort operations when the OPTIMIZE FOR 1 ROW clause is specified on a query. Valid values are ENABLE or DISABLE.

ENABLE means that when OPTIMIZE FOR 1 ROW is specified, DB2 chooses access paths that avoid sorts whenever such a path is available. For example, DB2 must choose between two index access paths. One index is very selective and filters two rows, but it requires a sort. The other index does not filter any rows, but it does not require a sort. If the OPT1ROWBLOCKSORT subsystem parameter is set to ENABLE, DB2 chooses the path that does not require a sort.

DISABLE means that when OPTIMIZE FOR 1 ROW is specified, DB2 avoids access paths that involve sorts. If an access path that avoids a sort exists, it is possible, although unlikely, that an access path that involves a sort is chosen instead. This behavior is used in DB2 9 and earlier releases.

The default value of OPT1ROWBLOCKSORT is DISABLE.

End of change