I am facing a weird error since last 1 month of with 4 specific load jobs which are not dependent on each other. Lets consider them job1, job2, job3 and job4 which has the target table as T1, T2, T3 and T4.
All four jobs has file as the source and DB as the target using DB2 stage.
All four jobs are updating and inserting data into DB using DB2 stage and a lookup through transform stage.
Below is the jobs design:
Source --> Transform ----> stored_proc ---> DB2 target
The stored procedure is doing update and insert as per the the data coming from the source.
I am not sure if I have provided all the information, but earlier it was working fine and our load was completing before our Service level agreement.
But since last few weeks, load is exceeding SLA and taking lots of time to finish where above mention 4 specific jobs are taking around 1.5-2 hrs each to complete.
I have compared it with other tables and found that other tables are processing more than 1k records/second, and these 4 specific jobs are only processing 6-10 records/second which is a huge performance issue.
Could you please give me some suggestions how can I debug this performance issue.
FYI, I have checked the indexes and all necessary things at DB level, the only thing which is worth noting is that these four jobs/tables have much larger data as compared to other tables.
