Wrong job behavior

[stephbzr]

Hello,

I don't understand the behavior of my job when a component group is activated. 

My job extracts data in tJavaFlex, they are sorted and stored in tHashOutput because I need to use this "output" as "input" several times in job. Then I try to transform them in my tMap. 

So far everything is fine with the tDBInputs. All lookup connectors are running correctly and I have 5025 rows in output. 

 

But when I activate the component group tHashInput - tMap - tDBSP with "Lookup" then all the "Lookup" are loading, but the final loading behavior is incorrect... it doesn't load 5025 rows anymore but an infinite number until reaching the Excel limit.

 

I don't understand then what to change. I made a test in an external job.

 

That's the only way it works, but obviously it's not what I'm looking for. I have several PL/SQL procedures to run and ideally everything should be concentrated around a single tMap... plus why does it take so long? How do the developers who have to use PL/SQL procedures and functions do it?

[Anonymous]

It looks like you are not joining your lookup which causes it to fail. Excel has a max number of rows of 1,048,576. Your main flow from your tHashInput is returning 209 rows. Your newly switched on lookup is returning 5025 rows. When you do not connect your tables inside a tMap, the number of rows are multiplied. 209x5025 = 1,050,225 rows. This is too much for an Excel file. This will be why it is failing.

[stephbzr]

Hello @Richard Hall​ 

Thanks for the explanation, but do you have any idea what I can change?

Because in run 1, my main flow (tHashInput - tMap) returns 5025 rows. So why if I activate flow 2 (tHashInput - tMap - tDBSP) which joins the tMap, flow 1 does not react the same way? I absolutely need the output of the tDBSP in my tMap. The two tHash entries have 5025 rows, I don't understand why Talend is multiplying these rows. 

[jlolling · Accepted solution]

To find the issue we should see the mapping of the tMap. I suggest you check the joins within the tMap and I guess there are some configuration of joins with Left Outer Join (or Inner Join) and as Match Model: All Rows. This potentially multiplies the rows.

[stephbzr]

Hello @Jan Lolling​ , thank you for your help.

Indeed I did not think to look at these parameters. 

I have 5025 lines in input of the two tHashInput and here is how my tMap is originally set up :

I tested several parameter combinations:

Inner Join - All rows : same result

Inner Join - All matches with key on rep_pdsqls because this column is already existing in tHashInput1 while the other column is an output of tDBSP from 

Left Outer Join - All matches : the result is the expected one

But I have another weird problem, after setting it like this, I have a warning panel with the message: 

The lookup table 'fnIndicHpds' should have at least one expression key filled.

Also, when I exit the tMap and go back in, the "Match Model" parameter is automatically set to "All rows".

[stephbzr]

Its ok for me 😀 Thank you !