SYSPROCPLAN

The sysprocplan system catalog table describes the query-execution plans and dependency lists for data-manipulation statements within each routine. Because different parts of a routine plan can be created on different dates, this table can contain multiple rows for each routine.
Table 1. SYSPROCPLAN table column descriptions
Column Type Explanation
procid INTEGER Identifying code for the routine
planid INTEGER Identifying code for the plan
datakey CHAR(1) Type of information stored in data column:
  • D = Dependency list
  • I = Information record
  • Q = Execution plan
seqno INTEGER Line number within the plan
created DATE Date when plan was created
datasize INTEGER Size (in bytes) of the list or plan
data CHAR(256) Encoded (compiled) list or plan

Before a routine is run, its dependency list in the data column is examined. If the major version number of a table accessed by the plan has changed, or if any object that the routine uses has been modified since the plan was optimized (for example, if an index has been dropped), then the plan is optimized again. When datakey is I, the data column stores information about UPDATE STATISTICS and PDQPRIORITY.

It is possible to delete all the plans for a given routine by using the DELETE statement on sysprocplan. When the routine is subsequently executed, new plans are automatically generated and recorded in sysprocplan. The UPDATE STATISTICS FOR PROCEDURE statement also updates this table.

A composite index on the procid, planid, datakey, and seqno columns allows only unique values.