Poor Server Job Performance

[jpr196]

Hi All,

We are seeing consistently poor performance in our server jobs loading to DRS stages. Our database is db2. Poor performance is defined as ~25-500 rows/sec depending on table size. We manage 3 other environments and all perform much better. Of course, datastage is being blamed for not being configured properly, but our configuration is consistent across our environments. I've tried to isolate the problem and have generally found that reading from database to sequential file gives us good performance and that writing to the database is the holdup. I've also found that using link partitioner and multiple links on update statements dramatically increases our performance. However, trying the same strategy on inserts results in extremely slow performance or jobs failing due to what appears to be locking issues.

Anyone have ideas as to what can be holding us back?

[jpr196]

Some more information. When writing to a sequential file, we are seeing performance upwards of 30000 rows/sec. Do you think this is an Ascential Configuration problem or Database issue? To me, it appears to be database but I don't know what to suggest to them for configuring the database to work better with Ascential.

[ray.wurlod]

It's database. You proved that with write speed to sequential file.

Have you considered using bulk loader for your inserts?

[vivekgadwal]

Also, try adjusting the array size and transaction size. They play a big part while insert/update actions. Too high of a transaction size would definitely be an issue as it slows performance in the DB itself!