SQL/MX 3.1 Reference Manual (H06.23+, J06.12+)

SQL/MX Statements
HP NonStop SQL/MX Release 3.1 Reference Manual—663850-001
2-34
Considerations for ALTER TABLE
because it is the same physical table with a different name. The following example
illustrates this occurrence:
>>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 and the table is not open. 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. The similarity check fails and an error is returned
because the table t1 was renamed but was not accessed before the rename operation.
There are two ways to avoid such a failure:
•
Create a new table with the same ANSI name and layout. When an application
accesses the table, the executor determines that the redefinition timestamp is