SQL/MP Programming Manual for COBOL

Program Invalidation and Automatic SQL
Recompilation
HP NonStop SQL/MP Programming Manual for COBOL—529758-003
8-14
Preventing Automatic Recompilations
An SQL statement uses unqualified column names and the additional columns make
one of the column names used in the statement ambiguous. When the statement is
compiled, the column names are resolved unambiguously. However, if the execution
plan for the statement is executed against a RUN-TIME-TABLE with more columns
than the COMPILE-TIME-TABLE, the column names might not be resolved
unambiguously.
For example, consider these SQLCI commands:
CREATE TABLE table1 (a INTEGER, b INTEGER);
INSERT INTO table1 VALUES (11,22);
CREATE TABLE table2 (c INTEGER, d INTEGER);
INSERT INTO table2 VALUES (33,44);
PREPARE statement1 FROM SELECT a,b,c,d FROM table1, table2;
EXECUTE statement1; -- Returns 11,22,33,44
ALTER TABLE table1 ADD COLUMN c INTEGER DEFAULT NULL;
PREPARE statement1; -- Returns an error because the compiler
-- cannot resolve column c unambiguously
A similar situation occurs if you specify the CHECK INOPERABLE PLANS option and
execution-time name resolution. When the SQL executor attempts to use the plan with
a new set of tables, it retains the association of the unqualified column names with
tables established when the statement was explicitly compiled. However, if the
similarity check fails and automatic recompilation is attempted, the recompilation also
fails because of the ambiguity.
If an INSERT statement does not specify the column-name list, the statement must
specify values for all the columns in the table:
INSERT INTO table1 VALUES (1,2,3,4);
INSERT INTO table1 (SELECT a,b,c,d FROM table2);
For these statements to compile successfully, TA
BLE1 must have four columns at both
compile time and run time. A program cannot use the CHECK INOPERABLE PLANS
option to execute the statement against TABLE1 after a column has been added to the
run-time version of TABLE1. In this case, the similarity check fails, and the statement is
automatically recompiled.
SELECT columna FROM table1
WHERE EXISTS
(SELECT [DISTINCT] * FROM table2)
TABLE1 = Pass
TABLE2 = Fail
INSERT INTO table1
(SELECT [DISTINCT] * FROM table2)
TABLE1 = Fail
TABLE2 = Fail
SELECT table1.*,table2.x
FROM table1,table2
TABLE1 = Fail,
TABLE2 = Pass