Optimizer directives and SPL routines

Directives operate differently for a query in an SPL routine because a SELECT statement in an SPL routine is not necessarily optimized immediately before the database server executes it.

The optimizer creates a query plan for a SELECT statement in an SPL routine when the database server creates the SPL routine or during the execution of the UPDATE STATISTICS statement that include the FOR FUNCTION, FOR PROCEDURE, or FOR ROUTINE keywords.

The optimizer reads and applies directives at the time that it creates the query plan. Because it stores the query plan in a system catalog table, the SELECT statement is not reoptimized when it is executed. Therefore, settings of IFX_DIRECTIVES and DIRECTIVES affect SELECT statements inside an SPL routine when they are set at any of the following times:
  • Before the CREATE PROCEDURE statement
  • Before the UPDATE STATISTICS FOR ROUTINE statements that cause SQL data-manipulation statements in SPL routines to be optimized
  • During certain circumstances when SELECT statements have variables supplied at runtime