Job that is struck in starting for longer duration
Hi All,
I have a job that transfers records from Oracle to MS SQL. I have put in a customized query in the input component. I tried running the job and it stays in the 'starting' phase I couldn't see any records being transferred nor any errors. I started the job yesterday around 19:30 and till now (11:20 ) it still remains the same. I tried killing the job and re-run it. But the issue remains the same whereas for transferring other queries its working fine.
Any setting to be changed in the input component side? Kindly advice on this further. Thanks in advance.
The query is a customized one involving sub query with around 7 tables(which includes 2-3 transaction tables ) in it. When I try to execute the query in Toad it results in "End of file communication channel" so I'm not sure of the record count but it will be in millions. But similarly, there were other queries too that result in "End of file communication channel" when I execute in Toad and when I design the job in Talend( the similar way as of this job) and run it, it was successfully done. Kindly advice!
@sowmi333 ,Since the job is executing form Talend ,can you check with DBA, what is happening in background of DB side,is any request come from talend to extract data to DB?
The issue seems to be with query itself. The first rule of writing a query is that you need to analyse its performance. Did you check the execution plan of your query and analysed the cost before executing it?
Please also verify whether it is using right indexes for fetching the data. There might be full table scans happening since you are joining 7 tables in one shot. I normally join 2 or 3 tables in a query and break the original functionality to two or three logical groups. In your case, the volume of lookup tables also might be creating problem.
If you are not able to analyse the query by yourself, the next person to contact is Oracle DBA as they have advanced tools like ADDM, Tuning Advisor etc.
Warm Regards, Nikhil Thampi
Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂
Adding my bit to this:
I think more ETL approach would be , get dimension table ( more or less static tables )data in local machine and do your join with transactional data in Talend it self.
As transactional data usually as time dimension,See if you can parallelise these using talend components
With your approach, you have relying on remote Oracle server to do everything , it is usually approach if you are sql expert.
