I have an BODS job which is loading from one table to the other table.
Both Source and target tables are oracle(oracle 11g).
The job was running fine from almost 6 months. but, now the job is failing after running for 30minutes with the error. connection lost issue.
source table has almost 1.5 crore records. after doing group by and some joins we are pulling only 15 lacks records into one table and around 7lakhs records into other table.
But, now the job is failing with the error.
(14.2) 08-01-16 02:39:24 (E) (10510:2426177280) DBS-070300: |Data flow DF_BW_PROFITABILITY_HYP|Loader Query_5_BW_REVENUE_CP SQL submitted to Oracle Server <(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = 10.99.57.8)(PORT = 1599)))(CONNECT_DATA =(SID=PHYP)))> resulted in error <ORA-03135: connection lost contact Process ID: 19399
We requested the DBA team to increase the connection timeout to 3 hours on both source and target side.
But, again no luck the job is failing with the connection issue.
I tried an option available in bods. transaction control and put the target tables to load one after the other(as earlier they were loading parallel).
Now the job is running successfully . It runs for almost 35 to 40 minutes.
But, i guess this is not the correct solution. can anyone please help me on this.
Can anyone please put an permanent solution for this issue.