Hi,
I have a process to fetch the data from data base and write it to a file.
Requirement as follows
Fetch the data from MAIN table, for every record I need to have a lookup into another table to fetch some more data.
I have created a job as shown in the attachment. Problem here is in the lookup table I have huge data.
When I use query without condition it is working fine, but it is taking more time.
My question is how can I apply the condition that will fetch only required records from lookup table.
Example:
Working Example:
MAIN table query is as follows
"select SECURITY_NAME,SEDOL,CUSIP,ISIN,RIC,msci_security_code from MSCI_DNSA where ISO_COUNTRY_SYMBOL='"+(String)globalMap.get("output.IsoCountrySymbol")+"'"
Lookup table query is as follows
"select sec_code,ticker,exchange from SECURITYINFO"
Not working Example:
MAIN table query is as follows
"select SECURITY_NAME,SEDOL,CUSIP,ISIN,RIC,msci_security_code from MSCI_DNSA where ISO_COUNTRY_SYMBOL='"+(String)globalMap.get("output.IsoCountrySymbol")+"'"
Lookup table query is as follows
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+(String)globalMap.get("row3.msci_security_code")+"'"
Can you please suggest on this.
Thanks & Regards,
Syed
Hi Syed
Have a try to design the job as below and see if it has a bit improvement of performance,
......tOracleInput(msci_dnia)--row3--tFlowToIterate--iterate---tFixedFlowInput---main--tMap--main--tFileOutputDelimited
|
lookup
|
tOracleInput
on tFixedFlowInput: define the current iterate row
and now you can define the lookup query as below:
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+(String)globalMap.get("row3.msci_security_code")+"'"
Best regards
Shong
Hi Shong,
I have created the job as you suggested, but still same problem.
Lookup is not fetching the corresponding value. I think the globalMap value is not available to Lookup DB.
Please Suggest.
Thanks & Regards,
Syed
Hi Syed
Move the tMap component into a child job should be ok, for example:
father job:
......tOracleInput(msci_dnia)--row3--tFlowToIterate--iterate---tRunJob
child job:
tFixedFlowInput---main--tMap--main--tFileOutputDelimited
|
lookup
|
tOracleInput
on tRunjob: call the child job, pass the current iterate row to child job using context variables.
in child job, you can define the lookup query as below:
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+context.sec_code+"'" //context.sec_code is one of variables in child job.
Best regards
Shong
Hi Shong,
I have a complex job as shown in the attachment. Which can have many lookups.
My requirement should be in one job. Dividing it in many jobs will make the more complex.
The 'tRunJob' is not accepting subjobs, it requires only jobs.
Please suggest me how can I solve this within a job.
Hi
As your job showed, you should be able to get the value of one column of row1 and set the query as below on lookup component:
"select sec_code,ticker,exchange from SECURITYINFO where sec_code='"+(String)globalMap.get("row1.msci_security_code")+"'"
Best regards
Shong
