Description:
Created an 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 do I resolve this and generate valid DDL in order to convert this table?
Solution:
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 RELRESPCOMP ASC CHAR 3 N ___ 2 RELRESPOFFICE ASC CHAR 11 N ___ 3 PRNOI ASC CHAR 24 N ___ 4 PRREVI 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.