Talend ,Oracle ,tOracleOuptut , auto incrememt , sequence

[Mehdi_Mhedhbi2]

hello, I'am using Talend integration studio version 7.2 to implement a datawarhouse on an Oracle 11g Database
I will use a simple scenario to explain my 2 problems:
I have a source table "client " from which I'am going to create a dimension table Dim_client as you all know in a datawarhouse dimension we add an auto increment column as a surrogate key

since in oracle 11g, I do not have the option to create an auto increment column in the database, I found myself with these three options :

option 1: create a Java sequence in tmap
option 2: creating a sequence in the oracle database with a trigger
option 3: create a sequence in the database and use it in tOralceOutput

my problem is with the third scenario

i have a :
***client as source table with three columns (code, prenom, age).
code prenom age
c1 mehdi 26
c2 raed 24
c3 nada 25

***the dimension table will be Dim_client with 4 columns (key_client, code, prenom, age)

I want to insert or update data from the source table to the dimension table based on the column code as my business key, so I did it using toracleOutput component.
-I created an oracle sequence for the surrogate key: creates sequence seq_dim_client minvalue 1 start with 1 increment by 1;
-I'm using two Java components to check the number of inserted and updated rows

-in the first exectution everythinhg is going fine  and dim_client is populated with three rows .

key_client code prenom age
1 c1 mehdi 26
2 c2 raed 24
3 c3 nada 25

but when I re-execute the same job without changing anything something weird happened, the numbers of the key_client changes from 1,2,3 to 4,5,6 and the number of updated lines = to 3 .

I understand that even when nothing is changed in the source table tOracleOutptut is doing an updated, which explain the changes on the key_client

key_client code prenom age
4 c1 mehdi 26
5 c2 raed 24
6 c3 nada 25

can  someone validate my hypothesis and tell me what is the solution for this specific case ? 

THX 

[Anonymous · Accepted solution]

This is an interesting problem and a pain that I have had in the past. When you get used to working with autoincrements and then you use a DB without them, it really can be a pain. Using an Oracle Sequence is a good way of getting round this. But I think you need to tweak your DB component's advanced settings. You have your "additional columns" configured to add your sequence BEFORE CODE. Can you change this to REPLACE to KEY_CLIENT? Then in the "field options" below, configure ALL columns (KEY_CLIENT, CODE, PRENOM, AGE) and set them up as you want them to behave for an INSERT and UPDATE. So, the only change I think you'll need is to add KEY_CLIENT and set it to not be changed on UPDATE (and maybe should the update key be this field too?).

[Mehdi_Mhedhbi2]

Thank you very much for your help ,your solution works fine for me, i had just to add the key_client in the schema and use it replace  like you said and make it not updatable. .

Actially i tried many things 
1) i used triggers with sequence. 
2) sequence only (your solution) 
3) tOracleSCD type 1 since I'm familiar with the concept of slowly changing the dimension and a sequence. 


the problem I am facing with your solution and the tSCD one is the performance, my job is not very complicated i am dowing two joins in tmap but when executing I'm only getting:
- 200 rows per second with your solution which keeps getting smaller every time until 15 rows/s.
-and 10  rows/s with tSCd.
I tried to play XMS and XMX values but with no result knowing that my dimension is big 400 000 rows
do you have any suggestion please? i'm using Talend Enterprise edition