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 an SPL routine or during the execution of some versions of the UPDATE STATISTICS statement.
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: