SQL0987N -- Memory overdeployment?

Barry Spiegel

SQL0987N -- Memory overdeployment?
> Working with DB2 V5.2 on AIX 4.3.2.1. We've been monitoring our memory
> utilization for a bit and have been gradually increasing SHEAPTHRES. The
> last change to this DBM configuration parameter took place on Friday and
> took effect with a db2stop/db2start on Sunday night.
>
> When the first user tried to connect, he got an SQL0987N error -- usually
> indicative of a failure to allocate application global memory. The
> related setting -- app_ctl_heap_sz -- is 1024K and hasn't been changed in
> months. However, the slight increase to SHEAPTHRES may have taken the
> database global memory and global control block memory to over 2Gb.
>
> The server has 4Gb available and, when DB2 is started, there is no
> indication of any memory in use on the server.
>
> When I reduced SHEAPTHRES back to its former setting, all returned to
> normal. So we're out of immediate trouble, but I'd still like to increase
> that setting again. Should I be looking at increasing NUM_ESTORE_SEGS to
> something higher than 0 to move buffer pool storage to the area over 2Gb?
> Or should I be looking at other memory-related issues?
>
> Am I missing the big picture here?
>
> Any hints or advice would be appreciated.
>
> Thanks, Barry
>
> Barry Spiegel
> EDS - New York Solution Centre
> 25 Northpointe Parkway
> Amherst, NY 14228
> Phone 716-564-6614 (8-373)
> Fax 716-564-6775
> E-mail [login to unmask email]
> Pager 716-448-6279
>
>



Bruce Allen

Re: SQL0987N -- Memory overdeployment?
(in response to Barry Spiegel)
My understanding is that if INTRA_PARALLEL is on, SHEAPTHRES is going to be
allocated at database startup. In a 32 bit model (like AIX DB2 v5.2), I
think SHEAPTHRES is going to be allocated from the same 256 Mbyte segment as
LOCKLIST and DBHEAP (which of course is going to be large if you have large
buffer pools and estore).

So, given up to 7 segments in AIX for Database Shared memory, I think your
absolute upper limit on Database Shared memory is going to be 1.75Gb.

Hmm, any messages in db2diag.log?

If you are only using 2Gb of the 4Gb available on the machine, then I think
the only way to get DB2 EE to use it is to use estore. This implies that you
have only a few (say less than 100) concurrent connections, and only a
single DB2 running on the server.

HTH,
Bruce Allen

> -----Original Message-----
> From: Spiegel, Barry [SMTP:[login to unmask email]
> Sent: Tuesday, 19 December 2000 7:07
> To: [login to unmask email]
> Subject: SQL0987N -- Memory overdeployment?
>
> > Working with DB2 V5.2 on AIX 4.3.2.1. We've been monitoring our memory
> > utilization for a bit and have been gradually increasing SHEAPTHRES.
> The
> > last change to this DBM configuration parameter took place on Friday and
> > took effect with a db2stop/db2start on Sunday night.
> >
> > When the first user tried to connect, he got an SQL0987N error --
> usually
> > indicative of a failure to allocate application global memory. The
> > related setting -- app_ctl_heap_sz -- is 1024K and hasn't been changed
> in
> > months. However, the slight increase to SHEAPTHRES may have taken the
> > database global memory and global control block memory to over 2Gb.
> >
> > The server has 4Gb available and, when DB2 is started, there is no
> > indication of any memory in use on the server.
> >
> > When I reduced SHEAPTHRES back to its former setting, all returned to
> > normal. So we're out of immediate trouble, but I'd still like to
> increase
> > that setting again. Should I be looking at increasing NUM_ESTORE_SEGS
> to
> > something higher than 0 to move buffer pool storage to the area over
> 2Gb?
> > Or should I be looking at other memory-related issues?
> >
> > Am I missing the big picture here?
> >
> > Any hints or advice would be appreciated.
> >
> > Thanks, Barry
> >
> > Barry Spiegel
> > EDS - New York Solution Centre
> > 25 Northpointe Parkway
> > Amherst, NY 14228
> > Phone 716-564-6614 (8-373)
> > Fax 716-564-6775
> > E-mail [login to unmask email]
> > Pager 716-448-6279
> >
> >
>
>
>
>
>



Jeremy Dodd

Re: SQL0987N -- Memory overdeployment?
(in response to Bruce Allen)
Bruce,

Whilst I agree with you about the 7 memory segments etc., I don't believe that you can get around it even using estore. My understanding is that it is a hard and fast limit with 32 bit
application on AIX. You certainly couldn't prior to v5.2 - although I will admit to not having tried it since.

Jeremy


Bruce Allen wrote:

> My understanding is that if INTRA_PARALLEL is on, SHEAPTHRES is going to be
> allocated at database startup. In a 32 bit model (like AIX DB2 v5.2), I
> think SHEAPTHRES is going to be allocated from the same 256 Mbyte segment as
> LOCKLIST and DBHEAP (which of course is going to be large if you have large
> buffer pools and estore).
>
> So, given up to 7 segments in AIX for Database Shared memory, I think your
> absolute upper limit on Database Shared memory is going to be 1.75Gb.
>
> Hmm, any messages in db2diag.log?
>
> If you are only using 2Gb of the 4Gb available on the machine, then I think
> the only way to get DB2 EE to use it is to use estore. This implies that you
> have only a few (say less than 100) concurrent connections, and only a
> single DB2 running on the server.
>
> HTH,
> Bruce Allen
>
> > -----Original Message-----
> > From: Spiegel, Barry [SMTP:[login to unmask email]
> > Sent: Tuesday, 19 December 2000 7:07
> > To: [login to unmask email]
> > Subject: SQL0987N -- Memory overdeployment?
> >
> > > Working with DB2 V5.2 on AIX 4.3.2.1. We've been monitoring our memory
> > > utilization for a bit and have been gradually increasing SHEAPTHRES.
> > The
> > > last change to this DBM configuration parameter took place on Friday and
> > > took effect with a db2stop/db2start on Sunday night.
> > >
> > > When the first user tried to connect, he got an SQL0987N error --
> > usually
> > > indicative of a failure to allocate application global memory. The
> > > related setting -- app_ctl_heap_sz -- is 1024K and hasn't been changed
> > in
> > > months. However, the slight increase to SHEAPTHRES may have taken the
> > > database global memory and global control block memory to over 2Gb.
> > >
> > > The server has 4Gb available and, when DB2 is started, there is no
> > > indication of any memory in use on the server.
> > >
> > > When I reduced SHEAPTHRES back to its former setting, all returned to
> > > normal. So we're out of immediate trouble, but I'd still like to
> > increase
> > > that setting again. Should I be looking at increasing NUM_ESTORE_SEGS
> > to
> > > something higher than 0 to move buffer pool storage to the area over
> > 2Gb?
> > > Or should I be looking at other memory-related issues?
> > >
> > > Am I missing the big picture here?
> > >
> > > Any hints or advice would be appreciated.
> > >
> > > Thanks, Barry
> > >
> > > Barry Spiegel
> > > EDS - New York Solution Centre
> > > 25 Northpointe Parkway
> > > Amherst, NY 14228
> > > Phone 716-564-6614 (8-373)
> > > Fax 716-564-6775
> > > E-mail [login to unmask email]
> > > Pager 716-448-6279
> > >
> > >
> >
> >
> >
> >
> >
>
>
>