Automatic optimization

When you create an SPL routine, the database server attempts to optimize the SQL statements within the routine at that time. If the tables cannot be examined at compile time (they might not exist or might not be available), the creation does not fail. In this case, the database server optimizes the SQL statements the first time that the SPL routine executes. The database server stores the optimized execution plan in the sysprocplan system catalog table for use by other processes.

The database server uses the dependency list to keep track of changes that would cause reoptimization the next time that an SPL routine executes. The database server reoptimizes an SQL statement the next time that an SPL routine executes after one of the following situations:
  • Execution of any data definition language (DDL) statement (such as ALTER TABLE, DROP INDEX, or CREATE INDEX) that might alter the query plan
  • Alteration of a table that is linked to another table with a referential constraint (in either direction)
  • Execution of UPDATE STATISTICS FOR TABLE for any table involved in the query

    The UPDATE STATISTICS FOR TABLE statement changes the version number of the specified table in systables.

The database server updates the sysprocplan system catalog table with the reoptimized execution plan.