SQL Programming Manual for TAL

Program Compilation and Execution
HP NonStop SQL Programming Manual for TAL—527887-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
•
Adding an index to a table that was created by a CREATE INDEX statement with
the INVALIDATE option specified
•
Dropping an index or constraint on a table
•
Adding a constraint, column, or partition to a table
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 a program file or an object the program uses
•
Altering the file attributes including the AUDIT flag
•
Creating a new view on a table
•
Adding an index to a table that was created by a CREATE INDEX statement with
the NO INVALIDATE option specified
•
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 TACL 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.
Note. These last two 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.