DB2_UDB_API stage performane variations

[suresh_dsx]

Hi,

Code: Select all

We have sequence job. Running very slow.

First_job --> Second_Job

First job--> delete all the rows based on 4 columns (Col1,Col2,Col3,Col4) in a DB2 Table(using DB2_UDB_API stage)

Job design
Dataset1---> DB2_UDB_API stage 

Second job--> Insert/Update rows based on 4 columns (Col1,Col2,Col3,Col4) in a DB2 Table(using DB2_UDB_API stage)

Job design
Dataset2---> DB2_UDB_API Stage

• Total number of records is -700012
  First run-> The job completed in 8 mins and second job completed 12 mins. --> Total process First job and second job is 20mins
  Second time i have rerun the job with the same file ->The job completed in 7 mins and second job completed 10 mins. --> Total process First job and second job is 17mins
  Third time i have rerun the job with the same file ->The job completed in 12 mins and second job completed 15 mins. --> Total process First job and second job is 27mins
  
  
  I am unable to predict the performance of the job. When i run the same job 3 days ago, the sequence completed with in 8 mins. Now it is taking 2 times more time.
  
  Based on the forums, I have cross checked indexes, those are same as old. Still unable to figure out why performance have lot of variations
  
  Any help greatly appreciated.

Thanks -suresh

[ppgoml]

suresh,

there are two points you could have a check.

1. the load of target database or ETL server during your runs might be different.
2. if you have indexes on target table, the performance may be different when the data volume of target tables are different.