OPT1ROWBLOCKSORT in macro DSN6SPRM
Specifies whether Db2 explicitly blocks sort operations when the OPTIMIZE FOR 1 ROW clause is specified on a query.
Deprecated subsystem parameter: Non-default settings for OPT1ROWBLOCKSORT are deprecated. Db2 12 supports these settings, but their use is discouraged, and support is removed in Db2 13 or later. For best results, use the default value DISABLE.
| Acceptable values: | DISABLE, ENABLE |
|---|---|
| Default: | DISABLE |
| DSNZPxxx: | DSN6SPRM OPT1ROWBLOCKSORT |
| Security parameter: | No |
- DISABLE
- 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 was always used in DB2® 9 and earlier releases.
- ENABLE
- 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.