Configuration parameters and environment variables for optimizer directives

You can use the DIRECTIVES configuration parameter to turn on or off all directives that the database server encounters, and you can use the IFX_DIRECTIVES environment variable to override the setting of the DIRECTIVES configuration parameter.

If the DIRECTIVES configuration parameter is set to 1 (the default), the optimizer follows all directives. If the DIRECTIVES configuration parameter is set to 0, the optimizer ignores all directives.

You can override the setting of DIRECTIVES. If the IFX_DIRECTIVES environment variable is set to 1 or ON, the optimizer follows directives for any SQL the client session executes. If IFX_DIRECTIVES is 0 or OFF, the optimizer ignores directives for any SQL in the client session.

Any directives in an SQL statement take precedence over the join plan that the OPTCOMPIND configuration parameter forces. For example, if a query includes the USE_HASH directive and OPTCOMPIND is set to 0 (nested-loop joins preferred over hash joins), the optimizer uses a hash join.