Template parameters

This page describes the most frequent parameters available in the Properties view for the different types of templates available in Semarchy xDI:

Load template parameters

Property

Default Value

Description

Cdc Subscriber

empty

See CDC template parameters

Cdc Wait Mode

empty

See CDC template parameters

Cdc Wait Poll Interval

empty

See CDC template parameters

Cdc Wait Rows Number

empty

See CDC template parameters

Cdc Wait Timeout

empty

See CDC template parameters

Clean Temporary Objects

True

Select this option to delete the temporary objects created during a data load at the end of the process.

Lock Cdc Table

True

See CDC template parameters

Transaction Name

T1

Defines the name of transactions if Use Transation On Work Tables is selected.

Use Transaction On Work Tables

False

  • If this option is selected, operations on load tables will be executed in a transaction using the NOCOMMIT mode. A commit will be performed at the end of execution in case of success, otherwise, a rollback will be performed.

  • If this option is not selected, data will be inserted in the reject table in AUTOCOMMIT mode.

Unlock Cdc Table

True

See CDC template parameters

Use Distinct

False

Select this option to add a DISTINCT statement to the data load query to remove duplicate rows.

Stage template parameters

Property

Default Value

Description

Cdc Subscriber

empty

See CDC template parameters

Lock Cdc Table

True

See CDC template parameters

Unlock Cdc Table

True

See CDC template parameters

Create Stage Indexes

False

When selected, an index is created for each stage field tagged with the IDX<name> pattern.

The index name created is prefixed with this pattern. The tag must be added to the stage field directly within the mapping, from the stage field’s Properties view. For example, IDX_1.

Use Distinct

False

When selected, a DISTINCT statement is added to the subsequent query to remove duplicate rows.

Integration template parameters

Property

Default Value

Description

Append Mode

False

If selected, all rows are directly inserted in the target table. No updates are performed.

Clean Temporary Objects

True

Select this option to delete the temporary objects created during integration at the end the process.

Commit Transaction

True

If this option is selected, transactions are committed at the end of integration. This parameter applies only if Transactional Mode On Target is selected.

Create Index On Pre Integration Table

True

If selected, an index is created on the update key of the integration table to optimize the subsequent statements using this table.

Create Target Table

True

Defines whether the target table must be created before integration.

  • True: The target table is created. If it already exists, no error is raised.

  • False: The target table is not created.

  • Drop and create: The target table is dropped and re-created at each execution.

Create Indexes On Target Table

auto

Defines whether the indexes defined on the metadata should be created at the end of the integration. Possible values are:

  • auto: The indexes are created when Create Target Table is set to True, Create or Drop and Create

  • disable: Indexes are not created.

  • enable: Indexes are created.

Delete All Target Table

False

Replaced by Deletion Strategy. If this option is selected, all existing rows in the target table are deleted before the integration.

Deletion Strategy

disable

Defines the deletion strategy for the target table:

  • all rows: All rows are deleted from the target table.

  • where clause: Only the rows returned by the Deletion Where Clause are deleted.

  • key: If at least one column of the target table has a DELETE_KEY tag, all rows having matching values for this(these) column(s) are deleted.

If no column is tagged with DELETE_KEY, all rows matching the Update Key of the Mapping are deleted. * disable: No deletion is performed.

Deletion Where Clause

1=1

If Deletion Strategy is set to where clause, this parameter defines the WHERE clause that defines the rows to delete. Example: UPDATE_DATE < current_timestamp.

Do Insert

True

If selected, insertions are performed in the target table. This parameter has no effect if Integration Strategy is set to slowlyChangingDimension.

Do Update

True

If selected, existing rows in the target table are updated with new values. This parameter has no effect if Integration Strategy is set to slowlyChangingDimension.

Recycle Rejects

False

If selected, rejects created during the previous execution are inserted in the integration table.

Load

True

Defines whether the load step must be exectuted prior to integration. Unselect this option to disable the load step.

Incremental Method

insertUpdate

Defines the method used if Integration Strategy is set to Incremental:

  • insertUpdate: Modified rows are updated first, new rows are inserted afterwards.

  • merge: A MERGE statement is used to perform both insertions and updates simultaneously.

Integration Strategy

auto

Defines how data is integrated in the target table:

  • append: All source rows are directly inserted in the target table, no update is performed. The Incremental Method and Useless Update Detection Method parameters are ignored.

  • incremental: Integration is done based on the "Incremental Method and Useless Update Detection Method parameters. Existing records are updated with new values and new records are inserted in the target table.

  • slowlyChangingDimension: Integration is performed using the SCD method. This mode is used to manage the history of rows in specific columns configured in the target table’s Metadata.

  • auto: The strategy is defined automatically.

    • If the target table contains SCD settings on columns, the slowlyChangingDimension (SCD) strategy is used.

    • Otherwise, if columns used as keys are mapped, then the incremental strategy is used.

    • Finally, if no columns used as keys, the append strategy is used.

SCD Default End Date

NULL

If Integration Strategy is set to slowlyChangingDimension, this parameter defines the SQL expression used during insertions to populate the unmapped columns configured with Slowly Changing DimensionMode=endDate.

Transactional Mode On Target

False

  • If this option is selected, the operations on the reject table will be executed in a transaction using the NOCOMMIT mode. A commit will be performed at the end of execution in case of success, otherwise, a rollback will be performed.

  • If this option is not selected, data will be inserted in the reject table in AUTOCOMMIT mode.

Transaction Name

T1

Defines the name of transactions if Transactional Mode On Target is selected.

Synchronize Deletions From CDC

False

If selected, rows deleted from source tables configured with CDC are deleted from the target table.

Use Distinct

False

If selected, a DISTINCT statement is added to the query inserting data in the target table to avoid duplicate rows.

Useless Update Detection Method

flag

Defines how pre-integration data and data from the target table are compared to identify changes:

  • minus: A MINUS statement is used (potentially time-consuming for large tables).

  • flag: All rows are loaded in the integration table. An update is then performed to flag the columns as to be updated or to be ignored.

Reject template parameters

Property

Default Value

Description

Drop Reject Table

False

Select to drop and re-create the reject table before each execution.

Create Index On Reject Table

False

Select this option to create an index on the reject table for each primary and alternate key.

Delete Previous Rejects

True

Select to delete all rejects rows from previous executions at the beginning of each flow execution.

Delete Rejects On Checked Table

True

If this option is selected, rejected rows will not be inserted in the target table and will be inserted in the rejected table only.

This option applies only if the constraint’s Severity Level is Fatal or Error.

Create Index On Checked Table

False

Select this option to create an index on the checked table for each primary and alternate key.

Transactional Mode On Reject Table

False

  • If this option is selected, the operations on the reject table will be executed in a transaction using the NOCOMMIT mode. A commit will be performed at the end of execution in case of success, otherwise, a rollback will be performed.

  • If this option is not selected, data will be inserted in the reject table in AUTOCOMMIT mode.

Transaction Name

T1

Defines the name of transactions if Transactional Mode is selected.