Talk About Network

Google


Register and Login
Nick
Password
Register create new account Sign up is FREE and you can post replies, new topics, bookmark posts and more!
Recover lost password


Data Bases > IBM DB2 > heavy inserts a...
Latest [ Topics | Posts ] Archive Post A New Topic Post a Reply
<< Topic < Post Post 1 of 11 Topic 8805 of 9053
Post > Topic >>

heavy inserts and bufferpool

by dcruncher4@[EMAIL PROTECTED] Apr 20, 2008 at 01:39 AM

I am new to DB2.

version info DB2 9.5 , Linux

some more info:-
create bufferpool bp1 size 140000 pagesize 8K ;
update db cfg for test using CHNGPGS_THRESH 5 ;
update db cfg for test using NUM_IOCLEANERS 3 ;
update db cfg for test using NUM_IOSERVERS 3 ;

I am tying to convert a perl program to multi threaded.
To test the difference I created a table and loaded 100000 rows.
It loads fine as a single thread program. Loads it in about 15 seconds.

When I run it as multi threaded with 3 threads and all 3 of them
inserting, I
frequently
get this message:-

DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL1218N  There
are no
pages currently available in bufferpool "4097".  SQLSTATE=57011
DBD::DB2::st execute failed: [IBM][CLI Driver][DB2/LINUX] SQL1218N  There
are no
pages currently available in bufferpool "4097".  SQLSTATE=57011

It seems the rate of insert is much higher than the ability of DB2 to
clean the
dirty pages
out and frequently it reaches a point where it can't find a single free
page to
load the newly inserted row. but why this is an error. I have informix
background and in informix we don't see such errors.

Once I change the code and insert a sleep time after every X number of
rows
inserted,
the problem goes way. essentially giving the engine some time to flush the
dirty
buffers.

Or may be I am missing out a basic thing.

thanks.




 11 Posts in Topic:
heavy inserts and bufferpool
dcruncher4@[EMAIL PROTECT  2008-04-20 01:39:45 
Re: heavy inserts and bufferpool
"Mark A" <no  2008-04-20 17:48:48 
Re: heavy inserts and bufferpool
dcruncher4@[EMAIL PROTECT  2008-04-21 07:13:47 
Re: heavy inserts and bufferpool
"Mark A" <no  2008-04-21 21:09:01 
Re: heavy inserts and bufferpool
dcruncher4@[EMAIL PROTECT  2008-04-22 06:14:49 
Re: heavy inserts and bufferpool
"Mark A" <no  2008-04-22 19:00:19 
Re: heavy inserts and bufferpool
"ajstorm@[EMAIL PROT  2008-04-23 06:12:16 
Re: heavy inserts and bufferpool
Serge Rielau <srielau@  2008-04-21 06:50:32 
Re: heavy inserts and bufferpool
dcruncher4@[EMAIL PROTECT  2008-04-21 07:10:33 
Re: heavy inserts and bufferpool
Serge Rielau <srielau@  2008-04-21 15:28:51 
Re: heavy inserts and bufferpool
Ian <ianbjor@[EMAIL PR  2008-04-21 22:50:45 

Post A Reply:
  Go here to Signup

AddThis Feed Button


About - Advertising - Contact - Frequently Asked Questions - Privacy Policy - Terms of Use - Signup

Contact
tan13V112 Fri Jul 4 23:08:17 CDT 2008.