topic Records missing in tOracleInput in Talend Studio

Records missing in tOracleInput

abarjun — Mon, 03 Sep 2018 07:39:10 GMT

Dear team

I had designed a talend job (in screenshot "Parent_job"), which has a sub job called DT_P_Oracle (in screenshot "Sub_job"). In the sub job I had used 4 tOracleInput where I had divided the month in where clause. Some records randomly missing in table (tOracleInput) read itself (once in a week) which is random in any one of the four sub jobs in screenshot "Sub_job". Please provide solution for this

note: all jobs were running parallelly

Re: Records missing in tOracleInput

fdenis — Mon, 03 Sep 2018 10:03:30 GMT

We do not have enough information to help you.
Can you give us the SQL queries with where clause.
tOracleInput read itself ?what do you mean?
What is your commit method?
I did not see any parallel configuration into your job?

Re: Records missing in tOracleInput

abarjun — Mon, 03 Sep 2018 11:44:53 GMT

Thanks for your reply,

I had attached tOracleInput query in "select Query.txt" file, where I had changed "dt.posted_date" values in tOracleInput_1, tOracleInput_2, tOracleInput_3, tOracleInput_4.

"tOracleInput read itself ? what do you mean?" -> I am trying to say that the records (nearly 80 Lakhs records of the total - 5 crores) were missed during the reading process in Oracle DB (tOrackeInput) sometimes. But most of the time, it works well.

"What is your commit method?" -> I had attached the screenshot "commit method".

"I did not see any parallel configuration into your job?" -> I had enabled multi-thread execution in the main job, and four sessions were opened at back-end at a time for reading data.

commit method.png

Re: Records missing in tOracleInput

fdenis — Mon, 03 Sep 2018 12:15:17 GMT

when SBITABLETRUNK had run DT_P_Oracle is runing
I think SBITABLETRUNK trunk table.
DT_P_Oracle is configured to run 4 threads. Is there a configuration to split the job? or are they doing the same job?
I think your problem is here!

Re: Records missing in tOracleInput

abarjun — Tue, 04 Sep 2018 07:32:31 GMT

There's no configuration to split the job except multi-thread execution, All the four threads were doing the same job with a difference in where clause's posted_date range (refer "select Query.txt" in previous attachment).

will there be any problem in multi-thread execution?

Re: Records missing in tOracleInput

fdenis — Tue, 04 Sep 2018 09:22:27 GMT

I prefer to use multi thread on tRunJob not inside job.
https://help.talend.com/reader/mhqCkTBnin7IXmJBUJoocQ/6iQsGrBmZWe~mxK9aTY7RA
in Your case you are runing 4 multiple join query's
to read data of the same table.
you cannot garanti unicity and fullfill of datas.

Re: Records missing in tOracleInput

abarjun — Mon, 24 Sep 2018 08:01:55 GMT

Hi Denis

I had split the four sub-jobs and used tParallelize with four tRunJob. still, 47 Lakhs records missing in the 2nd job (this time), do you have any other suggestions

Re: Records missing in tOracleInput

fdenis — Mon, 24 Sep 2018 09:34:41 GMT

can you check your queries?
is your db on production (with update insert on it)?