SQL Programming Manual for Pascal

Program Compilation and Execution
HP NonStop SQL Programming Manual for Pascal—528614-001
5-25
Predicting Automatic SQL Recompilation
•
Table open time
•
A new DEFINE at SQL load time
•
An unavailable access path (index)
•
The attempted execution of an uncompiled SQL statement
SQL Load Time
The following conditions can cause automatic recompilation at SQL load time:
•
The SQL VALID flag is set to N
Automatic recompilation of an invalid program file occurs each time the program
runs.
•
The set of DEFINEs at run time is different from the set used when you explicitly
compiled the program.
To control the extent of recompilation at SQL load time, use the SQLCOMP command
RECOMPILEALL or RECOMPILEONDEMAND option. For automatic recompilation of
the entire program, use the RECOMPILEALL option (which is the default). To limit
automatic recompilation to only statements that are actually executed, use the
RECOMPILEONDEMAND option.
Table Open Time
When the SQL executor accesses a table, an SQL statement is automatically
recompiled if the timestamp for the table is different from the timestamp for the last
SQL compilation of the statement.
Usually, the SQL executor opens a table the first time the program executes an SQL
statement that refers to the table. The executor then leaves the table open until the
program stops. DDL and database utility operations on the table (or on a dependent
index) cause the timestamp of the table to be updated. When the SQL statement that
refers to the table executes again, the executor compares the timestamp of the new
statement to the timestamp of the table and recompiles the statement, if necessary.
Only the specific SQL statement that refers to the table is automatically recompiled
when the timestamp check reveals a discrepancy with the table timestamp.
New DEFINEs
If the statement is recompiled, the compiler uses the SQL load time DEFINEs for a
static SQL statement. For a dynamic SQL statement, the compiler uses the current
active DEFINEs when the associated PREPARE statement is executed.
Unavailable Access Path (Index)
If the SQL executor tries to execute an SQL statement and detects that an access path
in the execution plan is unavailable, the executor invokes the SQL compiler to