SAVE EXTERNAL DIRECTIVES statement

Use the SAVE EXTERNAL DIRECTIVES statement to create external optimizer directives for a specified query, and save the directives in the database. These directives are applied automatically to subsequent instances of the same query. This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

SAVE EXTERNAL DIRECTIVES directive { ACTIVE | INACTIVE | TEST ONLY } FORquery
Element Description Restrictions Syntax
directive Optimizer directive valid for query Must be valid for the query and delimited by comment indicators Optimizer Directives
query Text of a valid SELECT statement NULL string is not valid SELECT statement

Usage

SAVE EXTERNAL DIRECTIVES associates one or more optimizer directives with a query, and stores a record of this association in the sysdirectives system catalog table, for subsequent use with queries that match the specified query string. This statement establishes an association between the list of optimizer directives and the text of a query, but it does not execute the specified query.

If the SAVE EXTERNAL DIRECTIVES statement specifies more than one optimizer directive, use the blank character (ASCII 32) as the separator between consecutive directives, as in the following example:
SAVE EXTERNAL DIRECTIVES /*+ USE_INDEX */ /*+ ORDERED */ ACTIVE FOR 
     SELECT * FROM systables;
Unlike in a query, comma ( , ) is not a valid separator in the directives list of SAVE EXTERNAL DIRECTIVES statements.

Only the DBA or user informix can execute SAVE EXTERNAL DIRECTIVES. Optimizer directives that it stores in the database are called external directives.