|
Problem IC88315
|
Status: Closed |
ADMIN_MOVE_TABLE FAILS WITH SQL2103N RC 17 IF THERE ARE 3 TYPES OF INDEX ON A TABLE (PRIMARY KEY , UNIQUE , NON UNIQUE ) |
| product: |
DB2 FOR LUW / DB2FORLUW / A10 - DB2 |
| Problem description: |
admin_move_table fails with sql2103n RC 17 if there are 3
types of index on a table (Primary key , unique , non unique )
The following testcase demonstrates the problem :
connect to sample;
create tablespace tbs_temp1 ;
create tablespace systoolspace ;
create table t1 ( c1 integer not null , c2 integer not null ) ;
alter table t1 add primary key (c1 ) ;
create index in1 on t1 (c2 asc ) ;
create unique index in2 on t1 ( c2 asc) ;
call sysproc.admin_move_table ('DB2INS10','T1' ,
'TBS_TEMP1','TBS_TEMP1', 'TBS_TEMP1', '', '', '', '', '',
'MOVE') ;
Fails with :
SQL2103N The ADMIN_MOVE_TABLE procedure could not be completed
because some
aspect of either the source table or target table is not
supported by the
ADMIN_MOVE_TABLE procedure. Reason code: "17". SQLSTATE=5UA0M
with db2diag.log entry :
INSTANCE: db2ins10 NODE : 000 DB : TEST1
APPHDL : 0-1507 APPID:
*LOCAL.db2ins10.120705051415
AUTHID : DB2INS10
EDUID : 66 EDUNAME: db2agent (TEST1) 0
FUNCTION: DB2 UDB, database application extension for utili,
adminotm_online_table_move, probe:20417
DATA #1 : String, 8 bytes
DB2INS10
DATA #2 : String, 2 bytes
T1
DATA #3 : String, 4 bytes
MOVE
DATA #4 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2103 sqlerrml: 4
sqlerrmc: 17
sqlerrp : SQL09076
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3)
0x00000000
(4) 0x00000000 (5) 0x00000000 (6)
0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
The following select shows the different index types present :
$ db2 "select indname,indextype,uniquerule from syscat.indexes
where tabname='T1'"
INDNAME INDEXTYPE UNIQUERULE
----------------------- --------- ----------
SQL120705061145480 REG P
IN1 REG D
IN2 REG U |
| Problem Summary: |
****************************************************************
* USERS AFFECTED: *
* Users who use admin_move_table in DB2 V10.1 *
****************************************************************
* PROBLEM DESCRIPTION: *
* admin_move_table fails with sql2103n RC 17 if there are 3 *
* types of index on a table (Primary key , unique , non unique *
* ) *
* *
* The following testcase demonstrates the problem : *
* *
* connect to sample; *
* *
* create tablespace tbs_temp1 ; *
* create tablespace systoolspace ; *
* create table t1 ( c1 integer not null , c2 integer not null *
* ) ; *
* alter table t1 add primary key (c1 ) ; *
* create index in1 on t1 (c2 asc ) ; *
* create unique index in2 on t1 ( c2 asc) ; *
* *
* call sysproc.admin_move_table ('DB2INS10','T1' , *
* 'TBS_TEMP1','TBS_TEMP1', 'TBS_TEMP1', '', '', '', '', '', *
* 'MOVE') ; *
* *
* Fails with : *
* *
* SQL2103N The ADMIN_MOVE_TABLE procedure could not be *
* completed *
* because some *
* aspect of either the source table or target table is not *
* supported by the *
* ADMIN_MOVE_TABLE procedure. Reason code: "17". *
* SQLSTATE=5UA0M *
* *
* with db2diag.log entry : *
* *
* INSTANCE: db2ins10 NODE : 000 DB : *
* TEST1 *
* APPHDL : 0-1507 APPID: *
* *LOCAL.db2ins10.120705051415 *
* AUTHID : DB2INS10 *
* EDUID : 66 EDUNAME: db2agent (TEST1) 0 *
* FUNCTION: DB2 UDB, database application extension for utili, *
* adminotm_online_table_move, probe:20417 *
* DATA #1 : String, 8 bytes *
* DB2INS10 *
* DATA #2 : String, 2 bytes *
* T1 *
* DATA #3 : String, 4 bytes *
* MOVE *
* DATA #4 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes *
* sqlcaid : SQLCA sqlcabc: 136 sqlcode: -2103 *
* sqlerrml: 4 *
* sqlerrmc: 17 *
* sqlerrp : SQL09076 *
* sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) *
* 0x00000000 *
* (4) 0x00000000 (5) 0x00000000 (6) *
* 0x00000000 *
* sqlwarn : (1) (2) (3) (4) (5) *
* (6) *
* *
* (7) (8) (9) (10) (11) *
* sqlstate: *
* *
* The following select shows the different index types present *
* : *
* *
* $ db2 "select indname,indextype,uniquerule from *
* syscat.indexes *
* where tabname='T1'" *
* *
* INDNAME INDEXTYPE UNIQUERULE *
* ----------------------- --------- ---------- *
* SQL120705061145480 REG P *
* IN1 REG D *
* *
* IN2 REG U *
****************************************************************
* RECOMMENDATION: *
* UPGRADE TO V101FP2 *
**************************************************************** |
| Local Fix: |
|
| available fix packs: |
DB2 Version 10.1 Fix Pack 2 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 4 for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 3a for Linux, UNIX, and Windows
DB2 Version 10.1 Fix Pack 6 for Linux, UNIX, and Windows
|
| Solution |
UPGRADE TO V101FP2 |
| Workaround |
not known / see Local fix |
| Timestamps |
Date - problem reported :
Date - problem closed :
Date - last modified :
| 17.11.2012
17.01.2013
17.01.2013 |
| Problem solved at the following versions (IBM BugInfos) |
|
| Problem solved according to the fixlist(s) of the following version(s) |
| 10.1.0.2
|
 |
| 10.5.0.2
|
|
