Data Integration

  • 1.  Identifying Delete in DB2 to Kafka replication

    Posted Tue July 20, 2021 09:29 AM
    We are replicating DB2 (ZOS) data to Kafka and from kafka we use S3 connector to write data in S3.
    we are using derived column to figure out type of operation done on row to identify Insert/update/delete.

    it works for insert and update, it doesn't work for delete. instead of D we get keys and operation type - null as avro messages.

    derived column- 
    %IF(&ENTTYP='PT' OR &ENTTYP='RR','I', %IF(&ENTTYP= 'UB', 'U', %IF(&ENTTYP= 'UP','U', %IF(&ENTTYP='DL','D','D'))))

    Could you Please let us know how can we identify delete ?

    our S3 connector is not working for delete now.

    ------------------------------
    Abhishek Kumar
    ------------------------------


    #DataIntegration
    #DataReplication


  • 2.  RE: Identifying Delete in DB2 to Kafka replication

    Posted Tue July 20, 2021 10:02 AM
    Hello Abhishek

    A delete operation in Kafka is marked by having the key columns populated and the data values set to null.
    This includes your derived column to record the operation type on the source.
    if you made the derived column a key column as well as the current key columns then the DL would appear in Kafka. However I am not sure what impact that would have on the downstream processing.
    A more radical alternative is to use say the audit mode KCOP which will simply add each row to Kafka together with the transaction type soure timestamp and user.
    Another approach is to test if the derived column is null or space, and if it is then it must be a delete

    Hope this helps

    ------------------------------
    Robert Philo
    ------------------------------



  • 3.  RE: Identifying Delete in DB2 to Kafka replication

    Posted Tue July 20, 2021 11:16 AM
    if you made the derived column a key column as well as the current key columns then the DL would appear in Kafka. However I am not sure what impact that would have on the downstream processing. --We have tried this, its not a feasible solution.

    A more radical alternative is to use say the audit mode KCOP which will simply add each row to Kafka together with the transaction type soure timestamp and user.
     ---  We don't have auditing requirements so KCOP will result in additional rows

    Another approach is to test if the derived column is null or space, and if it is then it must be a delete

     ---  We are using S3 connector not a customized consumer, We don't have much option of adding additional logic.

    Any other solution ?

    -- Thanks 
    Abhishek 



    ------------------------------
    Abhishek Kumar
    ------------------------------



  • 4.  RE: Identifying Delete in DB2 to Kafka replication

    Posted Tue July 20, 2021 11:30 AM
    Abhishek
    I think you are caught between the features of Kafka and the limitations of your S3 connector then.

    I think you will have to look at using a KCOP then to convert your delete message to an update and set the enttyp to DL in the code

    Regards
    Robert

    ------------------------------
    Robert Philo
    ------------------------------



  • 5.  RE: Identifying Delete in DB2 to Kafka replication

    Posted Wed July 21, 2021 03:19 AM
    Thank you

    ------------------------------
    Abhishek Kumar
    ------------------------------