SQL/MP Programming Manual for C

Program Invalidation and Automatic SQL
Recompilation
HP NonStop SQL/MP Programming Manual for C—429847-008
8-7
Causes of Automatic Recompilation
Changed DEFINEs
If the values of the DEFINEs used in the program at SQL load time differ from the
values of the DEFINEs used for explicit SQL compilation, the SQL executor forces the
automatic recompilation of the program or statement using the new DEFINE values.
(For a dynamic SQL statement, the SQL compiler uses the current set of DEFINEs
when the PREPARE or EXECUTE IMMEDIATE statement runs.)
Failed Timestamp Check
The SQL executor performs the timestamp check for each SQL object referenced in an
SQL statement at table open time (the first time the table is opened). The timestamp
check ensures that a statement’s current execution plan uses a valid definition of each
SQL object (table or view, or a dependent object such as an index or collation), even if
the program file was not accessible when the invalidating operation was performed on
the SQL object. (For operations that invalidate an SQL program, see Changes to
Referenced SQL Objects on page 8-2.)
Each SQL object contains a redefinition timestamp in its file label. An SQL program file
also contains the redefinition timestamps of all referenced SQL objects in each SQL
statement’s execution plan. When the SQL executor runs a statement, it compares the
timestamp in the object’s file label to the timestamp for the same object in the
statement’s execution plan. If the timestamps differ, the SQL executor forces a
recompilation with the new definition of the object.
After opening a table, the SQL executor usually leaves a table open until the program
stops running. However, a subsequent DDL or utility operation performed on the table
(or a dependent object such as an index or collation) causes the table to be closed and
its redefinition timestamp to be updated. If the SQL statement that refers to the table
runs again, the SQL executor reopens the table and then performs the timestamp
check to force a recompilation.
These steps describe the run-time timestamp check as shown in Figure 8-1
on
page 8-8.
1. A valid SQL program named PROG refers to an SQL table named TAB in a
SELECT statement. During explicit SQL compilation, NonStop SQL/MP generates
an execution plan, which includes the TAB redefinition timestamp, for the SELECT
statement and stores the plan in the PROG program file.
2. After PROG is running, a database administrator adds a new column to TAB using
the ALTER TABLE statement. This operation updates the redefinition timestamp in
the TAB file label.
3. When the SELECT statement runs, the SQL executor opens TAB and compares
the timestamp in TAB file label with the TAB timestamp in the PROG execution
plan. The TAB file label timestamp is more recent than the PROG execution plan
timestamp. Therefore, the execution plan for the SELECT statement that was
generated from the old definition of TAB during explicit SQL compilation is no
longer valid.