Informatica Power Centre
allows us to control the roll back and commit on transaction based on set of
rows that passes through the Transaction
Control transformation. This allows to define your transaction whether it should be committed
or rollback based on the rows that pass through ,such as based on the Entry Date
or some other column.We can control this transaction either at the Mapping
level or Session level.
1)Mapping
Level:
Inside the mapping we will
be using Transaction Control transformation. And inside this transformation we
have an expression. Based on the return value of this expression we decide
whether we have to commit, roll back, or continue without any transaction
changes.The transaction control
expression uses the IIF function to test each row against the condition.
Use the following syntax
for the expression:
IIF
(condition, value1, value2)
Use the following built-in
variables in the Expression Editor when you create a transaction control
expression:
Expression
|
Description
|
TC_CONTINUE_TRANSACTION
|
The Integration
Service does not perform any transaction change for this row. This is the
default value of the expression.
|
TC_COMMIT_BEFORE
|
The Integration
Service commits the transaction, begins a new transaction, and writes the
current row to the target. The current row is in the new transaction.
|
TC_COMMIT_AFTER
|
The Integration
Service writes the current row to the target, commits the transaction, and
begins a new transaction. The current row is in the committed transaction.
|
TC_ROLLBACK_BEFORE
|
The Integration Service rolls back the
current transaction, begins a new transaction, and writes the current row to the
target. The current row is in the new transaction.
|
TC_ROLLBACK_AFTER
|
The Integration
Service writes the current row to the target, rolls back the transaction, and
begins a new transaction. The current row is in the rolled back transaction
|
2)Session
Level:
When we run a session the
Integration Service checks the expression ,and when it finds a commit row then it commits all rows in transaction to
the target table. When the
Integration Service evaluates a rollback row, it rolls back all rows in the
transaction from the target or targets.
Also we can do user defined commit here in case
Integration services fails to do so.
Also read about XML Transformation in Informatica
No comments:
Post a Comment