Hi All,
Just wondering about the performance with DB2. While inserting 15 million records into the db2 table it is took 2 days to finish it. The job uses db2 plug-in to insert records into the database. Is there any thing need to be checked to increase the performance.
Also my other jobs which has the same 15million records will be inserting or updating into the db2 table.
If anyone knows the best way to load this let me know.
Thanks
Rasi
DB2 Insert Performance
Moderators: chulett, rschirm, roy
-
vmcburney
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
Two days is a bit on the slow side.
Ways to make it faster:
- Use the native load utility instead of the plug-in.
- Use multiple instances to have at least 4 jobs going at once.
- Cosort have a Load Accelerator for DB2 that they claim can load tables 50% faster.
- Check your network connection to the DB2 database.
The fastest way to get that volume of rows into a database is via a bulk load utility. You output the data to a sequential file and ftp it to the database server. You then use a db2 load application to put it into the table.
What I'd suggest is you create a multiple instance job where each job processes part of the 15 million. Try to stagger the amounts so one of the instances finishes well before the others. Each instance ftps the file (can also compress) and bulk loads it into the table. The tricky part is writing a routine that ensures only one load is running at any one time (if the type of load you are using needs exclusive access to the table). This reduces the impact of your network overhead since you load directly from the DB2 server and you ftp load files while other loads running.
I found a page which discusses the performance of the DB2 Load command:
http://www7b.boulder.ibm.com/dmdd/libra ... miya2.html
Vincent McBurney
Data Integration Services
www.intramatix.com
Ways to make it faster:
- Use the native load utility instead of the plug-in.
- Use multiple instances to have at least 4 jobs going at once.
- Cosort have a Load Accelerator for DB2 that they claim can load tables 50% faster.
- Check your network connection to the DB2 database.
The fastest way to get that volume of rows into a database is via a bulk load utility. You output the data to a sequential file and ftp it to the database server. You then use a db2 load application to put it into the table.
What I'd suggest is you create a multiple instance job where each job processes part of the 15 million. Try to stagger the amounts so one of the instances finishes well before the others. Each instance ftps the file (can also compress) and bulk loads it into the table. The tricky part is writing a routine that ensures only one load is running at any one time (if the type of load you are using needs exclusive access to the table). This reduces the impact of your network overhead since you load directly from the DB2 server and you ftp load files while other loads running.
I found a page which discusses the performance of the DB2 Load command:
http://www7b.boulder.ibm.com/dmdd/libra ... miya2.html
Vincent McBurney
Data Integration Services
www.intramatix.com
What version of DataStage are you using? If the data is totally mainframe related better go for a MainFrame job instead of using Plug-Ins. This way you will be using the processing power of DB2. If the data is coming from mainframes, this option will be valid. Also you could use DB2 Bulk Loader, if its plain insert. This way its much faster.
Cheers
Viswanath.S
Cheers
Viswanath.S