ADMIN_MOVE_TABLE might return SQL2103N rc=17 if PRIMARY Key
indexes do not match.
This can occur if there are UNIQUE Indexes created on the source
table with the
same columns, but different order.
eg.
$ db2 "create table db2inst1.tab1(c1 int not null, c2 int not
null)"
DB20000I The SQL command completed successfully.
$ db2 "create unique index db2inst1.indx1 on
db2inst1.tab1(c1,c2)"
DB20000I The SQL command completed successfully.
$ db2 "create unique index db2inst1.indx2 on
db2inst1.tab1(c2,c1)"
DB20000I The SQL command completed successfully.
$ db2 "alter table db2inst1.tab1 add primary key(c2,c1)"
SQL0598W Existing index "DB2INST1.INDX1" is used as the index
for the primary
key or a unique key. SQLSTATE=01550
$ db2 "select substr(indname,1,30), uniquerule from
syscat.indexes where tabname='TAB1'"
1 UNIQUERULE
------------------------------ ----------
INDX1 P
INDX2 U
2 record(s) selected.
We could look to use either INDX1 or INDX2 for the Primary Key
Index.
This is working as design on the ALTER TABLE statement:
See topic:
Database administration > Administration concepts > Database
objects > Constraints
Reuse of indexes with unique or primary key constraints
If you use the ALTER TABLE command to add a unique or primary
key constraint to a partitioned table with a partitioned index,
depending on the indexes that already exist, one might be
altered to enforce the new constraint, or a new one might be
created.
When you run the ALTER TABLE statement to add or change a unique
or primary key for a table, a check is performed to determine
whether any existing index matches the unique or primary key
being defined (INCLUDE columns are ignored). An index definition
matches if it identifies the same set of columns, regardless of
the order or the direction (for example ASC/DESC) of the
columns.
In the case of partitioned tables that have partitioned,
non-unique indexes, if the index columns of the table being
altered are not included among the columns that form the
partition key, the index will not be considered a matching
index.
If the table does have a matching index definition, it will
changed to be a UNIQUE index if it wasn't one already, and will
marked as required by the system. If the table has more than one
existing index that matches, then an existing unique index is
selected. If there is more than one matching unique index, or if
there are more than one matching non-unique indexes and no
matching unique indexes, then a partitioned index is favoured.
Otherwise the selection of an index is arbitrary.
If no matching index is found, then a unique bidirectional index
is automatically created for the columns. |
