When altering a table from Table Controlled Partitioning to Index Controlled Partitioning, the Analysis generates error message RMA689E.
Article ID: 52269
Updated On:
Products
Issue/Introduction
Created an RC/Migrator for Db2 for z/OS (RCM) ALTER strategy to alter a table from Table Controlled Partitioning to Index Controlled Partitioning.
On the Table Alter panel the Partitioning field was changed from YES to NO.
ROPTBALX ---------------- CA - Table Alter --------------- 2009/12/21 12:01:5
COMMAND ===> SCROLL ===> PAG
Table => TABLE1 > Creator => CRT1 > Comm/Lab => N
Database => DB1 Editproc => Data Cap => NONE
Tablespace => TS1 Validproc => OBID =>
Partitioning > YES (TS Parts: 4) Audit => NONE Volatile => N
Table Type => REGULAR Restrict => N CCSID => EBCDIC
Row Size => 358/-3,690 Forgn Key => N Chk Const > N
When the strategy is analyzed, the analysis completes with a return code 8 and the following messages are issued:
RMA685W No TYPE 2 partitioning index was defined for the table being
converted from table-controlled partitioning to
index-controlled partitioning. A TYPE P clustering index was
found and will be converted to a TYPE 2 partitioning index.
RMA689E One or more TYPE P or D (DPSI) indexes are defined against a
table that will be converted from table-controlled partitioning
to index-controlled partitioning. These are not automatically
converted to TYPE 2 non-partitioned indexes and execution of
this DDL may fail.
How to resolve this and generate valid DDL in order to convert this table?
Resolution
An automatic complete conversion of your table from TCP to ICP, complete with a type-2 clustering index defined for the table, does NOT occur if
your table was originally defined as using table-controlled partitioning and does NOT also have a type-P clustering index defined on the table.
In this example, we do have a type-P clustering index which will therefore be converted to a type-2 partitioning index.
This is confirmed by warning message RMA685W:
RMA685W No TYPE 2 partitioning index was defined for the table being
converted from table-controlled partitioning to
index-controlled partitioning. A TYPE P clustering index was
found and will be converted to a TYPE 2 partitioning index.
However, we also have a type-D index which will NOT automatically be converted to a type-2 index.
This is reported by error message RMA689E:
RMA689E One or more TYPE P or D (DPSI) indexes are defined against a
table that will be converted from table-controlled partitioning
to index-controlled partitioning. These are not automatically
converted to TYPE 2 non-partitioned indexes and execution of
this DDL may fail.
In the strategy, one of the following actions should be taken; either edit the index to make it type 2, non-partitioned, non-clustering index or drop the index.
In this case we can alter the type-D index to be non-partitioned.
Update the Alter strategy and specify alter/index for the type-D index. On the Index Alter panel, switch the Partitioned field from YES to NO.
ROPIALTX ---------------- CA - Index Alter --------------- 2009/12/23 05:07:44 COMMAND ===> SCROLL ===> PAGE RO190I Index Type changed to 2 because index is defined as not partitioned. Index Name => INDEXD > Creator => CRT1 > Comment => N Table Name => TABLE1 > Creator => CRT1 > Unique Rule => NO Buffer Pool => BP2 Cluster => NO Close Dataset => NO Partitioned => NO GBP Cache => CHANGED Defer Build => NO Piecesize => Padded => DEFAULT Index Type => 2 Copy => NO Define => YES CMD SEQ# PS KEY-COLUMN-NAME ORDER COLTYPE SRCTYPE SIZE N ___ 1 xxxxxxxxxxx ASC CHAR 3 N ___ 2 xxxxxxxxxxxx ASC CHAR 11 N ___ 3 xxxxxxx ASC CHAR 24 N ___ 4 xxxxxxx DESC CHAR 8 N
Then re-analyze the strategy.
This time only the RMA685W warning message should be issued, confirming that the type-P index will be converted to a type-2 partitioning index.
This DDL can now be executed to convert the table from Table Controlled Partitioning to Index Controlled Partitioning.
Feedback
