Question & Answer
Question
When using IIDR for Db2 for i source, what are the steps to resume replication following a planned DDL change that results in a table structure change?
Cause
InfoSphere CDC maintains and depends on metadata describing source and target tables and columns being replicated. When DDL occurs which results in a change to the source or target table structures, the metadata no longer contains the correct information with which to read source database log records to capture change data during mirroring.
For that reason, following a DDL change that results in table structural changes, some action is required to update the metadata before replication can be performed or resumed.
This document describes how to update table definitions to accommodate a DDL change so that replication can proceed.
Answer
Planning DDL changes
In the best case, a DDL change that affects replication of a table or tables that are being replicated better to be known and planned. In this case, it is necessary to ensure that the entire procedure described below is executed during a quiescent maintenance period for both source and target systems where no DML changes occur to the same tables.
The steps would be given base on below architecture and planned DDL change as example scenario:
- Architecture of the environment
In the scenario, the CM_DEV subscription is replicating from the APPSRCDEV library on the Toronto server APP_TGT_DEV database on the Sydney server. The CM_PRD is the production counterpart of CM_DEV and replicates from APPSRCPRD to APP_TGT_PRD.
The changes are first made in the development environment and deployed to the production environment. In the development environment, there are no constraints regarding refreshes etc, but in the production environment, no refreshes can be done; the procedure must be such that no operations are lost or duplicated.
Note: Pre-condition for a successful import of a subscription having table mappings that using an index is that a named index (not a system-generated name) has been specified and that the index exists in the target environment. Alternatively, the individual key columns can be specified.
- Table structure of tables that has DDL changes applied in development and production environment:
Table CUSTOMER:
Table PRODUCT:
- Subscription CM_DEV has mapped table the following tables, one to one replication, no transformations.
Add and modify a column to the APPSRCDEV/CUSTOMER and APP_TGT_DEV.CUSTOMER_TGT tables. Additionally, we add a column to the APPSRCDEV/PRODUCT table.
Procedure for PLANNED DDL CHANGE:
Deployment in Development environment
1. Stop the development subscription
First, we stop the CM_DEV development subscription.
2. Implement the DDL change in development
Execute the DDLs to make the table structure changes (or recompile the DDS).
At the source:
SQL> ALTER TABLE APPSRCDEV/CUSTOMER ALTER COLUMN NAME1 SET DATA TYPE CHARACTER (80)SQL> ALTER TABLE APPSRCDEV/CUSTOMER ADD COLUMN UPD_DATE DATE NOT NULL WITH DEFAULTSQL> ALTER TABLE APPSRCDEV/PRODUCT ADD COLUMN UPD_USER CHAR (10) NOT NULL WITH DEFAULTAt the target:
SQL> alter table app_tgt_dev.dbo.Customer_tgt alter column name1 varchar(80)SQL> alter table app_tgt_dev.dbo.Customer_tgt add upd_date datetimeSQL> alter table app_tgt_dev.dbo.Customer_tgt add upd_timestamp datetime3. Update the source- and target table definitions in development
Update the source table definitions (screen captures only shown for one of the tables). This is to make CDC aware of the structure change so it can correctly interpret the journal entries.
…and do the same for the target table.
4. Adjust the mappings in the development subscription
CDC has now updated the table definitions, both on source and target. The next apply the mapping changes for the modified columns.
The statuses of the tables have been set to Parked and the modified or added columns have been remapped. We mapped the &TIMSTAMP journal control column to the new UPD_TIMESTAMP column on the CUSTOMER_TGT table. For the PRODUCT table, the new column is not mapped to any target column.
For simplicity, we’re assuming that in the development environment, the tables are refreshed.
5. Export the development subscription
Finally, the subscription is exported to an XML file so that it can be imported in the production environment later.
Deployment in PRODUCTION environment
For deployment of the table changes in the production environment, we're taking into consideration that a refresh of the altered tables must be avoided and no transactions should be lost or duplicated.
If refresh of tables must be avoided, making DDL changes to multiple replicated tables without the need to refresh these tables is supported under the following conditions.
• Replication for the tables in question is and remains active up to the point where the first DDL change is made
• DDL statements are not intermixed with DML statement for the replicated tables
Note: It is important that for the production environment, the sequence of steps in this procedure is respected. If DDL changes are done to a source table without first having processed all entries in the database log, the only possible recovery is to refresh the table in question.
In production, the old table definitions are still in place and replication is active.
Note: This procedure is focused on keeping the downtime for the replication as minimal as possible. Therefore, the update of the target table definition and the import of the new subscription have been kept as one of the last activities. It also allows for automation of the change management process from the production (iSeries) side.
1. Stop activity on the tables to be altered
First, make sure that the application has stopped so that no more transactions are generated on the table for which the structure changes.
2. Apply any pending transactions (net change)
Now, stop the subscription and restart in Net Change mode.
This is to ensure that all the transactions up to the DDL change have been applied to the target tables. When the subscription stops, the replication is up to date for the CUSTOMER, and PRODUCT tables and the DDL changes can be made.
When checking the event log, you see that the subscription started and subsequently ended normally.
The alternative to this Management Console action would be to run the following command on the iSeries server:
==> CHGCURLIB DMIRROR
==> STRDTAMIR TARGET(CM_PRD) IDLE(*END)
3. Implement the DDL change at the source
Execute the DDLs to make the table structure changes.
At the source:
SQL> ALTER TABLE APPSRCPRD/CUSTOMER ALTER COLUMN NAME1 SET DATA TYPE CHARACTER (80)SQL> ALTER TABLE APPSRCPRD/CUSTOMER ADD COLUMN UPD_DATE DATE NOT NULL WITH DEFAULTSQL> ALTER TABLE APPSRCPRD/PRODUCT ADD COLUMN UPD_USER CHAR (10) NOT NULL WITH DEFAULTIn many customer environments, a change management solution is in place to apply the tables changes into the production environment. Typically the following (or similar) steps are executed for each table that is being altered:
− Move physical file and dependent logical files to a temporary library
− Create new physical file in the production library
− Copy the data back to the new physical file (and/or run conversions)
− Rebuild the dependent logical files in the production library
− Start journaling on the physical file
4. Update source table definitions
Ensure that the table definitions have been updated for the datastore (screen captures only shown for one of the tables). This is to make CDC aware of the structure change so it can correctly interpret the journal entries.
Now, update the source table definition for the table.
The alternative to this Management Console action would be to run the following commands on the iSeries server:
==> CHGCURLIB DMIRROR
==> READDTBL TABLE(CUSTOMER) LIB(APPSRCPRD) ==> READDTBL APPSRCPRD/PRODUCT
5. Mark table capture point for mirroring (optional)
The last step before resuming business application activity on the CUSTOMER and PRODUCT tables is to set the tables to active. This is done by selecting the table and choosing the “Mark Table Capture Point for Mirroring” option.
The alternative to this Management Console action would be to run the following commands on the iSeries server:
==> CHGCURLIB DMIRROR
==> SETMIRSTS TARGET(CM_PRD) TABLE(APPSRCPRD/CUSTOMER) STATUS(*ACTV) ==> SETMIRSTS TARGET(CM_PRD) TABLE(APPSRCPRD/PRODUCT) STATUS(*ACTV)
6. Resume business application activity
Now the business application can resume activity on the CUSTOMER and PRODUCT tables; the journal entries are written in the table’s new format and will also be collected by CDC in the correct format.
7. Implement DDL changes at the target
Execute the DDLs to make the target table structure changes.
At the target:
SQL> alter table app_tgt_prd.dbo.Customer_tgt alter column name1 varchar(80)
SQL> alter table app_tgt_prd.dbo.Customer_tgt add upd_date datetime
SQL> alter table app_tgt_prd.dbo.Customer_tgt add upd_timestamp datetime
Then, update the target table definition. As the PRODUCT_TGT table did not change, its definition does not need to be updated.
C:\Program Files\IBM\IS CDC SQL\bin>dmreassigntable -I app_tgt_prd -t APPSRCPRD.CUSTOMER -s CM_PRD
Command for APPSRCPRD.CUSTOMER successfully executed.
8. Deploy the new version of the subscription
Now, import the new version of the subscription that was exported from the development environment into the production environment.
Choose to replace the existing CM_PRD subscription. Also, because the source library is different for the production subscription, we need override this.
After the import completes, new definitions have been applied.
Note: In this example, the deployment of the changes into the production environment has been implemented using export/import of the subscription. Alternatively, the changes could also have been applied by manually making the adjustments in the CDC configuration.
9. Resume replication
Finally, the production subscription can be started again; this causes any transactions done after the marking of the table capture points to be replicated to the target side.
Additional Information for UNPLANNED SCENARIOS:
1) Moving an in-scope table to a different library
Some automated change management solutions will move the old table (physical file) to a different library to allow copying of the data back into the new table. If an in-scope table is moved to a different library, CDC picks up the journal entry and cause the table to be parked. Replication continues for the other tables in-scope. Please note that the new version of the table will not automatically be replicated once it has been created.
A message for every parked table appears in the event log.
2) Ending journaling for an in-scope table
If journaling is ended stopped (ended) for a table that is in-scope, CDC reads the journal entry indicating the ENDJRNPF command and park the table. Replication continues for the other tables in-scope. Note that the table will not automatically resume replication once journaling has been restarted.
A message for every parked table appears in the event log.
[{"Type":"SW","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSTRGZ","label":"InfoSphere Data Replication"},"ARM Category":[{"code":"a8m0z000000cws0AAA","label":"Db2 for i"}],"ARM Case Number":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Version(s)"}]
Product Synonym
IIDR for Db2 for i
Was this topic helpful?
Document Information
Modified date:
02 July 2023
UID
ibm16448772