SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-16
Using the CHECK Option
The CHECK option has three forms:
•
INVALID PROGRAM specifies automatic recompilation for all SQL statements in
an invalid program, or a program that refers to changed DEFINEs (if
NORECOMPILE is not specified). This option is the default.
•
INVALID PLANS specifies automatic recompilation for an SQL statement if either
of these conditions occur (and NORECOMPILE is not specified):
°
The statement is invalid.
°
The statement refers to a DEFINE at SQL load time that has changed since
the last explicit SQL compilation.
•
INOPERABLE PLANS specifies that the SQL executor should perform the
similarity check for each SQL object in an SQL statement if the similarity check is
enabled for referenced tables and protection views and either of these conditions
occur:
°
The statement is invalid.
°
The statement refers to a DEFINE at SQL load time that has changed since
the last explicit SQL compilation.
If the similarity check passes, the SQL executor considers the plan to be operable
(although it might not be optimal) and executes the statement without automatically
recompiling it.
If the similarity check fails, the SQL executor considers the plan to be inoperable.
The SQL executor then recompiles (in memory only) the statement that generated
the inoperable plan (if NORECOMPILE is not specified) and executes the
recompiled statement.
Parallel Execution Plans
You cannot use the similarity check for a query that uses parallel execution plans. At
run time, a query that uses parallel execution plans fails the similarity check, and the
SQL statement containing the query must be automatically recompiled before it can
run (if NORECOMPILE is not specified). To use the similarity check in this query, you
must disable parallel plans by using a CONTROL QUERY PARALLEL EXECUTION
OFF directive.
Preventing Program Invalidation Caused by DDL Operations
Certain DDL operations on an SQL object cause a program that refers to the object to
be invalidated. When a program is invalidated, the SQL catalog manager sets the
VALID flag to N in the PROGRAMS catalog table and in the program’s file label (if the
program file is accessible) and deletes the program’s usages entries in the USAGES
table. An invalid program must be recompiled either explicitly or automatically before it
can execute.