Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / July 2008

Tip: Looking for answers? Try searching our database.

Any suggestion on how to speed up large batch inserts?

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Amber - 14 Jul 2008 10:10 GMT
We are using 8.2.9 Windows 64 edtion, in one of our projects we need
to recreate a few lager tables which have  many millions of rows each,
we have used concurrent Java threads to read from data source and do
batch inserts to the target tables concurrently, to speed up the
inserts we have used the following configuration policies:

1. Use large page size (32K) and extend size (256) on the target db,
2. Use large buffer pools for the tablespaces which contains the large
tables on the target db.
3. Set num_cleaners to 4 to speed up moving data from bufferpool to
disks on the target db, we have 4  4-core CPU on the server.
4. Set chngpgs_thresh to 80%, using this value we mean avoiding DB2 to
moving data from bufferpool to disk too frequtenlly, because when
doing these inserts we have not other readers on the target.
5. Use lage page size(32K) and extend size(256) and prefetch size
(256) on the source db.
6. Use lage bufferpool on the source db.
7. Set num_ioservers to 4, we have 4 4-core cpu on the source db.

We are not sure about the chngpgs_thresh value, any suggestions?
Mark A - 14 Jul 2008 17:42 GMT
> We are using 8.2.9 Windows 64 edtion, in one of our projects we need
> to recreate a few lager tables which have  many millions of rows each,
[quoted text clipped - 16 lines]
>
> We are not sure about the chngpgs_thresh value, any suggestions?

Instead of modifying the chngpgs_thresh, set the
DB2_USE_ALTERNATE_PAGE_CLEANING to ON (with db2set).

In the db cfg, set you logbuffsz to about 256. This requires that you
increase DBHEAP by same amount since it is part of DBHEAP memory.
Arun Srinivasan - 14 Jul 2008 19:15 GMT
> > We are using 8.2.9 Windows 64 edtion, in one of our projects we need
> > to recreate a few lager tables which have  many millions of rows each,
[quoted text clipped - 24 lines]
>
> - Show quoted text -

This has become a performance tuning thread, but I'd use a cursor load
instead, great speed, no space usage..
Norm - 29 Jul 2008 17:03 GMT
> We are using 8.2.9 Windows 64 edtion, in one of our projects we need
> to recreate a few lager tables which have  many millions of rows each,
[quoted text clipped - 16 lines]
>
> We are not sure about the chngpgs_thresh value, any suggestions?

If you are using SMS tablespace, I highly recommend enabling multipage
file allocation.  Formatting one page at a time is a big performance
hit.  See http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.
udb.doc/core/r0002054.htm
.

HTH,
Norm
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.