Hi,
ADMIN_MOVE_TABLE aborts within the SWAP phase with SQL2103N RC=17 when two identical indexes (one non-unique the other unique) are on the source table.
I found APARs from 2012 where this bug was already solved but I am able to reproduce this with Db2 versions 11.1 / 11.5
http://www-01.ibm.com/support/docview.wss?uid=swg1IC77972
http://www-01.ibm.com/support/docview.wss?uid=swg1IC84547
With the current beta drop I cannot reproduce it - so I assume within vNext or one of further fixpacks for the current versions it willbe solved again - am I right?
Can any developer confirm this?
Db2 v11.1/v11.5
CREATE TABLE TEST01 (I INTEGER NOT NULL)
DB20000I The SQL command completed successfully.
CREATE INDEX INDEX01 ON TEST01 (I)
DB20000I The SQL command completed successfully.
CREATE UNIQUE INDEX UINDEX01 ON TEST01 (I)
DB20000I The SQL command completed successfully.
ALTER TABLE TEST01 ADD PRIMARY KEY (I)
SQL0598W Existing index "DB2INST3.UINDEX01" is used as the index for the
primary key or a unique key. SQLSTATE=01550
CALL admin_move_table('DB2INST3', 'TEST01',null,null,null,null,null,null,null,'','MOVE')
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
Current beta s1910021500
CALL admin_move_table('DB2INST4', 'TEST01',null,null,null,null,null,null,null,'','MOVE')
Result set 1
--------------
KEY VALUE
-------------------------------- --------------------------------------------------------------------------------------------------------------------------------
AUTHID DB2INST4
CLEANUP_END 2019-11-15-11.17.20.100232
CLEANUP_START 2019-11-15-11.16.46.994603
COPY_END 2019-11-15-11.16.39.833894
COPY_OPTS OVER_INDEX,ARRAY_INSERT,NON_CLUSTER
COPY_START 2019-11-15-11.16.07.012217
COPY_TOTAL_ROWS 0
INDEXNAME UINDEX01
INDEXSCHEMA DB2INST4
INDEX_CREATION_TOTAL_TIME 36
INIT_END 2019-11-15-11.15.49.068973
INIT_START 2019-11-15-11.15.18.263627
ORIGINAL_TBLSIZE 512
REPLAY_END 2019-11-15-11.16.46.599060
REPLAY_START 2019-11-15-11.16.39.837192
REPLAY_TOTAL_ROWS 0
REPLAY_TOTAL_TIME 1
STATUS COMPLETE
SWAP_END 2019-11-15-11.16.46.841054
SWAP_RETRIES 0
SWAP_START 2019-11-15-11.16.46.610788
UTILITY_INVOCATION_ID 0100000066040000080000000000000000002019111511154907002300000000
VERSION 11.01.0900
23 record(s) selected.
Return Status = 0
Thanks in advance.
Best regards,
Gerhard