SQL/MP Installation and Management Guide

Managing Database Applications
HP NonStop SQL/MP Installation and Management Guide—523353-004
10-8
Automatic Recompilation
•
The AUDIT attribute of a table referred to by an SQL statement is altered. This
does not invalidate the statement, but in these cases altering the AUDIT attribute
can cause automatic recompilation:
°
If a statement performs a DELETE or UPDATE set operation on a nonaudited
table with a SYNCDEPTH of 1, the SQL executor returns SQL error 8203 and
forces automatic recompilation of the statement.
°
If a statement is executed in parallel on a table whose AUDIT attribute has
changed since the last explicit SQL compilation, the SQL executor returns SQL
error 8207 and forces automatic recompilation of the statement.
The SQL executor detects the condition requiring recompilation and invokes the SQL
compiler. The compiler generates an execution plan based on the available information
and the best available access path.
If a statement cannot be executed again because of another invalid path, a last attempt
is made to compile the statement by using the primary key as the access path. The
SQL executor tries to recompile only two times. If, in these two attempts, the SQL
compiler cannot find an available access path that returns all requested data, the data
is considered unavailable, and an error is returned to the program for the statement.
Subsequent use of the same statement within the same process (SQLCI session) uses
the plan developed by the previous recompilation, so that recompilation does not occur
again.
If an access path becomes unavailable before the execution of an SQL statement
finishes, the SQL executor takes one of these actions:
•
If no records were returned during the statement execution, the SQL executor
again attempts recompilation as described in the preceding paragraphs.
•
If one or more records were returned during the statement execution, the SQL
executor returns an error indication and terminates.
Automatic recompilation does not occur if an object that was initially unavailable
becomes available while a program is running. If a program was automatically
recompiled because a path in the best query execution plan became unavailable, the
program continues to run with the best available access path until the program is
stopped and restarted.
The results of automatic recompilation are:
•
Executable object code for the optimized access paths is generated in the copy of
the program file in the SQL executor’s memory.
•
The program is validated only for the duration of the current session. No
compilation changes or validation flags are stored in the program file or in catalogs.
You can reduce compilation time for an application by directing the SQL compiler to
recompile only plans that are actually inoperable, not merely invalid. If you do so, the
SQL compiler uses similarity checks to determine whether certain invalid plans (those