>I am new to DB2.I would try these:
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.
db2set DB2_USE_ALTERNATE_PAGE_CLEANING=ON (this will override
CHNGPGS_THRESH)
db2set DB2_SKIPINSERTED=ON (this will reduce lock contention on inserts)
db2 update db cfg for test using NUM_IOCLEANERS 8
db2 update db cfg for test using LOGBUFSZ 256
db2 update db cfg for test using DBHEAP 2000 (unless it already at least
this high)