Skip to Main Content
IBM Data and AI Ideas Portal for Customers


This portal is to open public enhancement requests against products and services offered by the IBM Data & AI organization. To view all of your ideas submitted to IBM, create and manage groups of Ideas, or create an idea explicitly set to be either visible by all (public) or visible only to you and IBM (private), use the IBM Unified Ideas Portal (https://ideas.ibm.com).


Shape the future of IBM!

We invite you to shape the future of IBM, including product roadmaps, by submitting ideas that matter to you the most. Here's how it works:


Search existing ideas

Start by searching and reviewing ideas and requests to enhance a product or service. Take a look at ideas others have posted, and add a comment, vote, or subscribe to updates on them if they matter to you. If you can't find what you are looking for,


Post your ideas

Post ideas and requests to enhance a product or service. Take a look at ideas others have posted and upvote them if they matter to you,

  1. Post an idea

  2. Upvote ideas that matter most to you

  3. Get feedback from the IBM team to refine your idea


Specific links you will want to bookmark for future use

Welcome to the IBM Ideas Portal (https://www.ibm.com/ideas) - Use this site to find out additional information and details about the IBM Ideas process and statuses.

IBM Unified Ideas Portal (https://ideas.ibm.com) - Use this site to view all of your ideas, create new ideas for any IBM product, or search for ideas across all of IBM.

ideasibm@us.ibm.com - Use this email to suggest enhancements to the Ideas process or request help from IBM for submitting your Ideas.

IBM Employees should enter Ideas at https://ideas.ibm.com


Status Delivered
Created by Guest
Created on Mar 11, 2024

CDC zOS Support Index with Identity Columns

Last week, during our testing of table replication with identity columns, it was discovered that CDC on zOS was unable to utilize primary indexes containing identity columns for replication. Since many tables utilize the identity column as the primary index key, this issue prevents CDC from replicating these tables.   IBM ssupport uggested changing the identity column to a regular column.  While this approach may work for some tables, it won't work for other tables that requies identity column be present for record creation.   
The proposed idea is to ignore the index with identity column and treat it as a regular index.  This will make this index as a 'qualifed' index for replication.  Similar behaviour is observed on CDC Db2 LUW.   

Needed By Month
  • Admin
    Davendra Paltoo
    Reply
    |
    Apr 30, 2024

    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.

  • Guest
    Reply
    |
    Mar 26, 2024

    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.