SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
P-29
Operations That Invalidate a Program
An invalid program requires either explicit or automatic recompilation to execute. An
invalid program requires explicit recompilation to revalidate it.
Operations That Invalidate a Program

Copying the program file. If you copy a program file by using the FUP DUPLICATE
command, the original file is unaffected, but the new file is invalid. For more
information, see the SQL/MP Programming and Installation Guide.

Binding the program file. If you explicitly bind a program file by using the Binder
program, the original file is unaffected, but the resulting target file is invalid.

Restoring a program file. If you restore a program file (or an underlying table of a
protection or shorthand view used by the program) by using the RESTORE
program without specifying the SQLCOMPILE ON option, the restored program is
invalid.

Running the accelerator for the program file. If you run the accelerator to optimize
the object code (TNS/R systems only), the program file becomes invalid.
Certain operations on database objects used by a program or on the file that contains
the program cause a program to become invalid. SQL automatically marks programs
as invalid in the PROGRAMS catalog table and in the file label when these operations
occur. SQL also deletes entries for the program in the USAGES catalog table.
Performing any of these operations on an object used by a program invalidates a
registered program:

ALTER TABLE SIMILARITY CHECK ENABLE (or DISABLE)

ALTER VIEW SIMILARITY CHECK ENABLE (or DISABLE)

CLEANUP table, DROP TABLE, or PURGE table

CLEANUP view, DROP VIEW, or PURGE view

DROP CONSTRAINT
Preventing Program Invalidation Caused by DDL Operations
Certain DDL operations on an SQL object cause a program that references the object
to be invalidated. As a result, the SQL catalog manager sets the VALID flag to N in the
PROGRAMS catalog table and in the program’s file label (if the program file is
accessible) and deletes the program’s usages entries in the USAGES table. An invalid
program must be recompiled either explicitly or automatically before it can execute.
This DDL operations do not invalidate a program compiled with the CHECK
INOPERABLE PLANS option if the similarity check is enabled for each referenced
object. 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.)