Scott, I don't think your losing connections is related to bp size. You are doing a roll forward and I believe that that requires exclusive access to the db. You cannot restore nor roll forward a whole db with allowing online access on top of that when at V7. I haven't verified but I don't think V8 will allow it either.
If you are saying the lost connections happen over the oltp db, not the one being restored and rolled forward, then you must be doing this in the same machine and I don't understand how rolling forward one db would impact on another db, given your resources. Have you tried setting your DIAGLEVEL to 4 and looked at what the db2diag.log is telling you? Now as to finding out info. about your bp's, you can turn on the following: db2 update dbm cfg using dft_mon_bufferpool on then take periodic snapshot over a period of time. The info you'll get out of this will show you page utilisation if I remember well and you may see for each pool how it is used. Util_heap_sz is used as working storage for your buffers when doing loads, backups, restores and so on. It contends for same memory block as your buffer pools. If you are doing online db or tablespace backups or online tablespace restores, both these and normal db operatioons will force buffer pool and ustil_heap to fight for same memory blocks. If all the work is done off line then no contention happens. Given yoiur 23 GB of database, I hope you are setting your own buffer size and number of buffers, the following was recommended by Dale McInnis of IBM Toronto lab. So you buffer size*# of buffers have to fit in util_heap_sz. # of buffers = max(2x# targets, parallelism+# targets+2) buffersize = Multiple of your largest extentsize +1 Parallelism = min(# of processors +1,# of tablespaces) Is the db cfg you're showing the one for the oltp system or the one for the reporting system?? HTH, Pierre. ----- Original Message ----- From: "scott m" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, March 11, 2003 11:55 AM Subject: [DB2EUG] Is bufferpool too large? > I am running DB2 v7.2 on AIX. It is an OLTP system. We have a batch > job that copies the production database and does a restore/roll > forward to create a report database. The problem is when the > roll-forward step of the job is running, other applications lose > thier connection to the server (ATM machines, etc.). The bufferpool > size is 256,000 pages (4k each) as shown in syscat.bufferpools, and > I'm wondering if this is depriving other applications of necessary > resources. The bufferpool hit ration is always > 99%. Is it possible > that the BP can be too big? What is the best way to determine if DB2 > is over-allocating memory. And what about other memory allocations, > such as restbufsz, util_heap_sz, and applheapsz. Does anyone think > changing these parameters might help? > > The AIX server has 6 GB RAM and 6 CPU. The database is about 23 GB > and the db cfg paramers are: > > Database heap (4KB) (DBHEAP) = 9216 > Catalog cache size (4KB) (CATALOGCACHE_SZ) = 256 > Log buffer size (4KB) (LOGBUFSZ) = 16 > Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000 > Buffer pool size (pages) (BUFFPAGE) = 1000 > Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 65536 > Number of extended storage segments (NUM_ESTORE_SEGS) = 0 > Max storage for lock list (4KB) (LOCKLIST) = 200 > > Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 192 > > Sort list heap (4KB) (SORTHEAP) = 256 > SQL statement heap (4KB) (STMTHEAP) = 2048 > Default application heap (4KB) (APPLHEAPSZ) = 15000 > Package cache size (4KB) (PCKCACHESZ) = 1024 > Statistics heap size (4KB) (STAT_HEAP_SZ) = 10664 > > Interval for checking deadlock (ms) (DLCHKTIME) = 10000 > Percent. of lock lists per application (MAXLOCKS) = 10 > Lock timeout (sec) (LOCKTIMEOUT) = -1 > > Changed pages threshold (CHNGPGS_THRESH) = 60 > Number of asynchronous page cleaners (NUM_IOCLEANERS) = 12 > Number of I/O servers (NUM_IOSERVERS) = 36 > Index sort flag (INDEXSORT) = YES > Sequential detect flag (SEQDETECT) = YES > Default prefetch size (pages) (DFT_PREFETCH_SZ) = 764 > > Track modified pages (TRACKMOD) = OFF > > Default number of containers = 1 > Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 256 > > Max number of active applications (MAXAPPLS) = 80 > Average number of active applications (AVG_APPLS) = 3 > Max DB files open per application (MAXFILOP) = 64 > > Log file size (4KB) (LOGFILSIZ) = 62500 > Number of primary log files (LOGPRIMARY) = 8 > Number of secondary log files (LOGSECOND) = 4 > > > Thanks for your help. > > > > __________________________________________________ > Do you Yahoo!? > Yahoo! Web Hosting - establish your business online > http://webhosting.yahoo.com > - > ::: When replying to the list, please use 'Reply-All' and make sure > ::: a copy goes to the list ([EMAIL PROTECTED]). > *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] > *** For more information, check http://www.db2eug.uni.cc > - ::: When replying to the list, please use 'Reply-All' and make sure ::: a copy goes to the list ([EMAIL PROTECTED]). *** To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED] *** For more information, check http://www.db2eug.uni.cc
