Oracle Business Intelligence Application Architect
☎ +91 9994883085
Don't miss

Monday, 9 September 2013

Transaction Control Transformation In Informatica


By on 03:08:00


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
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
Transaction Control Transformation In Informatica


Go to Properties Tab and select the Variables tab. Select the built-in folder 
Transaction Control Transformation In Informatica
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
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
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
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
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


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 

1 comments:

Blog Archive

Labels

1z0-482 Dumps (2) 1Z0-525 Dumps (1) BI Apps Installation (1) BI Publisher Interview Questions (1) BICS (2) Business Intelligence (1) DATA WAREHOUSE ADMINISTRATOR CONSOLE (18) Data Warehousing (48) E-BIZ R12 (1) E-BIZ R12 INSTALLATION (1) Essbase (1) hmailserver (1) Hyperion (1) Hyperion Essbase (2) Hyperion Essbase Interview Questions (1) Hyperion financial management (1) Informatica (163) Informatica Installation 9.6.1 (2) Informatica Interview Question (47) Informatica Online Training (1) informatica scenarios questions (1) Informatica Training (1) Informatica Training Chennai (1) JAVA (1) linux (1) Normalization in Oracle (1) OBIA 11.1.1.7.1 (1) OBIA 7.9.6.3 (3) OBIA Installation (33) OBIEE (2) OBIEE 10G (8) OBIEE 11G (138) OBIEE 11g dumps (2) OBIEE 11g Interview Questions (1) OBIEE 12c (20) OBIEE 12c Architecture (1) OBIEE 12C TRAINING (1) OBIEE certification (2) OBIEE Interview Question (1) OBIEE Publisher Interview Questions (1) OBIEE TRAINING CHENNAI (1) OBIEE TRAINING ONLINE (1) ODI (Oracle data integrator) (2) odi 11g certification (1) ODI dumps (2) Oracle Administrator (20) Oracle Business Intelligence Application (3) Oracle Data Integrator (2) Oracle Data Integrator Interview questions for experience (1) Oracle Database (3) Oracle Database 12c Installation steps (3) Oracle Database Installation in Linux 6 (2) Oracle E-Business R 12.3 (1) Oracle Hyperion Planning and Budgeting Questionnaire (2) oracle Joins (1) Power BI (5) RCU INSTALLATION (1) scorecard in obiee 11g (1) SQL (3) SQL Interview Questions (1) sql server 2008 (2) Sql server installation (1) WinSCP (1)