I have a mapping that insert/update in different targets and for one of those, it did not get updated. The only explanation I found is that the row was skipped during the process in a joiner. It should not happened because the query should have returned something but at the moment of the execution, maybe it was not the case. Let me explain :
A SQ (SQ1) reads source (say S1) with a filter on a Status = ‘P’ (for Processing). The mapping sends an update on the source to ‘C’ in a specific pipeline. And another SQ (SQ2) also reads from the same table with the same filter ‘P’.
SQ1 -------- Tgt (update to ‘C’)
JNR ---- Tgt2
So, here is the story that must have happened :
1. SQ1 returned the row. Status is still ‘P’
2. Rows are processed to the different targets and the status is updated to ‘C’
3. SQ2 is still running, or even just starts, and does not find any row with status ‘P’.
4. The joiner does not find a match.
I fooly thought that the process would wait for all SQ to return their results but it’s clear that as soon as one gets the results from the database, those rows are processed at once.
So, is there a way to force all SQs to run before processing any rows ?
Likewise, I have a lookup on the same source (with again status = ‘P’) and no value was returned from it, so same issue, status was already updated to ‘C’. I could move it at the beginning for example to force the creation of the cache so it’s easy to fix. For the SQ though I can’t find any way to handle this. And I can’t move the status update to ‘C’ somewhere else since I aggregate the records (differents keys, depending on the target), so I have to update to ‘C’ just after the main SQ (SQ1).
Also, my session has a Target commit type and commit interval of 10,000 but only 100 rows were processed so I thought the SQs wouldn’t have seen any update to ‘C’.
Thanks for reading till the end and thanks in advance for any help !
not sure if I could solve this using the Commit Type but I was thinking of this since it could be a way to prevent the target to be updated before the end of the run.
As per the documentation :
Target-based commit. The Integration Service commits data based on the number of target rows and the key constraints on the target table. The commit point also depends on the buffer block size, the commit interval, and the Integration Service configuration for writer timeout
Source-based commit. The Integration Service commits data based on the number of source rows. The commit point is the commit interval you configure in the session properties
My session has a target-based commit with 10,000 interval so I thought that informatica would commit every 10,000 for all targets. But is it working by target ? so, as soon as a target has received all of its records (like my target for the update to ‘C’), it’s gonna be commited ?
Is there any specific reason to use second source qualifier on same data set and look up?
If possible try to use SQL override in SQ1 to get all the required attributes.
thanks for your reply. Yes, there is a specific reaon : actually SQ1 is my main source (say Table1), I read my records to be processed (status 'P'). In SQ2 I read different sources but I join with Table1 in the sql override to limit the number of records pulled out Oracle to speed up the process because the other tables are pretty big.
What I could do basically is to remove this source from SQ2 but I have to consider the duration of the query.
I'd rather find a solution with the session settings (if any) because I don't understand why my target is updated before another SQ Reader is completed. But this commit type is kind of mystery to me...