SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-4
Determining Validity of a Program
Determining Validity of a Program
A program is invalid if any of these are true:
•
The VALID flag of the program entry in the PROGRAMS table is not set or is set
to N.
•
The VALID flag in the program file label is not set or does not correspond to the
VALID flag in the PROGRAMS entry.
•
The value of RECOMPILETIME in the program file label does not correspond to
the RECOMPILETIME recorded in the PROGRAMS catalog table.
•
The RECOMPILATION timestamp in the program file label represents a time
earlier than any redefinition timestamp of any SQL object on which the program
depends.
There are several ways to verify and maintain valid programs in your application:
•
Use the VERIFY utility through SQLCI to read the PROGRAMS catalog tables and
the program file labels to determine validity.
•
Query the PROGRAMS catalog table directly to find programs marked as invalid.
•
Monitor SQL compilations and automatic recompilations by using the logging
facility.
Using VERIFY to Detect Invalid Programs
You can use VERIFY to check file labels and catalogs for invalid programs. VERIFY
can produce an output EDIT file that contains a list of the invalid programs. You can
edit the invalid warning message for each invalid program written to an EDIT file to
create an OBEY command file to explicitly SQL compile these programs.
VERIFY can detect only those programs actually marked as invalid in the file labels
and catalogs tables. VERIFY cannot detect all the conditions that could cause
automatic recompilation at run time.
VERIFY does not detect the invalid status of programs in these situations:
•
The FORCE option was used.
•
A program was explicitly SQL compiled, but the best query execution plan was not
available at compile time.
In the latter case, the valid flag is set to Y, but certain statements in the program are
invalid and must be automatically recompiled at run time.
To maintain valid programs, use VERIFY in combination with the logging facility that
detects all automatic recompilations. This VERIFY example shows the invalid warning
messages. A VERIFY request generates SQLCOMP commands to recompile any
invalid programs. In this example, these commands are written to a cleared EDIT file