SQL Programming Manual for Pascal

Program Compilation and Execution
HP NonStop SQL Programming Manual for Pascal—528614-001
5-23
Changes to Database Objects
Changes to Database Objects
When changes are made to database objects (such as tables, views, and indexes), a
program file that uses these objects becomes invalid. Altering the database object
requires a new execution plan for the program file. These database changes are:
•
Executing an UPDATE STATISTICS statement with the RECOMPILE option for a
table
•
Dropping a table or view
•
Dropping an index or constraint on a table
•
Adding an index, constraint, column, or partition to a table
However, if the NO INVALIDATE option of the CREATE INDEX statement is
specified when an index is added, then the program file remains valid.
When a program file is invalidated by one of the operations just listed, the SQL catalog
manager also resets the VALID flag for the program in the PROGRAMS catalog.
Other Changes
Not all changes to the program file or database cause the program file to become
invalid. The database changes that do not invalidate a program file are:
•
Renaming a program file
•
Altering the security or owner of the program file or a database object used by the
program
•
Creating a new view on a table
•
Altering the file attributes including the AUDIT flag
•
Adding or dropping comments to a table or view
•
Executing an UPDATE STATISTICS statement with the NORECOMPILE option for
a table used by the program
Changing a DEFINE does not invalidate a program file; however, automatic
recompilation might be necessary if the DEFINE is different from the one that was used
during the original compilation.
Sometimes, however, either the program file or the catalog in which the program is
registered is not available to have the VALID flag reset. For example, a table is altered
on system \SYSTEMA in a network when system \SYSTEMB is unavailable, but a
program on \SYSTEMB uses the altered table on \SYSTEMA. In this case, the
program on \SYSTEMB is not marked as invalid in the file label or in the catalog.
Therefore, an invalid program file is sometimes marked as SQL valid.
Note. These last two list items invalidate a program regardless of its use of the particular
index, column, or partition. A program’s dependency on an affected table, as recorded in the
USAGES table, causes invalidation.