SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-25
Using the COMPILE Option
•
Physical name
•
Redefinition timestamp. The new timestamp prevents future similarity checks for
the plan until the SQL object changes again.
•
Partition node array. SQL uses the partition node array to determine alternate
paths when partitions are unavailable to a plan.
SQL considers an altered execution plan to be invalid but operable and recompiles the
plan as follows:
•
During explicit compilation, the SQL compiler recompiles an altered plan if you
specify the COMPILE PROGRAM or COMPILE INVALID PLANS option.
•
At run time, the SQL executor automatically recompiles an altered plan if you
specified the CHECK INVALID PROGRAM or CHECK INVALID PLANS option
during the previous explicit SQL compilation.
CURRENTDEFINES and STOREDDEFINES Options
If you recompile a program using the CURRENTDEFINES option (which is the default)
and a statement refers to a DEFINE that does not exist, the SQL compiler recompiles
the statement and generates an empty section, regardless of whether the previous
execution plan was valid.
If you recompile a program by using the STOREDDEFINES option, the SQL compiler
resolves DEFINE names using the values stored in the program’s name map during
the previous explicit compilation. In this case, the current DEFINE values have no
effect on the COMPILE INVALID PLANS and COMPILE INOPERABLE PLANS
options, and the STOREDDEFINES option does not change the name map in the
program file.
Example: Explicitly Recompiling Only Inoperable Plans
If you must explicitly recompile a program, but you want to minimize downtime for the
program, use the COMPILE INOPERABLE PLANS option and the similarity check to
recompile only the statements with inoperable plans that fail the similarity check.
Follow these steps to implement this solution:
1. Enable the similarity check for each table or protection view specified in the SQL
statements as follows:
•
For existing tables, use the ALTER TABLE or ALTER VIEW statement with the
SIMILARITY CHECK ENABLE clause.
•
If you are creating a new table or protection view, use the CREATE TABLE or
CREATE VIEW statement with the SIMILARITY CHECK ENABLE clause.
2. Explicitly SQL compile the program with the COMPILE INOPERABLE PLANS
option. The SQL compiler compiles only the SQL statements with invalid plans that
fail the similarity check (and any uncompiled statements).