Hi all,
We have a requireent to load about 60-70 million of records per day.Currently we are using OPTILOAD tool for this.Now we are considering to load it using Data Stage through DB2/UDB Enterprise stage.So just wanted to know about the fesability of this or if there are any benhmarks laid by IBM on how many records can be loaded per second using this stage.Currently OPTILOAD loads 2000 records/second.
thanks in advance.
Benchmark for DB2/UDB Enterprise Stage
Moderators: chulett, rschirm, roy
-
ray.wurlod
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
The main bottleneck will be how fast the DB2 direct write can handle the rows. You can automatically effect parallel loading into partitioned tables. 2000 rows/sec ought not to be difficult at all.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
I just recently did some performance enhancements on a job by changing some sort/joins into lookups and changing the write mode to load for applying the data to the target DB2 table.
It is loading 50 million records in a development environment (much slower than production). It has 4 DS nodes / 4 DB2 UDB nodes. Amongst other things, the job is using the DB2 enterprise stage to load (i.e. db2load) into the target table. The whole job takes about 7 minutes. That equates to about 120,000 records / second.
In comparison, the original and slower version of this job uses write mode and takes 50 minutes. That still is averaging about 18,000 records per second.
Based on those numbers, I would venture to guess that DataStage will provide better load times than OptiLoad.
Hope this helps.
Brad.
It is loading 50 million records in a development environment (much slower than production). It has 4 DS nodes / 4 DB2 UDB nodes. Amongst other things, the job is using the DB2 enterprise stage to load (i.e. db2load) into the target table. The whole job takes about 7 minutes. That equates to about 120,000 records / second.
In comparison, the original and slower version of this job uses write mode and takes 50 minutes. That still is averaging about 18,000 records per second.
Based on those numbers, I would venture to guess that DataStage will provide better load times than OptiLoad.
Hope this helps.
Brad.