SQL/MP Programming Manual for COBOL

Program Invalidation and Automatic SQL
Recompilation
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
8-7
Causes of Automatic Recompilation
(For a dynamic SQL statement, the SQL compiler uses the current set of DEFINEs
when the PREPARE or EXECUTE IMMEDIATE statement executes.)
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 executes 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 executes again, the SQL executor reopens the table and then performs the
timestamp check, which forces 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, 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 executes, 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.
4. The SQL executor invokes the SQL compiler to recompile the SELECT statement
using the current TAB definition. This recompilation does not modify the PROG
program file on disk; it only changes the copy of PROG in memory.