cancel
Showing results for 
Search instead for 
Did you mean: 

Transaction Control Order on Oracle DB

Former Member
0 Kudos
161

Hi,

Does "Including in Transaction Flag" works, if you have same target tables, i.e., In one you do all Inserts, then Updates and finally Deletes operation.

I have this file with following format, basically it is transactional dummy data.

ID,OPCODE,NAME,STATE

1,I,SAM,OH

2,I,RAM,NJ

3,I,TOM,WI

4,I,LIL,IN

5,I,ABY,NY

6,U,TOM,CA

7,D,ABY,NY

8,I,PAM,VA

9,U,ABY,DC

I am trying to load this data in Target table say A. Here for OPCODE = "I", I have to generate primary key, while for OPCODE = "U" and "D", I have to either Delete for Update the Target based upon Natural Key, which in this case is column "NAME".

So I have used, a CASE transform after the Source, and I am branching out on OPCODE column.

Code:

First Branch ==> Key gen ==> Target A {Inserts Row here}

Second branch ==> Map Update ==> Target A {Perform Updates, if any}

Third branch ==> Map Delete ==> Target A {Perform Delete, if any}

Target A, same table dragged thrice, in the data flow!

For Second and Third branch, I have made "NAME" as my primary key, and have enabled "USE INPUT KEY" option at the TARGET table, for Update and Delete, and the flag "commit at end of insert...select" is checked for all the Target A table Instances!

I have also enabled "Include in Transaction" using following logic

Code:

First Branch ==> Key gen ==> Target A ==> Transaction Order : 1

Second branch ==> Map Update ==> Target A ==> Transaction Order : 2

Third branch ==> Map Delete ==> Target A ==> Transaction Order : 3

This is the correct output that it should generate:

ID,NAME,STATE

1,SAM,OH

2,RAM,NJ

6,TOM,CA

4,LIL,IN

8,PAM,VA

It only performs INSERTS operations, no Updates or Deletes. Is it because, I have same target table thrice, so even by enabling "Transaction Order" flag it doesn't really matter.

Or is it "Transaction Order" Flag works only if you have different Target table but from Same data store?

Technically, it should load all the data for Transaction Order : 1 first, then Transaction Order : 2 and finally Transaction Order : 3, right ?.

I am using Oracle 10g DB, with 11.7.3 on Win server 2003.

Is this a bug, or this logic can't be implemented in DI?

Any help will be appreciated!

Thanks,

Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

transactional order will control the order in which commit is done it will not serialize the DML statements

in this case, all the 3 operations will be happening in parallel, an update or delete can happen before insert of a row, resulting in 0 update or 0 deletes

you can try this outside DI by executing these statements from SQLPLUS in 3 parallel session, one session for insert, one for update and one for delete and do a commit in the order that you mentioned does this gives the expected output ?

Former Member
0 Kudos

Hi Manoj,

Thanks for the insight, but according to this URL:

https://wiki.sdn.sap.com/wiki/display/BOBJ/ControltheCommit+points

Trouble start when there are two or more target tables to be loaded. Imagine there is a master and a detail tables and you want to load both at the same time. If they do have a foreign key relationship it is paramount to load the master first and the detail later. But DI is a parallel pipelined engine, there is no guarantee the master gets its record first. Therefore DI has the transaction order flag.

We collect all SQL's inside memory and at the end, execute all the statements for the transaction order 1 first, once those records are loaded, take the SQL's for the second and at very end, one commit for the entier session.

So this is my understanding for above, you load all the data generated by Transaction Order 1 SQL,in my case all INSERTS and then load all data generated by Transaction Order 2 SQL, again all UPDATES and then at the end commit all.

According to above logic, it should work for my case?

So what you say, what am trying to do can't be implemented using Transaction Control Feature of DI?

I can serialize, the above design, into three different DF, as my last options!, but I have too many of them around 130 DF.

Thanks,

Edited by: data_guy on Sep 6, 2009 3:02 AM

Former Member
0 Kudos

then I might be wrong, let me check the behavior and correct way to achieve this in your case

can you try doing the following, instead or setting the transaction order for UPDATE and DELETE as 2 and 3, set that also as 1 (same as for insert), so that all these 3 operation become part of one single transaction

what is the exact version of 11.7.3, I remember fixing few issue around this in 11.7..3.6 and 11.7.3.7

Former Member
0 Kudos

one more thing, in your case if you have a column which can be used to identify Insert, update and delete then have you tried using MAP_CDC transform instead of case transform ?

Former Member
0 Kudos

I can't have same transaction sequence for all the three Target table, its same as not using it, and on the other hand can be used only if you have Real Time jobs.

My version is: Designer/Jobserver/jobengine : 11.7.3.2 and Repository is 11.7.0.0000 DB is Oracle 10g on UX.

And Regarding using Map_CDC transform, I do have at the input which specify if the given record will be I/U/D in column OPCODE.

But you see, when you use Map_transform, you need to have Primary_Key in the source, you don't generate New Primary key in the Target, but in my case I have to generate for Inserts, if am not missing anything, I don't think I can use it.

Heres a very nice article, which describes the scenario that I am trying to implement using DI, it is implemented using OWB. I think there is a bug in this DI version, or this logic can't be implemented using DI.

http://www.oracle.com/technology/products/warehouse/pdf/Cases/case5.pdf

Thanks,

Former Member
0 Kudos

the scenario that is mentioned in the link is nothing but Map_CDC transform in DI, you don't have put a case tranform and split your operation in 3 different streams, the logic is embedded in Map_CDC (by specifying the sequence column and opcode column)

I am not able to access my work env from home so can't check the options for Map_CDC and transaction control

you also mentioned that your key column for target is different from source and its a generated column which doesn't exists in source, in that case you can put a query before Map_CDC and generated the column for insert rows

but in any case whether you use Map_CDC or the other approch how are you going to get key column value for update and delete rows ? are you going to join your source and target table to get the key column ? or oding a lookup on target table by using some other column from source to get the key column ?

Former Member
0 Kudos

tried this scenario with 11.7.3.9 and its works are expected, as mentioned earlier there was a bug in this area

I tried this with SQL Server as target , it should work for Oracle as well, will verify that (in 11.7.2.3 the job hangs probably because of table locking for SQL server)

can you try using the latest fix pack for 11.7 ? (11.7.3.10)

before going for this approach, try with larger volume of data, since in this case the data will be buffered, I not sure what will be the implciation in terms of memory usage and performance

Former Member
0 Kudos

So did you try my approach on SQL server DB.

I mean with Case Statement, three branches I/U/D and then using Transaction control flag for all three target TABLE

Can you send me snapshot of the Monitor file, if its not a biggy?

I will try this using SQL server DB for target table instead of Oracle, and will see if its works!

Thanks for your help!

Former Member
0 Kudos

yes, I tried with the same design source -> Case -> 3 branches based on opcode (I/U/D) each loading to same table with tranction oder set as 1, 2 and 3

you can try with Oracle, shouldn't be a problem, I ran the job from command line so don't have monitor log (multiple DS env on same machine)

used the same dataset, the dataset is small, so try will a little bigger set of source data