SQL/MX 3.2 Reference Manual (H06.25+, J06.14+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.2 Reference Manual—691117-001
2-34
Considerations for ALTER TABLE
because it is the same physical table with a different name. The following example
illustrates this occurrence:
>>control query default RECOMPILATION_WARNINGS 'ON';
--- SQL operation complete.
>>prepare q from select * from t1;
--- SQL command prepared.
>>execute q;
<successful result>
>>alter table t1 rename to t2;
--- SQL operation complete.
>>execute q;
*** WARNING[8578] Similarity check passed.
<successful result>
>>
In this example, the first command execute q, uses the original ANSI name to
access the table t1. The second command changes the name and redefinition
timestamp of the table. The third command execute q, also uses the original ANSI
name to access the table t1. Although the rename command changes the name and
redefinition timestamp of the table, the similarity check passes because the table t1
was already open using the first command.
New attempts to access a renamed table using the original ANSI name fail, because
the original ANSI name no longer exists. The following example illustrates this
occurrence:
>>prepare q from select * from t1;
--- SQL command prepared.
>>alter table t1 rename to t2;
--- SQL operation complete.
>>execute q;
*** ERROR[1004] Object CAT.SCH.T1 does not exist or object type is
invalid for the current operation.
*** ERROR[8300] Late name resolution failed for table, view or
stored procedure CAT.SCH.T1.
--- 0 row(s) selected.
>>
In this example, the first command rename, changes the name and redefinition
timestamp of the table t1. The second command execute q, tries to access the table
t1 by the original ANSI name. An error is returned because the table t1 was renamed.
There are two ways to avoid such a failure: