The Integration Service provides exactly-once delivery of messages of real-time sources with the SQL transformation. A real-time message has to be delivered once to the SQL transformation. If there is an interruption in processing, the Integration Service can recover without requiring the message to be sent again. If the message is sent again, the Integration Service does not run DML statements from the message twice. The Integration Service might run other SQL statements such as SELECT or SET again.
To perform exactly-once processing, the Integration Service stores a state of operations for a checkpoint in the PM_REC_STATE table. Each SQL transformation has a separate state of operations. Each SQL transformation maintains a consistent state and does not share connections. You must have high availability for exactly-once processing.
Use the following rules and guidelines for recovering real-time sessions with SQL transformations:
You must set the transformation scope to Transaction to enable recovery for a sessions that contains an SQL transformation.
You cannot include auto-commit, commit statements, or DDL statements in SQL queries.
You cannot enable HA recovery for an SQL transformation if it is a passive transformation, or if you configured it for dynamic connections or for Script Mode .
A session may fail when you enable recovery for the SQL transformation and the workflow is enabled for concurrent execution or the session runs in multiple partitions. A database deadlock might occur on the PM_REC_STATE table.
ASK INFAPreview
