home clear 64x64
en blue 200x116 de orange 200x116 info letter User
suche 36x36
Latest versionsfixlist
11.1.0.7 FixList
10.5.0.9 FixList
10.1.0.6 FixList
9.8.0.5 FixList
9.7.0.11 FixList
9.5.0.10 FixList
9.1.0.12 FixList
Have problems? - contact us.
Register for free anmeldung-x26
Contact form kontakt-x26

DB2 - Problem description

Problem IC91921 Status: Closed

WHEN USING ADMIN_MOVE_TABLE, THE STAGING AND TARGET TABLE IS NOT CLEANED
DURING THE CANCEL OPERATION WHEN INIT PHASE FAILS.

product:
DB2 FOR LUW / DB2FORLUW / 970 - DB2
Problem description:
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
Problem Summary:
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
Local Fix:
Identify the table used in the ADMIN_MOVE_TABLE and the equiva- 
leng old target and old stagin table. 
Example : Table used in ADMIN_MOVE_TABLE = TAB1 
Do a "list tables for schema <schema name>". 
The output will show... 
$ 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. 
. 
Where : TAB1        = Table specified in the ADMIN_MOVE_TABLE 
        TAB1AABYVRs  and TAB1AABYVRt 
                    = Is the old target and old stagin table.
available fix packs:
DB2 Version 9.7 Fix Pack 9 for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 9a for Linux, UNIX, and Windows
 DB2 Version 9.7 Fix Pack 10 for Linux, UNIX, and Windows
Solution
Problem was first fixed in V9.7 FP9 (s131204).
Workaround
Identify the table used in the ADMIN_MOVE_TABLE and the equiva- 
leng old target and old stagin table. 
Example : Table used in ADMIN_MOVE_TABLE = TAB1 
Do a "list tables for schema <schema name>". 
The output will show... 
$ 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. 
. 
Where : TAB1 = Table specified in the ADMIN_MOVE_TABLE 
TAB1AABYVRs and TAB1AABYVRt 
= Is the old target and old stagin table.
BUG-Tracking
forerunner  : APAR is sysrouted TO one or more of the following: IC95520 IC95655 
follow-up :
Timestamps
Date  - problem reported    :
Date  - problem closed      :
Date  - last modified       :

26.04.2013
18.08.2014
18.08.2014
Problem solved at the following versions (IBM BugInfos)
9.7.FP9
Problem solved according to the fixlist(s) of the following version(s)
9.7.0.9 FixList
9.7.0.9 FixList