CDC zOS Support Index with Identity Columns

IBM Update

PTF UI96691 for APAR PH60838 that addresses CDC-I-637 : CDC zOS Support Index with Identity Columns has been shipped.This PTF may still need 1 or 2 days to be visible/available for customers to download.

Documentation in the knowledge center will also be updated. In the meantime, here is a brief description of this enhancement:

1) A new parameter ALLOWGENERATEDKEY is introduced to control whether generated keys are allowed in replication index, the value can be YES or NO. NO is the default value, with this setting, CDC does not allow generated keys in replication index, as what CDC behaves without this feature. When ALLOWGENERATEDKEY is setting to YES, CDC allows GENERATED BY DEFAULT columns in replication index, but still does not allow GENERATED ALWAYS columns.
The ALLOWGENERATEDKEY should be configured in CHCDBMXX member dataset.

2) Unique index with GENERATED BY DEFAULT columns has the lowest rating, so when there are several qualifying indexes available for target table, by default, the one with GENERATED BY DEFAULT columns would not be chosen as replication index, but customer can choose it via MC Column Mappings -> Key Columns.. ->Specify Key -> Use an Index.
If unique index with GENERATED BY DEFAULT columns is the only qualifying index, then CDC has no choice but to use it as replication index, in such situation, create table mapping will trigger warning message CHC1516W(Pop up in MC as well as issuing in CDC z/OS SPOOLed output), start refresh/mirror will trigger warning message CHC1519W, refresh/mirror can continue. Of course, those are only happening when ALLOWGENERATEDKEY=YES.

I am providing more information about the test case.  

1) DDL on DB2 LUW

Here is the DDL for creating table on Db2 LUW.

CREATE TABLE "DBATST0"."TPAS_FIS_TO_TRP" (
    "ID" INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY ( START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CYCLE CACHE 20 NO ORDER ), 
    "COL1" SMALLINT, 
    "COL2" INTEGER, 
    "COL3" DECIMAL(10 , 2), 
    "COL4" CHAR(10 OCTETS), 
    "COL5" VARCHAR(50 OCTETS), 
    "COL6" DATE, 
    "COL7" TIME, 
    "COL8" TIMESTAMP
  )
  ORGANIZE BY ROW
  DATA CAPTURE CHANGES;;
ALTER TABLE "DBATST0"."TPAS_FIS_TO_TRP" ADD CONSTRAINT "PK_TPAS_FIS_TO_TRP" PRIMARY KEY
  ("ID");

2) DDL on Db2 zOS

CREATE TABLE DBATST0.TPAS_FIS_TO_TRP2                               
         (ID INTEGER                                                     
                                                         NOT NULL        
   GENERATED BY DEFAULT AS IDENTITY ( START WITH 1                       
         , INCREMENT BY 1                                                
         , MINVALUE 1                                                    
         , MAXVALUE 2147483647                                           
         , CACHE 20                                                      
         , NOCYCLE                                                       
         , NOORDER )                                                     
         ,COL1 SMALLINT                                                  
   WITH DEFAULT NULL                                                     
         ,COL2 INTEGER                                                   
   WITH DEFAULT NULL                                                     
         ,COL3 DECIMAL(10, 2)                                            
   WITH DEFAULT NULL                                                     
         ,COL4 CHARACTER(10) FOR SBCS DATA                               
   WITH DEFAULT NULL                                                     
         ,COL5 VARCHAR(50) FOR SBCS DATA                                 
   WITH DEFAULT NULL                                                     
         ,COL6 DATE                                                      
   WITH DEFAULT NULL                                                     
         ,COL7 TIME                                                      
   WITH DEFAULT NULL                                                     
         ,COL8 TIMESTAMP (6) WITHOUT TIME ZONE                           
   WITH DEFAULT NULL                                                     
         ,CONSTRAINT I37TST21 PRIMARY KEY                                
         (ID                                                             
         )                                                               
         )                                                               
         IN PAS0037D.S37TST02                                            
    APPEND NO                                                            
    NOT VOLATILE CARDINALITY                                             
    DATA CAPTURE CHANGES                                                 
    AUDIT NONE                                                           
    CCSID EBCDIC;       

CREATE UNIQUE INDEX DBATST0.I37TST21   
       ON DBATST0.TPAS_FIS_TO_TRP2     
      (ID ASC                          
      )                                
       INCLUDE NULL KEYS               
       NOT CLUSTER                     
       DEFINE YES                      
       COMPRESS NO                     
       BUFFERPOOL BP0                  
       CLOSE YES                       
       DEFER NO                        
       COPY NO                         
       USING STOGROUP SYSDEFLT         
           PRIQTY -1                   
           SECQTY -1                   
           ERASE NO                    
       FREEPAGE 0                      
       PCTFREE 10                      
       GBPCACHE CHANGED                
       PIECESIZE 4194304K;          

3) Error message received during mapping.

ERROR : 

CHC1508W: The selected table has no qualifying index. The table can be used for refresh, but a qualifying index must be created before it can be used for mirroring.  A qualifying index is a unique index with no nullable and no GENERATED ALWAYS columns.