Creating and saving external directives

You can define external directives by creating association records that include query optimizer directives, and saving those records in the sysdirectives system catalog table. Association records associate a list of one or more optimizer directives with a specific query text. The database server can apply those optimizer directives to subsequent instances of the same query text.

Use the SAVE EXTERNAL DIRECTIVES statement to create the association record to use for the list of one or more query directives These directives are applied automatically to subsequent instances of the same query.

The following example shows a SAVE EXTERNAL DIRECTIVES statement that registers an association-record in the system catalog as a new row in the sysdirectives table that can be used as a query optimizer directive.

SAVE EXTERNAL DIRECTIVES {+INDEX(t1,i11)} ACTIVE FOR
   SELECT {+INDEX(t1, i2) } c1 FROM t1 WHERE c1=1;

The following data is stored in the association record that the SQL statement above defined:

id             16
query          select {+INDEX(t1, i2) } c1 from t1 where c1=1
directive      INDEX(t1,i11)
directivecode  BYTE value

active         1
hashcode       -589336273

Here {+INDEX(t1,i11)}, the external directive that followed the DIRECTIVES keyword, will be applied to future instances of the specified query, but the inline {+INDEX(t1,i2)} directive will be ignored.

The information in the external directives that immediately follow the DIRECTIVES keyword must be within comment indicators, just as the same directives would appear in SELECT, UPDATE, MERGE, and DELETE statements, except that blank characters, rather than comma ( , ) symbols, are the required separators if the list of external directives includes more than one directive.