Active transformation which decides what type of transaction has to be loaded. (Is it commit or rollback?)
Transaction type can be defined at either Session Level or Mapping Level.
SESSION LEVEL
By default, transaction control is defined at Session level in the session properties.
 |
| Transaction Control Transformation In Informatica |
Commit Type can be Source,Target or User Defined.
Commit interval 10,000 (By default, 10000 transactions are committed)
MAPPING LEVEL
Transaction Control Transformation is used in a mapping to control transactions according to the user-defined conditions.
Use expression transformation to form the condition.
Built-in variables in the Expression Editor when we create a transaction control expression:
TC_CONTINUE_TRANSACTION. The PowerCenter Server does not perform any transaction change for this row. This is the default value of the expression.
TC_COMMIT_BEFORE. The PowerCenter Server 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 PowerCenter Server 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 PowerCenter Server 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 PowerCenter Server 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.
If the transaction control expression evaluates to a value other than commit, rollback, or continue, the PowerCenter Server fails the session.
Steps:
Define a mapping M_TC.
Drag Source EMP,Target DIM_EMP to the mapping designer.
Create a Transaction Control Transformation T_TC.
Project EMPNO,ENAME,JOB,SAL,DEPTNO from SQ to T_TC.
Project required ports from T_TC to the TGT.
Edit the Transformation T_TC and go to Properties Tab.
 |
| Transaction Control Transformation In Informatica |
Go to Properties Tab and select the Variables tab. Select the built-in folder
 |
| Transaction Control Transformation In Informatica |
Specify the condition in the expression editor.
IIF(DEPTNO=10,TC_COMMIT_AFTER,TC_ROLLBACK_AFTER)
If deptno is 10 then insert this record into TGT and commit the transaction after wards.Inserted record is in the commit transaction.
Otherwise, insert the record into TGT and rollback the transaction after wards. The inserted record is in the rollback transaction.
 |
| Transaction Control Transformation In Informatica |
Validate the mapping and save the repository.
Define a workflow. Assign Source and Target Connections.
Save the repository. Start the Work Flow and preview the output.
 |
| Transaction Control Transformation In Informatica |
Now let’s give this condition in the expression editor.
IIF(DEPTNO=10,TC_COMMIT_AFTER,TC_CONTINUE_TRANSACTION)
It means if deptno=10 condition is satisfied then insert the record and commit the transaction afterwards.
Else, continue the transaction.
In this case all 15 source records will get inserted into the target table.
 |
| Transaction Control Transformation In Informatica |
Log output (Very Important Observation)
USER-DEFINED COMMIT POINT Fri Aug 14 14:01:12 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 6 Applied: 6 Rejected: 0 Affected: 6
WRITER_1_*_1> WRT_8317
USER-DEFINED COMMIT POINT Fri Aug 14 14:01:12 2009
This means the records are inserted into a buffer till the time the record satisfying the condition deptno=10 is feteched into the buffer.
Once that record is fetched, it satisfies our condition.This is also the commit point for our transaction. So the transaction is committed after this record is fetched. All the 6 records go to target table.
The next record satisfying the condition is at position 8.
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 8 Applied: 8 Rejected: 0 Affected: 8
WRITER_1_*_1> WRT_8317
USER-DEFINED COMMIT POINT Fri Aug 14 14:01:12 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 13 Applied: 13 Rejected: 0 Affected: 13
WRITER_1_*_1> WRT_8317
USER-DEFINED COMMIT POINT Fri Aug 14 14:01:12 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 14 Applied: 14 Rejected: 0 Affected: 14
WRITER_1_*_1> WRT_8168 End loading table [DIM_EMP] at: Fri Aug 14 14:01:12 2009
WRITER_1_*_1> WRT_8143
Commit at end of Load Order Group Fri Aug 14 14:01:12 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 15 Applied: 15 Rejected: 0 Affected: 15
WRITER_1_*_1> WRT_8035 Load complete time: Fri Aug 14 14:01:12 2009
In the Session Properties the option Commit On End Of File is checked. So the last record which is mot satisfying the condition also gets inserted and committed.
 |
| Transaction Control Transformation In Informatica |
Uncheck the option Commit On End Of File and see the output
 |
| Transaction Control Transformation In Informatica |
 |
| Transaction Control Transformation In Informatica |
Only 14 records coming as output. Last record was not committed.
LOG OUTPUT
USER-DEFINED COMMIT POINT Fri Aug 14 14:28:25 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 6 Applied: 6 Rejected: 0 Affected: 6
WRITER_1_*_1> WRT_8317
USER-DEFINED COMMIT POINT Fri Aug 14 14:28:25 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 8 Applied: 8 Rejected: 0 Affected: 8
WRITER_1_*_1> WRT_8317
USER-DEFINED COMMIT POINT Fri Aug 14 14:28:25 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 13 Applied: 13 Rejected: 0 Affected: 13
WRITER_1_*_1> WRT_8317
USER-DEFINED COMMIT POINT Fri Aug 14 14:28:25 2009
===================================================
WRT_8036 Target: DIM_EMP (Instance Name: [DIM_EMP])
WRT_8038 Inserted rows - Requested: 14 Applied: 14 Rejected: 0 Affected: 14
transaction control transformation..
ReplyDeleteinformatica training