When altering a table from Table Controlled Partitioning to Index Controlled Partitioning, the Analysis generates error message RMA689E.

Products

• RC Compare for DB2 for z/OS

Issue/Introduction

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.

Environment

Release:
Component: RCM