Configuring DML Command Substitution at Classifier Level

To configure a DML command substitution at the classifier level

  1. From the Transformation View, select the transformation, and then select the mapping line between the required classifiers.

  2. In the Properties pane on the right, under DML Command Substitution, a list of source and target commands are displayed.

  3. In the left column, select the source command and in the right column, select the required target command from the drop-down list.

    Note: By default, all the target commands are set to <Inherit>. It inherit the commands from the transformation level

  4. At the classifier level, you can set conditional command substitution for the target command.

    To set a substitution condition

    1. Click the filter icon () displayed on the right side of the target command.

      The DML Command Substitution Condition window appears.

    2. To set a substitution condition, either double-click the source variables and then the functions from the list, or manually drag or type the source variables and then the functions as required.

      By default, the filter icon is gray in color. After a filtering condition is set, the color of the filter icon changes to green with a green tick.

      Setting a substitution condition is similar to setting a filtering condition at the classifier level. For more information about the process of setting a filtering condition, refer to Setting Classifier Filter Conditions

The following table displays the supported source commands for each data store type and the available target commands that can be mapped to it.

Table 9. DML Command Substitution at Classifier Level for Enterprise Database Server as Source

Target Data Store Type
SQL Server DatabaseOracle Database
Source commandsAvailable Target commandsSource commandsAvailable Target commands
Create

Insert

Update

Delete

Merge

<Ignore>

<Inherit>

<Default>

Create

Insert

Update

Delete

Merge

<Ignore>

<Inherit>

<Default>

Modify

Insert

Update

Delete

Merge

<Ignore>

<Inherit>

<Default>

Modify

Insert

Update

Delete

Merge

<Ignore>

<Inherit>

<Default>

Delete

Insert

Update

Merge

<Ignore>

<Inherit>

<Default>

Delete

Insert

Update

Merge

<Ignore>

<Inherit>

<Default>


Table 10. DML Command Substitution at Classifier Level for RDMS as Source

Target Data Store Type
SQL Server DatabaseOracle Database
Source commandsAvailable Target commandsSource commandsAvailable Target commands
Insert

Insert

Update

Delete

Merge

<Ignore>

<Inherit>

<Default>

Insert

Insert

Update

Delete

Merge

<Ignore>

<Inherit>

<Default>

Update

Delete*

<Ignore>

<Inherit>

<Default>

Update

Delete*

<Ignore>

<Inherit>

<Default>

Delete

<Ignore>

<Inherit>

<Default>

Delete

<Ignore>

<Inherit>

<Default>


*The target command Delete is available only when the target has a primary key, and a source primary key is mapped to this primary key.

Table 11. DML Command Substitution at Classifier Level for SQL Server as Source

Target Data Store Type
Enterprise Database Server
Source commandsAvailable Target commands
Insert

Create

Modify

Delete

Merge

<Ignore>

<Inherit>

<Default>

Update

Create

Modify

Delete

Merge

<Ignore>

<Inherit>

<Default>

Delete

Create

Modify

Merge

<Ignore>

<Inherit>

<Default>