Steps to Develop a Mapping for Restart
Mapping objects such as targets, Java transformations, and SQL transformations can affect external files or database tables. When you run a mapping multiple times, these mapping objects can cause unexpected results in the external files or database tables. You must develop the mapping to remove any external effects from the previous mapping run before the mapping restarts in a recovered workflow.
For example, the Data Integration Service stops unexpectedly while a Mapping task with a restart recovery strategy is running. The mapping writes 50 rows to the target before Data Integration Service stops. When you recover the workflow instance, the Mapping task does not recover at the point of interruption and start writing row number 51. Instead, the mapping begins again.
Before the Data Integration Service restarts an interrupted mapping, you must manually remove the rows that the earlier mapping wrote to the target. Or, configure the mapping to remove the target rows.
Note: If you opt to manually remove the target rows, do not configure the workflow for automatic recovery.
Remove Target Rows Manually
If the mapping writes to a shared table that contains data that you need to keep, you can manually remove the rows written in the original run and then recover the workflow instance.
If the mapping writes to a table that a Human task reads, you must manually remove the target rows. Multiple Human tasks can write data to the same database table. If you configure the mapping to remove target rows, you might erase data from multiple mappings. Use the workflow instance ID to identify and manually remove the rows written in the original workflow run. You can locate the workflow instance ID in the workflow properties in the Monitoring tool. Or, you can locate the workflow instance ID in the output of the infacmd wfs startWorkflow and listActiveWorkflowInstances commands.
Configure the Mapping to Remove Target Rows
If the mapping writes to a file or a table that does not contain data that you need to keep, you can configure the mapping to remove all data from the target file or table. If the mapping writes to a shared table that a Human task does not read, you can configure the mapping to run an SQL command to remove the rows written in the original run.
Configure the mapping to use one of the following methods to remove target rows:
- Truncate a flat file target.
If you can remove all data from the target file, configure the mapping to truncate the target file before it writes to the file.
- Truncate a relational target table.
If you can remove all data from the target table, configure the mapping to truncate the target before it loads data.
- Run an SQL command on a relational target table.
If you need to keep some of the data in the target table, write an SQL command that the Data Integration Service runs to remove the rows written in the interrupted mapping run before it reads the source. For example, if the mapping writes sales data to a table that multiple departments share, write an SQL command that deletes all rows written for the Sales department. Or, if the mapping runs once daily, write an SQL command that deletes all rows written on the current date.
Configuring a Mapping to Truncate a Flat File Target
To support a complete restart of a mapping that writes to a flat file target, configure the write properties to truncate the target file before writing to the file.
1. Open the flat file data object in the editor.
2. Select the Write view.
3. Select the Input transformation.
4. In the Properties view, click the Run-time tab.
5. Clear Append if Exists.
6. Click File > Save to save the flat file data object.
Configuring a Mapping to Truncate a Relational Target
To support a complete restart of a mapping that writes to a relational target, configure the mapping to truncate the target before it loads data.
1. Select the Mapping task in the editor.
2. In the Properties view, click the General tab.
3. Click the name of the mapping that the task runs.
The mapping opens.
4. Select the relational data object in the editor.
5. In the Properties view, click the Advanced tab.
6. Select Truncate Target Table.
7. Click File > Save to save the mapping.
Configuring a Mapping to Run an SQL Command
To support a complete restart of a mapping that writes to a relational target, write an SQL command that the Data Integration Service runs to delete target rows before it reads the source.
1. Select the Mapping task in the editor.
2. In the Properties view, click the General tab.
3. Click the name of the mapping that the task runs.
The mapping opens.
4. Select the relational data object in the editor.
5. In the Properties view, click the Advanced tab.
6. In the Value column for the PreSQL property, click the Open button.
The SQL Query editor appears.
7. Enter an SQL command and then click OK.
The Developer tool does not validate the SQL.
8. Click File > Save to save the mapping.