Processing Mode Decision Logic

Overview

SQL Insert Modes and Effective Send Mode

This article explains how XDM determines the effective SQL insert behavior for tasks that apply data to a target database. The focus is on task types where data is applied row by row or by using SQL data transport. This includes Row Level Processor tasks, Row Level From Icebox tasks, Compatibility Table Copy tasks, Compatibility Table from Icebox tasks, and selected Native Table Copy scenarios where SQL data transport is used. The central point is that the technical send mode is not determined by the fill mode alone. The fill mode defines the intended behavior for applying source rows to the target, for example whether rows are inserted, merged, updated, or skipped. However, the actual execution path can also depend on the target DBMS, the task type, connection settings, table-specific rules, and runtime restrictions.

For this reason, the effective send mode should be understood as the result of several decision levels:

Decision level Result Typical influencing factors

Fill mode selection

Defines the intended apply behavior for the selected rows.

Global fillMode and table-specific overrides such as fillModeForTable.

Task and transport type

Determines whether SQL-based apply processing is relevant for the task.

Row level processor tasks, row level from icebox tasks, compatibility table tasks, and native table tasks with SQL data transport.

Target DBMS handling

Determines whether XDM can use a DBMS-specific path or must use a more generic SQL path.

Target database type, target database version, and DBMS-specific restrictions.

Batch decision

Determines whether rows can be sent in batches or must be processed differently.

Connection settings such as jdbcBatchActivated and jdbcBatchSize, table properties, column properties, and technical restrictions.

Runtime behavior

Determines the final execution behavior during the task run.

Errors, unsupported data types, constraints, LOB handling, or fallback behavior.

This separation is important when analyzing performance or task behavior. A task may be configured with a specific fill mode, but the SQL statements that are finally sent to the target database can still differ depending on the technical execution path. The article therefore describes the decision chain from the configured fill mode to the effective SQL execution mode. It is intended to help users understand why two tasks with similar fill mode settings may use different technical insert behavior, and where to check the configuration when the observed behavior differs from the expected behavior.

flowchart TD A["Task applies data to a target database"] --> B{"Is SQL-based apply processing relevant?"} B -- "No" --> C["Use task-specific transport or load behavior"] B -- "Yes" --> D["Determine effective fill mode"] D --> E["Select apply unit"] E --> F["Evaluate target DBMS"] F --> G["Select primary SQL processing path"] G --> H{"Can the selected SQL path be executed?"} H -- "Yes" --> I["Continue with selected processing mode"] H -- "No" --> J["Fallback condition detected

Possible reasons:
- JDBC batch cannot be used
- Table disables batch mode
- LOB or column restriction
- DBMS or JDBC driver limit
- DBMS-specific path unavailable"] J --> K{"Supported fallback path available?"} K -- "No" --> M["Report task error"] K -- "Yes" --> N["Fallback activated"] N --> O["Switch to supported alternative SQL path
for example non-batch SQL apply processing"] O --> P["Continue processing with fallback mode"] classDef fallback fill:#fff3cd,stroke:#b7791f,color:#000; classDef error fill:#f8d7da,stroke:#842029,color:#000; classDef ok fill:#d1e7dd,stroke:#0f5132,color:#000; class J,K,N,O,P fallback; class M error; class I ok;

Fill mode

The fill mode defines how XDM applies selected source rows to the corresponding target tables. It describes the intended behavior on the target data. For example, it defines whether XDM inserts new rows only, updates existing rows, merges source rows into existing target rows, or deletes matching rows from the target. The fill mode is used for row level processing tasks and for table copy tasks when SQL data transport is used.

For more information refer to Insert Data into the Target.

flowchart TD A[Source row must be compared with target table] --> B{Does the table have a primary key?} B -- Yes --> C[Use primary key as unique row identifier] B -- No --> D{Does the table have a unique key?} D -- Yes --> E[Use first unique key as unique row identifier] D -- No --> F[Use all columns except large object columns] C --> G[Compare source row with target rows] E --> G F --> G G --> H{Matching target row exists?} H -- Yes --> I[Handle as existing row] H -- No --> J[Handle as new or missing row]

Table-specific fill mode overrides

In some cases, one table must be handled differently from the rest of the task. For this purpose, XDM can use a table property named fillModeForTable. The property is set by a column property rule. To apply the property to a table, the column pattern of the rule must be empty. A typical use case is a start table in a Row Level Processor task. The table may be required to select related rows, but it may not be intended to change this table in the target. In this case, the task can use its normal fill mode for all other tables, while the start table is set to NONE by using fillModeForTable.

flowchart TD A[Start table processing] --> B{Is fillModeForTable set for this table?} B -- Yes --> C[Use table-specific fill mode] B -- No --> D[Use task-level fillMode] C --> E{Effective fill mode} D --> E E -- APPEND --> F[Insert rows with new unique row identifiers] E -- TABLE_REPLACE --> G[Clear target table and insert selected rows] E -- DELETE --> H[Delete matching target rows] E -- NONE --> I[Do not apply rows for this table] E -- INSERT --> J[Insert missing rows and discard key collisions] E -- MERGE --> K[Insert missing rows and update matching rows] E -- UPDATE --> L[Update matching rows and discard missing keys]

Fill mode versus generated SQL

The fill mode describes the target behavior that XDM should achieve. It does not always describe the exact SQL statement that is sent to the target database. For example, a configured MERGE fill mode means that XDM should insert missing rows and update existing rows. Depending on the target DBMS and its version, XDM may use different SQL strategies to implement this behavior.

The actual SQL execution can depend on several factors, including:

  • the target DBMS,

  • the DBMS version,

  • the task type,

  • whether SQL data transport is used,

  • table and column properties,

  • connection settings,

  • database restrictions encountered during execution.

Therefore, when analyzing task behavior, distinguish between the configured fill mode and the effective SQL execution mode.

Overall logic

The effective send mode is determined by several layers:

  • the task-level fillMode or a table-specific fillModeForTable override,

  • the target DBMS, including its version,

  • task and connection settings,

  • table properties and property rules,

  • runtime fallbacks in case of errors.

These layers are evaluated together. A task can therefore have the same configured fill mode but use a different technical send mode for different tables, target databases, or task executions.

Decision levels

Decision level Result Typical influencing factors

Apply unit selection

Determines which apply unit is used for the table.

Task-level fillMode and table-specific fillModeForTable.

DBMS-specific path

Determines whether XDM uses a DBMS-specific unit or a generic SQL unit.

Target DBMS, DBMS version, and DBMS-specific restrictions.

Batch decision

Determines whether batch processing is enabled or disabled for the table.

jdbcBatchActivated, jdbcBatchSize, table properties, column properties, and technical restrictions.

Execution mode

Determines whether statements are sent as single statements, JDBC batches, block statements, or utility operations.

Selected apply unit and runtime behavior.

An apply unit is the technical component that performs the selected apply operation, for example insert, update, merge, delete, or table replacement. The selected apply unit determines which send modes are technically possible.

Influence of the fill mode

The fill mode is the first input for the apply unit selection.

XDM starts with the task-level fillMode. If a table-specific override exists, the fillModeForTable table property defines the effective fill mode for the matching table.

As a result, two tables in the same task can use different apply units, even if the task has only one global fill mode.

For example:

  • A task uses MERGE as the global fill mode.

  • A specific table overrides this with fillModeForTable=NONE.

  • XDM does not apply rows for this table, while the other tables still use the merge-related apply path.

The selected apply unit then determines which technical send modes can be considered. For example, an insert-related apply unit can use insert-specific optimizations, while an update-related apply unit cannot use insert-only paths such as PostgreSQL COPY.

flowchart TD A[Effective fill mode is known] --> B{Fill mode} B -- NONE --> C[No apply processing for this table] B -- APPEND --> D[Insert-related apply unit] B -- TABLE_REPLACE --> E[Table replacement and insert-related apply unit] B -- DELETE --> F[Delete-related apply unit] B -- INSERT --> D B -- MERGE --> G[Merge-related apply unit] B -- UPDATE --> H[Update-related apply unit] D --> I[Evaluate target DBMS capabilities] E --> I F --> I G --> I H --> I I --> J[Continue with send mode decision]

Influence of the target DBMS

After the apply unit has been selected, XDM evaluates the target DBMS and its capabilities. Some SQL operations depend on native DBMS support. A native SQL MERGE statement can only be used if the target DBMS and the target DBMS version support it, and if XDM supports this path for the selected target type. If a target DBMS or DBMS version does not provide a suitable native operation, XDM must use another supported execution path or fall back to a more generic SQL strategy.

DBMS-specific implementations can also influence technical details such as:

  • statement syntax,

  • statement suffixes,

  • error handling,

  • discard handling,

  • return codes,

  • batching restrictions.

For this reason, the same fill mode can lead to different SQL statements or different send modes on different target database systems.

Influence of task and connection settings

Task and connection settings can enable, disable, or limit available send modes.

The most relevant connection settings for JDBC batch processing are:

jdbcBatchActivated

Controls whether JDBC batch mode is supported for the database connection. If it is active, XDM can bundle multiple rows and send them as a batch when applying data.

jdbcBatchSize

Controls how many rows are grouped into one batch unit when JDBC batch mode is active.

The configured batch size is a technical upper limit. The effective number of rows per batch or block can be lower if XDM must reduce the size because of table structure, DBMS restrictions, or runtime behavior.

For PostgreSQL insert processing, additional runtime properties can influence whether PostgreSQL-specific insert paths are available:

SubsetApplyPostgresCopyInsertUnitEnabled

Controls whether the PostgreSQL COPY insert path can be used.

SubsetApplyPostgresMultiValueInsertUnitEnabled

Controls whether the PostgreSQL multi-values insert path can be used.

deactivateLoggingBeforeApply

Can affect whether optimized PostgreSQL insert paths are available. If the optimized path is not applicable, XDM uses a normal insert path instead.

For more Information about Connections refer to Connections.

Influence of table properties

Even if batching is enabled on the connection, XDM may disable batch processing for a specific table or apply operation.

Typical reasons are table-specific or column-specific restrictions, for example:

  • large object columns,

  • XML or other special data types,

  • binary transport of character data by using transportCharacterDataAsBinary,

  • DBMS-specific restrictions for constraints,

  • restrictions of the selected apply operation.

For update processing, additional restrictions can apply because XDM must identify matching target rows and update them safely.

Batch behavior can also be controlled explicitly by table properties. These properties are set by column property rules with an empty column pattern.

enableApplyTableBatchMode

Forces batch processing for the matching table.

disableApplyTableBatchMode

Disables batch processing for the matching table.

Forcing batch mode can improve performance for large tables. However, it can also lead to errors if the table, the data, or the target DBMS does not support the required batch behavior.

PostgreSQL-specific insert paths

For PostgreSQL targets, XDM can use PostgreSQL-specific insert paths.

These paths are evaluated in the insert path. This means they are considered when the selected apply unit performs insert processing. Other fill modes can still result in insert processing internally. For example, a merge operation may insert rows that do not yet exist in the target.

The relevant PostgreSQL insert paths are:

Insert path Description

PostgreSQL COPY

Uses the PostgreSQL COPY path to apply rows. This is a utility-style insert path.

Multi-values insert

Uses one INSERT statement with multiple value tuples.

Normal insert

Uses regular insert processing. This path is used when the optimized PostgreSQL paths are not enabled or not applicable.

XDM evaluates whether the table is suitable for optimized PostgreSQL insert processing. Optimized paths such as COPY or multi-values insert are considered only if the relevant table and column properties allow them.

Typical restrictions include:

  • Object identifiers (OID)-related or larger objects (LOB)-related column types,

  • GENERATED ALWAYS columns,

  • task settings that prevent the optimized path.

If the table is suitable, the general selection order is:

  1. Use PostgreSQL COPY, if it is enabled and applicable.

  2. Otherwise, use multi-values insert, if it is enabled and applicable.

  3. Otherwise, use normal insert processing.

If COPY or multi-values insert is not possible because of configuration or table restrictions, XDM switches to normal insert processing.

Runtime fallback and log analysis

The planned send mode is not always the final send mode used for all rows.

If an optimized mode fails during execution, XDM can fall back to a more general execution mode. Typical fallback paths are:

  • from PostgreSQL COPY to insert-based processing,

  • from multi-values insert to individual insert processing,

  • from JDBC batch processing to non-batch processing.

These fallbacks are important when analyzing task performance or unexpected task behavior.

The task log can show both the initially selected apply unit and later fallback behavior. The following log message excerpts can help identify the selected mode and possible fallback paths.

Purpose Log message excerpt Level Meaning

Planned mode per table

ApplyUnits: …​ with entries such as Insert with COPY unit …​, Insert with multiple values tuples unit …​, or Insert unit …​

INFO

Shows which apply unit was planned for the table.

PostgreSQL COPY path prepared

Prepared COPY statement: …​

FINE

Indicates that the COPY path was prepared.

Fallback from COPY

Error during COPY operation: …​ Falling back to INSERT unit

INFO

Indicates that the COPY path failed and XDM switched to an insert-based fallback path.

Fallback from multi-values insert

Multi-value insert failed, falling back to individual inserts: …​

FINE

Indicates that a multi-values insert block failed and XDM switched to individual insert processing.

Optimized PostgreSQL path disabled by configuration

Using Copy for apply is not possible …​ Switching to normal insert apply unit…​ or Multi value inserts are not possible …​ Switching to normal insert apply unit…​

WARNING

Indicates that the optimized path was not used because of configuration or preconditions. This is not a runtime failure of the insert itself.

The ApplyUnits: entry is the most important starting point for identifying the initially planned mode. Fallback messages must be checked afterward to determine whether the planned mode was actually used for the complete table.

Some diagnostic messages are only visible with a more detailed log level. If the log level is too coarse, a fallback from multi-values insert to individual insert processing may not be visible in the standard task log.

Send mode decision logic

Insert path Description

PostgreSQL COPY

Uses the PostgreSQL COPY path to apply rows. This is a utility-style insert path.

Multi-values insert

Uses one INSERT statement with multiple value tuples.

Normal insert

Uses regular insert processing. This path is used when the optimized PostgreSQL paths are not enabled or not applicable.

XDM evaluates whether the table is suitable for optimized PostgreSQL insert processing. Optimized paths such as COPY or multi-values insert are considered only if the relevant table and column properties allow them.

Typical restrictions include:

  • OID related or LOB related column types,

  • GENERATED ALWAYS columns,

  • task settings that prevent the optimized path.

If the table is suitable, the general selection order is:

  1. Use PostgreSQL COPY, if it is enabled and applicable.

  2. Otherwise, use multi-values insert, if it is enabled and applicable.

  3. Otherwise, use normal insert processing.

If COPY or multi-values insert is not possible because of configuration or table restrictions, XDM switches to normal insert processing.

flowchart TD A[Task is executed] --> B{Is it a row level processing task?} B -- Yes --> C[SQL apply processing is relevant] B -- No --> D{Is it a table copy task?} D -- Yes --> F{Is SQL data transport used?} F -- Yes --> C F -- No --> G[Transport or load mechanism determines execution behavior] C --> H[Continue with fill mode and apply unit decision]

Runtime fallback and log analysis

The planned send mode is not always the final send mode used for all rows.

If an optimized mode fails during execution, XDM can fall back to a more general execution mode. Typical fallback paths are:

  • from PostgreSQL COPY to insert-based processing,

  • from multi-values insert to individual insert processing,

  • from JDBC batch processing to non-batch processing.

These fallbacks are important when analyzing task performance or unexpected task behavior. The task log can show both the initially selected apply unit and later fallback behavior. The following log message excerpts can help identify the selected mode and possible fallback paths.

Purpose Log message excerpt Level Meaning

Planned mode per table

ApplyUnits: …​ with entries such as Insert with COPY unit …​, Insert with multiple values tuples unit …​, or Insert unit …​

INFO

Shows which apply unit was planned for the table.

PostgreSQL COPY path prepared

Prepared COPY statement: …​

FINE

Indicates that the COPY path was prepared.

Fallback from COPY

Error during COPY operation: …​ Falling back to INSERT unit

INFO

Indicates that the COPY path failed and XDM switched to an insert-based fallback path.

Fallback from multi-values insert

Multi-value insert failed, falling back to individual inserts: …​

FINE

Indicates that a multi-values insert block failed and XDM switched to individual insert processing.

Optimized PostgreSQL path disabled by configuration

Using Copy for apply is not possible …​ Switching to normal insert apply unit…​ or Multi value inserts are not possible …​ Switching to normal insert apply unit…​

WARNING

Indicates that the optimized path was not used because of configuration or preconditions. This is not a runtime failure of the insert itself.

The ApplyUnits: entry is the most important starting point for identifying the initially planned mode. Fallback messages must be checked afterward to determine whether the planned mode was actually used for the complete table.

Some diagnostic messages are only visible with a more detailed log level. If the log level is too coarse, a fallback from multi-values insert to individual insert processing may not be visible in the standard task log.