How to reload a lookup table in a tmap after each iteration

[Anonymous]

Hi,
I'm trying to build an histtory table (tbHIST) thanks to a big table (TDR20) that contains full data for each days and a date dimension table.
The aim is too loop into the dim date table to retrieve a date that will filter the miror table (TDR20) and map it with the
target table to evaluate the input data and define whether it is a new row to add or an ins/upd.
The issue is that when a use the 'run the lookup table once' (look up table that in my case is also the target table (LKP_PREP_ENDBALANCE))
in the tmap component it's not refreshing my lookup table on the second day with the date inserted in the previous date and it's loading full day each time.
I tried to use the the (run each rows) but it's almost loading 20 rows by second because for each rows of the mirror table TOS is refreshing the lookup table fullyl which in my case it's not usefull as I need a refresh by date (iteration).
So my question is how to reload the tmap lookup table after each iteration how how to exclude the iteration from the main job
(I'm using TOS 3.1)
here is the line in the TDR20 input where I'm applying the date received by the dim_dates
AND TDR_DATE ='" + globalMap.get("row1.DATE_ORA") + "'

[Anonymous]

Hello
Have you select 'reload at each row' option?
Best regards

shong

[Anonymous]

Yes that's what I was saying in my first post.

I tried to use the the (run each rows) but it's almost loading 20 rows by second because for each rows of the mirror table TOS is refreshing the lookup table fullyl which in my case it's not usefull as I need a refresh by date (iteration).

the issue is that it reloads the lookup table for each row not for each iteration (loop)

[Anonymous]

Hi drari,
I encountered the same issue. I don't know if it's the best way, but I resolved it by creating 2 jobs.
I have a father job like: tOracleInput ---> tFlowToIterate ----> tRunJob, in which the tRunJob calls a child job. The child job contains all the components after the iteration.
With this structure, I'm sure that the lookup table is reloaded at each iteration. 😉
Hope this helps,
Best regards,
Karine

[Anonymous]

Hi Kboop,

I will be happy to try this, could you just explain me how i can retrieve the value of the dim date (that will be in the main job )
to push it in the second job that contains my tmap and at the same time to filter my tdr20 table with the retrived date?
thank you again
Drari
ps: is the joblet feature only available in TIS

[Anonymous]

Hello,
You can use this component, which can be use as a replacement for joblet :
http://www.talendforge.org/exchange/tos/extension_view.php?eid=155
I notice I had to correct the classpath, when I export the job as a POJO though.

[Anonymous]

To transfer the dim date to the child job, you can create a context parameter in the child job and use it in your TDR20 input:
AND TDR_DATE ='" + context.date + "'
Then, in the tRunJob settings, add the corresponding globalMap variable to this context param (see attached screenshot)
Best regards,
Karine

[Anonymous]

Karine,
thank you for your great help
But now I think I have another issue I'm not able to add a new parameter in the TrunJob component the '+' sign is gray
Drari

[Anonymous]

I think it's because you have to create the context parameter in the child job first, in the "Contexts" view.
Did you create it ?
Karine

[Anonymous]

yes you right I created in the child job and it's visible now.
Now it's working perfecty thank you for your great support.
Just a nice to have question
is it possible to see the subjob (called by the tRunJob feature) running when executing the main job?
Drari