SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-5
Determining Validity of a Program
named COMPFILE. You can edit this file and use it as a command file in an OBEY
command, directing the command interpreter to recompile the programs.
>> VERIFY *.*.* WHERE SQLPROGRAM, SOURCE COMPFILE CLEAR;
...
--- Verifying $VOL1.PPROGS.UEMPLIST
*** WARNING $VOL1.PPROGS.UEMPLIST IS AN INVALID PROGRAM.
--- SQL operation complete.
Querying the PROGRAMS Catalog Table
You can query the catalog tables to verify whether VALID flags are set. You can check
the catalog VALID flags but not the program’s file label. Similar to the VERIFY utility,
these queries might not detect all conditions that can cause programs to be
automatically recompiled.
The PROGRAMS table of the catalog stores information about program validity. You
can query each catalog for invalid programs. If you set up an SQLCI log file, the output
of the query is duplicated in an EDIT file.
This example shows setting a log file and then querying the PROGRAMS table:
>> LOG $SYSTEM.PGMS.INVALID;
>> CATALOG \SYS1.$VOL1.SALES;
>> SELECT * FROM PROGRAMS
+> WHERE VALID = "N" OR
+> AUTOCOMPILE = "Y";
Monitoring Compilations
If you want to be sure that all programs are valid, you should monitor recompilation
with the SQL logging facility. The logging of messages about explicit compilations and
automatic recompilations (described later in this section) is automatically directed to $0
for certain SQL compilation events. Compilations can be initiated by SQLCI commands
(which use dynamic SQL), dynamic SQL statements, explicit SQL compiles, or
automatic SQL recompilation.
You can use the DEFINE =_SQL_CMP_EVENT to redirect the logging to a disk file, or
a terminal, or to disable the logging facility. Logging to $0 is automatic unless you
disable the logging. Exceptionally heavy SQLCI DML activity or compilation activity can
exceed the capacity of $0. For the description of how to set up and use the DEFINE
=_SQL_CMP_EVENT to control the device to which messages are logged, see the
NonStop SQL/MP Reference Manual.
Caution. If a program is marked as invalid in the catalog or is detected as invalid by the
VERIFY utility, you should explicitly SQL compile the program to revalidate the program to
avoid automatic recompilations.
You should not attempt to validate a program file by altering the VALID column in the
PROGRAMS table. Validation information is also stored in the program’s file label, which
cannot be altered with SQL utilities.