Each type of CDC Connector supports recovery and restart processing. If a mapping task job fails or is stopped before the end of log, the Data Integration service in conjunction with the CDC client can use recovery information that is stored on the target system to resume the extraction of change records in the PowerExchange Logger log files from the point of interruption. This functionality prevents change data loss and inconsistencies between the source and target.
To enable recovery and restart processing, you must set the Recovery Strategy advanced session property to Resume from last checkpoint on the Runtime Options page when you create or edit a mapping task. With this recovery strategy, the Data Integration service provides for the recovery of target tables, and the CDC client and PowerExchange provide for the recovery of the CDC restart information that is used to resume extraction processing in the log files.
CDC restart information consists of PowerExchange-generated restart tokens from the source system. When you set the Recovery Strategy option Resume from last checkpoint, Data Integration stores CDC restart information in the following locations depending on the target type:
•For relational targets Db2 for i, Db2 for z/OS, Microsoft SQL Server, ODBC, and Oracle, the Data Integration service stores CDC restart information in the PM_REC_STATE recovery table in the target database. Data Integration automatically creates this PM_REC_STATE table on the target. For more information about recovery tables, see PowerExchange Interfaces for PowerCenter.
•For nonrelational targets, Data Integration stores the CDC restart information in state files in the shared location $PMStorageDir on the Secure Agent system.
For databases where the recovery tables are not automatically created, use the template script for creating the recovery tables:
•To replicate data to a target, you must create a recovery table on the target. To do so, you can modify the recovery_template.sql template provided in the PowerExchange root installation directory for the Linux, UNIX, and Windows installers. Update the template according to the target database requirements. For more information, see PowerExchange Interfaces for PowerCenter.
•To use recovery tables, set the Recovery Strategy advanced session property to Resume from last checkpoint on the Runtime Options page when you create or edit a mapping task. Set Commit on End of File to No.
In special situations, you can specify a restart point for a mapping task. Typically, the first time you start a mapping task, you specify a restart point that corresponds to the target materialization time so that no change records in the change stream are skipped. The default restart point is the end of log (EOL), which is the current point of CDC processing in the log. You can specify that extraction processing starts from the earliest available record in the log or from a specific date and time. When you use a time-based restart point, extraction processing starts in the log that contains the first unit-of-work (UOW) that has an end time later than the restart time.
When you specify a restart point, consider the following points:
•The restart point applies to all sources in the mapping that is associated with the mapping task.
•If you set a restart point that is too early, it might correspond to a expired log file. In this case, the PWX CDC Reader uses the earliest available record in the available log files.
•If you set a restart point that is later than the latest record in the log files, an error message is issued.
Note: Restart information is associated with a mapping task and a specific source and target combination. If you change the source object in a mapping, you must perform one of the following actions: create a new mapping task for the mapping, increment the restart revision number for the existing mapping task, or perform a cold start. To increment the restart revision number, navigate to the CDC Runtime page for the mapping task, open the Select Restart Point dialog box, and click OK. If you do not take one of these actions, the mapping task will fail the next time you run it.