SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-30
Preventing Program Invalidation Caused by DDL
Operations

ALTER TABLE...ADD PARTITION statement (including an underlying table of a
protection or shorthand view used by the program)

ALTER TABLE...ADD COLUMN statement (for more information, including
restrictions, see the SQL/MP Installation and Management Guide.)

ALTER TABLE statement to move or split partitions (including a simple move,
one-way split, or two-way split) or change the type of partition array

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

Changing a collation: dropping and then re-creating the collation, renaming a
collation, or changing a DEFINE that points to a collation
The ALTER TABLE... RENAME, ALTER INDEX... RENAME, and ALTER INDEX...
ADD PARTITION statements do not invalidate a program whether or not it was
compiled with the CHECK INOPERABLE PLANS option.
These operations invalidate specific execution plans within a program even when they
do not invalidate the program as a whole. (SQL detects the invalid plans at program
execution time by comparing an object's current redefinition timestamp to its
compile-time redefinition and then performs similarity checks at that time to determine
if the plan requires recompilation.)
Host language compiling, binding, or accelerating a registered SQL program deletes it
from the catalog it is registered in if the new version of the program is written to the
same object file that held the previous version. You must re-SQL-compile the program
to reregister it and make it a valid SQL program.
An unregistered SQL program is technically neither valid nor invalid because SQL
does not maintain information about its validity in the file label of the program file or in
Note. These DDL operations always invalidate a program, even if the program is 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

DROP INDEX statement, if the program contains a plan that references the dropped index