Performance Issue - identity column??
Posted: Thu Jan 05, 2006 2:20 pm
Hi
We have a performance issue with a job. The job design is like this. This job reads a sequential file of 30 columns and a transformer assigns System Date and Time for two columns and also assigns a string constant for another column. Then a look-up is done on the table TABLE1 (where the records are finally inserted) to check if they are new or existing. If they are new, the records are inserted into TABLE1. If they are existing, they are rejected. This is all the functionality of the job.
Here the extraction from the sequential file is done at a rate of 60000 rows/sec. But the insert into the target table is happening at a rate of 25-30 rows/sec. The insert and select from TABLE1 is done using DB2 API Stages. Also TABLE1 is having an identity column which generates surrogate key for each record internally within database. Also the partitioning in the insert DB2 API stage is set to AUTO.
Can anyone suggest why the job performance is very poor? Is it due to the identity column that creates bottle-neck?
We have a performance issue with a job. The job design is like this. This job reads a sequential file of 30 columns and a transformer assigns System Date and Time for two columns and also assigns a string constant for another column. Then a look-up is done on the table TABLE1 (where the records are finally inserted) to check if they are new or existing. If they are new, the records are inserted into TABLE1. If they are existing, they are rejected. This is all the functionality of the job.
Here the extraction from the sequential file is done at a rate of 60000 rows/sec. But the insert into the target table is happening at a rate of 25-30 rows/sec. The insert and select from TABLE1 is done using DB2 API Stages. Also TABLE1 is having an identity column which generates surrogate key for each record internally within database. Also the partitioning in the insert DB2 API stage is set to AUTO.
Can anyone suggest why the job performance is very poor? Is it due to the identity column that creates bottle-neck?