Thanks Pierre for the reply.
The DB cfg info is from the production "live" database.
This DB is backed up (online) and the batch job is run
(after hours). The job does a redirected restore to
create the report database (within the same instance),
copies the recovery logs, and does a roll-forward to
create an end-of-day report database. The report database
gets a bufferpool that is 1/2 size of the live database.
both databases have one bufferpool each, and both have
Degree of parallelism =1.
What is happening is that when the rollforward is being
done on the report DB ATM connections to the server start
to drop ("connection lost to host"). It appears that the
memory that is allocated during the rollforward is starving
these other applications of memory, and I am trying to determine
if that is the case and how. I will try the suggestions you
gave regarding the DIAGLEVEL and taking bufferpool snaps. Please
reply if you have any more ideas.
Scott
--- Pierre Saint-Jacques <[EMAIL PROTECTED]> wrote:
> From: "Pierre Saint-Jacques" <[EMAIL PROTECTED]>
> To: "scott m" <[EMAIL PROTECTED]>,
> <[EMAIL PROTECTED]>
> Subject: Re: [DB2EUG] Is bufferpool too large?
> Date: Tue, 11 Mar 2003 12:26:34 -0500
>
> 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
> >
>
>
__________________________________________________
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
