generate Unique sequence numbers for each partition in session with Unconnected Lookup ?
Hi All,
Please help me to resolve the below issue while Applying partitioning concept to my Session.
This is a very simple mapping with Source, Lookup , router, and target.
I need to Lookup on the target and compare with the source data, if any piece of data is new then Insert, and If any thing change in the existed data then Update.
while Inserting the new records to the target table I'm generating sequence numbers with Unconnected lookup, by calling the maximum PK ID from the target table.
The above flow is working fine from last one year.
Now I wish to apply the Partitioning concept to the above floe(session)
At source I used 4 pass through partitions.(For Each partition different filter conditions to pull the data from source)
at Target I used 4 passthrough Partitions.
it is working fine for some data, but for some rows for Insert Operation , it is throwing Unique key errors, because while Inserting the data it is generating the same sequence key twice.
In detail : 1st row is coming from 1st partition and generated the sequence number 1 for that row.
2nd row is coming from 1st partition and generated the sequence number 2 for that row
3rd row is coming from the 2nd partition generated the sequence number 2 again for that row. (it must generate 3 for this row)
the issue is becuase of generating the same sequence numbers twice for different partitions.
Can any one Please help me to resolve this issue.
While Applying partitions how can I generate a Unique Sequence numbers from Unconnected lookup for Each partitioned data.
Regrads,
N Kiran.
- 2 Answers
- 6036 Views
- Tech Mahindra, I also Faced
- E-Mail Answers
Answers were Sorted based on User's Feedback
Answer / sufi
Most simple way to handle this type scenario is use DB Sequence Generator (write Stored Procedure) and Trigger on INSERT on top of it.
| Is This Answer Correct ? | 2 Yes | 0 No |
Hi,
Take one parameter (i.e. $$seq) and assign initial value for the unique key (i.e. 1000). Now, from Source Qualifier take all the ports to one expression transformation. Add two additional ports in the Expression Transformation as below.
v_seq (variable port) = IIF(v_seq>0,v_seq+1,$$seq)
SEQ_NO (output port) = v_seq
In this way, the Sequence transformation can be avoided but we will have the same facility in partition scenario as we are changing the port values only.
Though I believe it will work but please let me know if it is not.
| Is This Answer Correct ? | 0 Yes | 0 No |
i have flat file it contains 'n' number of records, i need to send half of the records to one target table and Remaining half to another target table: pls full details
8 Answers L&T, TCS, UnitedHealth Group, Wipro,
How do you remove duplicate records in informatica? And how many ways are there to do it?
Why we use ENABLE HIGH PRECISION in session properties?
i having source, router transformation, two targets in my mapping... i given two conditions in router 1)sal >500 2)sal < 5000 --------------- my source is havig two sal records (1)1000 (2)2000 then which target will load first? will both targets are get load or single target only get load...... why?
expain about the tune parameters?
What if i will group by with some port in aggregator and will not pass the sorted values. Will the session fails tell in both cases ( if i have configured it for sorted input and if i have not configured it for sorted input).
what is the max/min size allocated for caches.whether index or datacache in any of the transformations like Joiner or Aggregator.bcoz it is set to Auto by default which means we need not to allocate no of bytes or MB.so what is the use of these properties.please let me know about the caches. Thnaks in advance Sai Ram
Explain what are the different versions of informatica?
How to load only the first and last record of a flat file into the target?
How does a rank transform differ from aggregator transform functions max and min?
if i have a file having record sal=10000,and when use router with two groups having condition sal>5000 in one group,sal>7000 in other group.and if two groups connected to two targets.in which target sal=10000 will go?
9 Answers BirlaSoft, Genpact, L&T, Syntel,
i have source as Column1 101 a 101 b 101 c so i want to load target as column1 ---> 101 abc
Teradata 
