Using the command line to resume mirroring after a DDL change occurs

You can use the command line to resume mirroring after a DDL change.

Procedure

End all DML activity on all of the tables in your database that are used for replication.
This would mean stopping any applications that are writing to the tables. DML activity must be stopped prior to making the DDL changes to the database.
Ensure that there are no open transactions involving a table that will be affected by a DDL change.
One way to see if there are any open transactions is to check if there are any locks on the table.
```
SELECT * FROM SYSIBMADM.SNAPLOCK WHERE TABNAME='table' AND TABSCHEMA=’<schema>’
```
Where:

table

Specifies the name of the table.

schema

Specifies the name of the schema to which the table belongs.
End replication for all running subscriptions in the CDC Replication instance by issuing the following command:
```
ENDDTAMIR TARGET(SUBNAME)
```
Where:

SUBNAME

Specifies the name of the subscription where replication is to be ended.

You need to do the following:
- Stop all subscriptions at head of log using a controlled shutdown. The controlled shutdown must complete normally. Do not shut down immediate or shut down abort or otherwise stop the product during the controlled shutdown sequence. If the controlled shutdown does not complete normally, then a refresh must be performed to bring the table contents back in sync.
- Ensure that CDC Replication reached head of log or has no latency.
- Wait for all subscriptions to end replicating, including the ones that are only performing refresh.
Ensure that logging is configured for all DDL-affected source tables
Apply the DDL changes to all affected source tables in your database.
Update the source table definitions and describe the source tables by issuing the following commands:
```
./READDTBL TABLE(NAME)
./STRDTAMIR TARGET(SUBNAME)
```
Where:

NAME

Specifies the name of a source table in the subscription for which CDC Replication updates the definition.

SUBNAME

Specifies the name of the subscription.

The table status is reset to Mirror/Active.
If DDL changes have occurred on the target instance, update the target table definitions by issuing the following command:
```
./dmreassigntable -I instance -s subscription -t table
```
Where:

instance

Specifies the name of the CDC Replication instance.

subscription

Specifies the subscription that contains the source table that is mapped to the target table which was updated in your database. To specify multiple subscriptions, list the subscriptions separated by a space.

table

Specifies the name of a source table in the subscription that is mapped to the target table for which CDC Replication updates the table definition in the metadata. You must specify the table name in the format schema.table. To specify multiple tables, list the tables separated by a space.
Check the table mapping for correctness.

When DDL changes modify source columns, you must make sure that the target table can accommodate the new data format, for example, going from CHAR(1) to CHAR(10). You can determined this in the Details area of the Table Mappings view in Management Console.

If necessary, manually remap the changed columns and verify the new mappings. The auto-mapping feature maps identical column names. However, because auto-map is a best-effort algorithm, verify and adjust your mappings to suit your business needs.
Issue the following command to change the replication method to refresh for all affected subscriptions to temporarily mark the tables as out of scope:
```
./CHGREPMTD TARGET(SUBNAME) TABLE(LIBRARY/NAME) REPMTD(*REFRESH) UPDMTD(*KEYED)
```
Where:

SUBNAME

Specifies the name of the subscription.

LIBRARY/NAME

Specifies the library and name of the source table.

Note: If the affected tables resides in multiple subscriptions, you must change the replication method of all affected subscriptions to refresh before proceeding to the next step.
Issue the following command to change the replication method back to mirror for all affected subscriptions.
```
./CHGREPMTD TARGET(SUBNAME) TABLE(LIBRARY/NAME) REPMTD(*MIRROR) UPDMTD(*KEYED)
```
Where:

SUBNAME

Specifies the name of the subscription.

LIBRARY/NAME

Specifies the library and name of the source table.
Mark the capture point to avoid the refresh operation.
```
./SETMIRSTS TARGET(SUBNAME) TABLE(LIBRARY/NAME)
```
Where:

SUBNAME

Specifies the name of the subscription.

LIBRARY/NAME

Specifies the library and name of the source table.

This step marks the capture point to the head of the log.
If there is a requirement to perform a refresh of the table or if refreshing the table is the preferred means of recovering, you can flag the table for refresh and initiate the refresh operation.
```
./SETRSHSTS TARGET(SUBNAME) TABLE(LIBRARY/NAME)
./STRDTARSH TARGET(SUBNAME)
```
Where:

SUBNAME

Specifies the name of the subscription.

When the refresh has successfully completed, you can restart mirroring for the subscription.
Note: You must perform a refresh under the following conditions:
- Logs need to be re-scraped from the time before the DDL change was performed; that is, the restart position is before the DDL change.
- Tables are dropped and recreated.
Start mirroring for the subscriptions that were halted due to the DDL change. Issue one of the following commands:
For continuous mirroring:
```
STRDTAMIR TARGET(SUBNAME)
```
For mirroring with a scheduled end:
```
STRDTAMIR TARGET(SUBNAME) IDLE(*END)
```
Where:

SUBNAME

Specifies the name of the subscription.