Complex write modes and circular table references

There are some special considerations where your jobs use complex write modes to update a table.

InfoSphere® DataStage® connectors offer three complex write modes: INSERT-then-UPDATE, UPDATE-then-INSERT, and DELETE-then-INSERT. When the INSERT-then-UPDATE or DELETE-then-INSERT modes are pushed into a target database, Balanced Optimization generates two consecutive SQL statements to implement the complex operation. When the UPDATE-then-INSERT operation is pushed into a target database, Balanced Optimization generates a SQL MERGE statement, except in the case of Teradata databases earlier than Teradata V12, where two SQL statements are generated.

Where two SQL statements are generated, a circular table reference problem can occur. A circular table reference is where the source and target refer to the same database table directly, or indirectly though database views. When the Balanced Optimization tool detects a direct circular reference, that part of the optimization is abandoned with a warning, Balanced Optimization cannot detect an indirect circular reference, however. In such a case, you must use the optimization options to prevent this processing from being pushed into the target connector. Use one or more of the following options or properties:

Set the Name of a stage where optimization should stop property to the name of the Join or Lookup stage where the circular reference is introduced.
Turn off the Push processing to database targets, Use bulk staging to database targets and Push all processing to the (target) database options.

The circular table reference problem does not occur where an UPDATE-then-INSERT operation is pushed into an SQL MERGE statement.