we have subs from central db to distributed db with cdc on linux and db2 luw
now we have an issue, while exporting a sub, the msg indicates : table definition has been changed and can not export
we opened a case, but it takes long time before we get an answer or recommendation
we tried to remove this table from the subs, but then the export complains about another table and again and again
so I stopped the subs - saved the bookmark at target for this subs - dropped subs - imported from a previous export/other location
imported the bookmark at source
in this subs some tables are in adaptive apply
although this setting when starting the subs again, cdc complained that he could not update a row : not found...
this was done on a target db that is not in use anymore
what would be a good scenario for a life system, where the sources/target receive modifs continuously ?
full refresh was tried, but there was a table of 250M rows, and the refresh loader path was getting full
so we exported the table / loaded at target - reset bookmark : but then we risk that updates/inserts at src are not present at target
we also created triggers on source table to a staging table to capture modifs
but the reproduce the updates, the script takes long time, as we have many updates.
again, all hints or scenarios you have been using, welcome...

------------------------------
Thanks for all answers
Best Regards,
Guy Przytula
------------------------------


#DataReplication
#DataIntegration

Guy

If you get a message about the table definition changing, it is not enough to drop it from the subscription. You need to stop all subscriptions to which the table is selected and then select the table mapping and then take the option to update the source table definition.

I am not sure why you wanted to export the subscriptions, However if the export fails with the table changed definition it suggests that recreating the subscription by importing will have issues because there will be differences in DDL definitions between environments. On the other hand if you take the option to generate a CHCCLP script to create a subscription, this is more tolerant of DDL differences as you do not have to specify column level mappings in a CHCCLP script, and it is easier to edit a CHCCLP script anyway than XML

The bookmark of CDC is held in the target database (TS_BOOKMARK table) and can be queried even if the CDC instance is not available for any reason - select srcsysid, statusinfo from cdc.ts_bookmark . So once you have this you can use this with dmsetbookmark on the source to restart replication from a bookmark. It can be useful sometimes to periodically store the target bookmark so that in a recovery situation you have a choice of bookmarks for replaying transactions - on the basis that repeating changes is better than loosing them. Of course you need to make the target apply process tolerant of insert and delete failures if these DML operations are repeated - change the target datastore parameter mirror_end_on_error to false

If you need to refresh or reload and you prefer to perform an  external load, you can use the dmmarkexternalunloadstart and dmmarkexternalunloadend commands to mark the start and end of the external export process on the source, CDC will then apply any  changes to the target adaptively in the same was as the native refresh. This is described at https://www.ibm.com/docs/en/idr/11.4.0?topic=commands-dmmarkexternalunloadstart-start-table-data-unload and https://www.ibm.com/docs/en/idr/11.4.0?topic=commands-dmmarkexternalunloadend-end-table-data-unload 

Regards

Robert







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

Original Message:
Sent: Tue February 22, 2022 10:59 AM
From: Guy Przytula
Subject: CDC and reset bookmark

we have subs from central db to distributed db with cdc on linux and db2 luw
now we have an issue, while exporting a sub, the msg indicates : table definition has been changed and can not export
we opened a case, but it takes long time before we get an answer or recommendation
we tried to remove this table from the subs, but then the export complains about another table and again and again
so I stopped the subs - saved the bookmark at target for this subs - dropped subs - imported from a previous export/other location
imported the bookmark at source
in this subs some tables are in adaptive apply
although this setting when starting the subs again, cdc complained that he could not update a row : not found...
this was done on a target db that is not in use anymore
what would be a good scenario for a life system, where the sources/target receive modifs continuously ?
full refresh was tried, but there was a table of 250M rows, and the refresh loader path was getting full
so we exported the table / loaded at target - reset bookmark : but then we risk that updates/inserts at src are not present at target
we also created triggers on source table to a staging table to capture modifs
but the reproduce the updates, the script takes long time, as we have many updates.
again, all hints or scenarios you have been using, welcome...

------------------------------
Thanks for all answers
Best Regards,
Guy Przytula
------------------------------


#DataIntegration
#DataReplication