I want to get the next sequence for x records coming from the I/P source . I need to get X new Sequence values from oracle.
But for a given record I have to Put in two tables,same set of records with the same sequence number.
this is easily possible in Server by using oracle as refernce link to the Transformer so for X records I get X sequence numbers
Can you let me know how to acheive the same thing in Parallel. I am not able to acheive it through the Lookup stage , possibly i am mising some trick....Eagerly awaiting replies and
Can you let us know what problem(s) you are facing?
As long as you use the same column (row) value into 2 outputs there cannot be anything changing them - even though the records can be repartitioned in the next few stages.
You can start by making the 'Execution Mode' for the lookup stage as 'Sequential' in your design. This will force PX not to run it in parallel and work similar to Server job.
Also you need to declare the 'Lookup Type = Sparse' in case of Oracle.
Priviously I tried with Oracle sequence to get sequence values using oraoci plugin and LookUp stage. For all the input records I am getting the same sequence number. I missed some where.
I am also looking for a way to use oracle sequence.Any body please advice.
But for the time being I did with using Surrogate Key generator by passing parameter as a initial value.
You can start by making the 'Execution Mode' for the lookup stage as 'Sequential' in your design. This will force PX not to run it in parallel and work similar to Server job.
Also you need to declare the 'Lookup Type = Sparse' in case of Oracle.
but it is giving me an error
LKP_GetNewNumbers,0: Null in field "CUST_ID" .The CUST_ID column is getting me the sequence number from Oracle....
When I make 'Lookup Type = Sparse' , It does not ask me for any derivations for the column (CUST_ID) which I think is right.
Possibly I am still missing some settings please let me know on the same...
This option is not encouraged, as it eat up your performance dramatically.
How to get surrogate key going correctly -
Use a Surrogate Key Stage, match via lookup with the maximum key from the table (along with a dummy field as key), and do the calculation within a transformer stage (key value + max value + 1).
You can also use a Column Generator stage (Integer, partval, partno - if you don't understand this, stick with the Surrogate Key Stage or ask around and someone will explain further.
It is not encouraged that you do this sequentially unless you must identify specfic records, and even then, you are better off using multiple transformers than to do it sequentially.
I want to get the next sequence for x records coming from the I/P source . I need to get X new Sequence values from oracle.
But for a given record I have to Put in two tables,same set of records with the same sequence number.
this is easily possible in Server by using oracle as refernce link to the Transformer so for X records I get X sequence numbers
Can you let me know how to acheive the same thing in Parallel. I am not able to acheive it through the Lookup stage , possibly i am mising some trick....Eagerly awaiting replies and
Please read a previous posting on how to generate a unique sequence number within a PX job
