SQL/MP Programming Manual for C

Program Invalidation and Automatic SQL
Recompilation
HP NonStop SQL/MP Programming Manual for C—429847-008
8-4
File-Label and Catalog Inconsistencies
File-Label and Catalog Inconsistencies
Because NonStop SQL/MP records SQL validity in both the program’s file label and in
the PROGRAMS catalog table, inconsistencies can occur. An invalid program file is
sometimes recorded as valid in the catalog, or a valid program file is recorded as
invalid in the catalog. Consider these situations:

A program file is not accessible to the SQL catalog manager.
A DDL operation alters an SQL object referenced by a program file. The SQL
catalog manager marks the program as invalid in the PROGRAMS table, but then
finds that the file is not accessible. The invalid program file remains marked as
valid in its file label. At run time, however, the SQL executor performs the
timestamp check for the referenced SQL object. When the timestamp check fails,
the SQL executor invokes the automatic recompilation of the program.

An SQL compiler (SQLCOMP) process abends.
An event such as a CPU failure causes an SQLCOMP process to abend after it
has generated a program file, marked the program file label as valid, and
registered the program in the PROGRAMS table. TMF backs out the changes to
the PROGRAMS table but not to the program’s file label, because the file label is
not audited. Therefore, a seemingly valid SQL program exists on disk, but an entry
for the program does not exist in the catalog.
You can sometimes recover from this condition by running SQLCOMP again to
reenter the information in the catalog. However, you might first need to use the
CLEANUP or GOAWAY utility to remove the invalid program file.

The SQL catalog manager (SQLCAT) process abends.
A DDL operation (described in Changes to Referenced SQL Objects on page 8-2)
causes a program file to be marked as invalid both in the PROGRAMS table and in
the program’s file label. Then an event such as a CPU failure causes the SQLCAT
process to abend. TMF backs out the changes to the PROGRAMS table but not to
the program’s file label, because the file label is not audited. The valid SQL
program file remains marked as invalid. To recover, you must reexecute the
original DDL operation.
Preventing Program Invalidation
Compiling a program with the CHECK INOPERABLE PLANS option can prevent
certain DDL operations from invalidating the program file. These DDL operations do
not invalidate a program compiled with the CHECK INOPERABLE PLANS option if the
similarity check is also enabled for each referenced object:

ALTER TABLE...ADD PARTITION statement

ALTER TABLE...ADD COLUMN statement (for more information, including
restrictions, see ALTER TABLE ... ADD COLUMN Statement and the Similarity
Check on page 8-13)