EXTDIRECTIVES session environment option

You can use the EXTDIRECTIVES environment option of the SET ENVIRONMENT statement to enable or disable external optimizer directives during the current session.

The EXTDIRECTIVES session environment option has this syntax:

EXTDIRECTIVES environment option

{ EXTDIRECTIVES } { OFF | '0' | ON | '1' | DEFAULT }

Usage

The setting that you specify for EXTDIRECTIVES session environment option can override the settings of both the IFX_EXTDIRECTIVES environment variable and of the EXT_DIRECTIVES configuration parameter in the ONCONFIG file for enabling or disabling external optimizer directives in the current session. Other user sessions are not affected.

To set the EXTDIRECTIVES session environment variable, specify one of these values:

'1' or ON: Enables external optimizer directives. During the current session, this setting overrides whatever values are set in the client-side IFX_EXTDIRECTIVES environment variable or in the EXT_DIRECTIVES configuration parameter.
'0' or OFF: Disables external optimizer directives. During the current session, this setting overrides whatever values are set in the client-side IFX_EXTDIRECTIVES environment variable or in the EXT_DIRECTIVES configuration parameter.
DEFAULT: Enables during the current session a default value that depends on the settings of the client-side IFX_EXTDIRECTIVES environment variable, if that is set, and on the EXT_DIRECTIVES configuration parameter, if that is set, as described immediately below in this topic.

The DEFAULT setting

If you or the sysdbopen routine used the SET ENVIRONMENT EXTDIRECTIVES statement earlier in the same session to enable or to disable external optimizer directives, the SET ENVIRONMENT EXTDIRECTIVES DEFAULT statement restores the external optimizer directives behavior of the database server to its default state.

After the DEFAULT setting takes effect for the session, whether the query execution optimizer is influenced by external directives depends on the settings of the EXT_DIRECTIVES configuration parameter and of the client-side IFX_EXTDIRECTIVES environment variable.

When the EXTDIRECTIVES option of the SET ENVIRONMENT statement is set to DEFAULT, and EXT_DIRECTIVES and IFX_EXTDIRECTIVES are both enabled, then external optimizer directives are enabled during the current session.
When EXTDIRECTIVES is set to DEFAULT, and EXT_DIRECTIVES and IFX_EXTDIRECTIVES are both disabled, then external optimizer directives are disabled during the current session.
If EXTDIRECTIVES is set to DEFAULT, but EXT_DIRECTIVES and IFX_EXTDIRECTIVES have conflicting settings, so that one of them is set to enabled, but the other is set to disabled, then the database server behaves according to these rules:
- if EXT_DIRECTIVES is set to 0 (or if it has no setting), then by default, external directives are disabled, even if IFX_EXTDIRECTIVES is enabled.
- if EXT_DIRECTIVES is set to 1, external directives are disabled if IFX_EXTDIRECTIVES is set to disabled.
- if EXT_DIRECTIVES is set to 1, external directives are enabled if IFX_EXTDIRECTIVES is set to enabled.
- if EXT_DIRECTIVES is set to 2), then external directives are enabled, regardless of the IFX_EXTDIRECTIVES setting.

If no SET ENVIRONMENT EXTDIRECTIVES statement was previously issued in the current session, it is generally not necessary to run the SET ENVIRONMENT EXTDIRECTIVES DEFAULT statement. Unless the EXT_DIRECTIVES configuration parameter has been reset since the session began, the database server is already in its default state for processing external optimizer directives.

Examples of enabling or disabling external directives

The following statement enables external directives in subsequent queries executed in the current session:

SET ENVIRONMENT EXTDIRECTIVES "1";

The following statement has the same effect:

SET ENVIRONMENT EXTDIRECTIVES ON;

In both cases, the database server ignores the EXT_DIRECTIVES and IFX_EXTDIRECTIVES settings, but those settings might affect queries in other concurrent sessions,

Both of the following statements disable external directives in subsequent queries in the current session:

SET ENVIRONMENT EXTDIRECTIVES OFF;

SET ENVIRONMENT EXTDIRECTIVES "0";

The following statement allows processing of external directives in subsequent queries of the current session to be determined by the EXT_DIRECTIVES and IFX_EXTDIRECTIVES settings, as described earlier in this topic:

SET ENVIRONMENT EXTDIRECTIVES DEFAULT;

SAVE EXTERNAL DIRECTIVES statement for a table that identifies the DEFAULT behavior of the database server for external optimizer directives for all possible combinations of EXT_DIRECTIVES configuration parameter settings and IFX_EXTDIRECTIVES environment variable settings.

Related information about external optimizer directives

For information on how to define external optimizer directives and how to save them in the sysdirectives table of the system catalog, see the same SAVE EXTERNAL DIRECTIVES statement topic. For more information about the EXT_DIRECTIVES configuration parameter and the effects of its settings, see the HCL Informix® Administrator's Reference.

For more information about the IFX_EXTDIRECTIVES environment variable, see the Informix® Guide to SQL: Reference, which also describes how the settings of the EXT_DIRECTIVES configuration parameter and of the IFX_EXTDIRECTIVES environment variable can jointly determine whether access to external directives is enabled or disabled for the query optimizer.