SQL optimization
If an SPL routine contains SQL statements, at some point the query optimizer evaluates the possible query plans for SQL in the SPL routine and selects the query plan with the lowest cost. The database server puts the selected query plan for each SQL statement in an execution plan for the SPL routine.
When you create an SPL routine with the CREATE PROCEDURE statement, the database server attempts to optimize the SQL statements within the SPL routine at that time. If the tables cannot be examined at compile time (because they do not exist or are not 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. In addition, the database server stores information about the SPL routine (such as procedure name and owner) in the sysprocedures system catalog table and an ASCII version of the SPL routine in the sysprocbody system catalog table.
