When using the ADMIN_MOVE_TABLE procedure, there are occasions
where it would fail due to various errors. To recover/cancel the
ADMIN_MOVE_TABLE procedure, a CANCEL option will be used.
However the CANCEL option will not do anything to fix the
problem as it would fail to remove the staging table created by
the ADMIN_MOVE_TABLE procedure.
So far this issue has only occured during if the problem occurs
during the INIT phase of the ADMIN_MOVE_TABLE procedure.
.
The problem can be reproduced using the following steps...
.
$ db2trc on -crash
DB2.DBAPPEXT_UTIL.adminotm_Otm_createStagingTable.exit
Trace is turned on
.
$ db2 "call
admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
CE')"
SQL1224N The database manager is not able to accept new
requests, has
terminated all requests in progress, or has terminated the
specified request
because of an error or a forced interrupt. SQLSTATE=55032
.
$ db2trc off
Trace is already off; no action was taken.
.
$ db2 list tables for schema test
.
Table/View Schema Type Creation
time
------------------------------- --------------- -----
--------------------------
TAB1 TEST T
2013-04-25-01.49.38.397285
TAB1AABYVRs TEST T
2013-04-25-02.10.29.760057
TAB1AABYVRt TEST T
2013-04-25-02.10.29.499973
.
3 record(s) selected.
.
$ db2 "select substr(key,1,30), substr(value,1,30) from
systools.admin_move_table"
.
1 2
------------------------------ ------------------------------
STAGING TAB1AABYVRs
LOCK 2013-04-25-02.10.29.243344
STATUS INIT
AUTHID TANJINXU
VERSION 09.07.0007
INIT_START 2013-04-25-02.10.29.337336
INDEXSCHEMA
INDEXNAME
TARGET TAB1AABYVRt
.
9 record(s) selected.
.
$ db2 "call
admin_move_table('TEST','TAB1','','','','','','','','','cancel,t
race')"
.
Result set 1
--------------
.
KEY VALUE
-------------------------------- ---------------------------
.
0 record(s) selected.
.
Return Status = 0
.
$ db2 "select substr(key,1,30), substr(value,1,30) from
systools.admin_move_table"
.
1 2
------------------------------ ------------------------------
.
0 record(s) selected.
.
$ db2 list tables for schema test
.
Table/View Schema Type Creation
time
------------------------------- --------------- -----
--------------------------
TAB1 TEST T
2013-04-25-01.49.38.397285
TAB1AABYVRs TEST T
2013-04-25-02.10.29.760057
TAB1AABYVRt TEST T
2013-04-25-02.10.29.499973
.
3 record(s) selected.
.
$ db2 "call
admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
CE')"
SQL0601N The name of the object to be created is identical to
the existing
name "TEST.TAB1AABYVRt" of type "TABLE". SQLSTATE=42710 |
Users Affected : All
Problem Description :
When using ADMIN_MOVE_TABLE, the staging and target table
is not cleaned during the cancel operation when INIT phase
fails.
Problem Summary :
When using the ADMIN_MOVE_TABLE procedure, there are occasions
where it would fail due to various errors. To recover/cancel the
ADMIN_MOVE_TABLE procedure, a CANCEL option will be used.
However the CANCEL option will not do anything to fix the
problem as it would fail to remove the staging table created by
the ADMIN_MOVE_TABLE procedure.
So far this issue has only occured during if the problem occurs
during the INIT phase of the ADMIN_MOVE_TABLE procedure.
.
The problem can be reproduced using the following steps...
.
$ db2trc on -crash
DB2.DBAPPEXT_UTIL.adminotm_Otm_createStagingTable.exit
Trace is turned on
.
$ db2 "call
admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
CE')"
SQL1224N The database manager is not able to accept new
requests, has
terminated all requests in progress, or has terminated the
specified request
because of an error or a forced interrupt. SQLSTATE=55032
.
$ db2trc off
Trace is already off; no action was taken.
.
$ db2 list tables for schema test
.
Table/View Schema Type Creation
time
------------------------------- --------------- -----
--------------------------
TAB1 TEST T
2013-04-25-01.49.38.397285
TAB1AABYVRs TEST T
2013-04-25-02.10.29.760057
TAB1AABYVRt TEST T
2013-04-25-02.10.29.499973
.
3 record(s) selected.
.
.
$ db2 "select substr(key,1,30), substr(value,1,30) from
systools.admin_move_table"
.
1 2
------------------------------ ------------------------------
STAGING TAB1AABYVRs
LOCK 2013-04-25-02.10.29.243344
STATUS INIT
AUTHID TANJINXU
VERSION 09.07.0007
INIT_START 2013-04-25-02.10.29.337336
INDEXSCHEMA
INDEXNAME
TARGET TAB1AABYVRt
.
9 record(s) selected.
.
$ db2 "call
admin_move_table('TEST','TAB1','','','','','','','','','cancel,t
race')"
.
Result set 1
--------------
.
KEY VALUE
-------------------------------- ---------------------------
.
0 record(s) selected.
.
Return Status = 0
.
$ db2 "select substr(key,1,30), substr(value,1,30) from
systools.admin_move_table"
.
1 2
------------------------------ ------------------------------
.
0 record(s) selected.
.
$ db2 list tables for schema test
.
Table/View Schema Type Creation
time
------------------------------- --------------- -----
--------------------------
TAB1 TEST T
2013-04-25-01.49.38.397285
TAB1AABYVRs TEST T
2013-04-25-02.10.29.760057
TAB1AABYVRt TEST T
2013-04-25-02.10.29.499973
.
3 record(s) selected.
.
$ db2 "call
admin_move_table('TEST','TAB1','','','','','','','','','INIT,TRA
CE')"
SQL0601N The name of the object to be created is identical to
the existing
name "TEST.TAB1AABYVRt" of type "TABLE". SQLSTATE=42710 |
