SQL/MP Programming Manual for C

Program Invalidation and Automatic SQL
Recompilation
HP NonStop SQL/MP Programming Manual for C—429847-008
8-5
Automatic SQL Recompilation

ALTER TABLE statement to move or split partitions (including a simple move,
one-way split, or two-way split)

ALTER TABLE...DROP PARTITION statement

ALTER INDEX...DROP PARTITION statement (if the similarity check is enabled for
the base table)

ALTER INDEX statement to move or split index partitions

CREATE INDEX statement

UPDATE STATISTICS...RECOMPILE statement
The program also retains its entries in the USAGES table. These operations, however,
do update the redefinition timestamp of each referenced object in the DDL statement.
The ALTER TABLE...RENAME, ALTER INDEX...RENAME, and ALTER INDEX...ADD
PARTITION statements do not invalidate a program regardless of whether it was
compiled with the CHECK INOPERABLE PLANS option.
Automatic SQL Recompilation
Automatic SQL recompilation is the run-time SQL compilation, invoked by the SQL
executor, of either an entire SQL program or a single static SQL statement in the
program, depending on whether the RECOMPILE or RECOMPILEONDEMAND option
was specified during explicit SQL compilation.
Automatic SQL recompilation validates only the copy of the SQL program or statement
in memory; it does not validate the SQL program file on disk. Only explicit SQL
compilation validates an SQL program file on disk.
Automatic SQL recompilation uses the default volume and catalog settings used for the
explicit SQL compilation and the set of DEFINEs in effect at SQL load time (that is,
when the SQL executor runs the first SQL statement in the program).
Note. These DDL operations always invalidate a program, even if the program was compiled
with the CHECK INOPERABLE PLANS option:

ADD CONSTRAINT statement

DROP CONSTRAINT statement

DROP TABLE statement

DROP VIEW statement

ALTER TABLE or ALTER VIEW statement with the SIMILARITY CHECK clause (For more
information, see Enabling the Similarity Check for Tables and Protection Views
on
page 8-10.)

DROP INDEX statement, if the program contains a plan that references the dropped index