When ADMIN_MOVE_TABLE is used to move a table and a target table
is created by the stored procedure, the target table will have
the same table flags as those of the source table. One of these
flags is RESTRICT ON DROP. If the source table has restrict on
drop enabled then so will the target table. When an
ADMIN_MOVE_TABLE operation is subsequently canceled, any objects
that the stored procedure has created including staging table,
target table, triggers etc. will need to be dropped. But since
the target table has the DROP ON TABLE flag, it can't be
dropped, which will cause the CANCEL operation fails with the
SQL0672N error.
For example:
//create a table named tab1 with restrict on drop
db2 "create table tab1(c1 int) with restrict on drop"
//call admin_move_table with INIT
db2 "call
admin_move_table('DB2INST1','TAB1','','','','','','','','','INIT
')"
//call admin_move_table with CANCEL, it fails with the SQL0672N
error
db2 "call
admin_move_table('DB2INST1','TAB1','','','','','','','','','CANC
EL')"
SQL0672N Operation DROP not allowed on table
"DB2INST1.TAB1AABYVRt".
SQLSTATE=55035 |
manually alter the target table to remove RESTRICT ON DROP
before running ADMIN_MOVE_TABLE with CANCEL.
For example, in the above example,
db2 "alter table DB2INST1.\"TAB1AABYVRt\" drop restrict on drop"
db2 "call
admin_move_table('DB2INST1','TAB1','','','','','','','','','CANC
EL')" |
