DB2 memory usage help

[bomahony]

Im runnign various versions of DB2 on various UNIX platforms
(Solaris / AIX).

DB2 is generally eating up about 30% of memory on these systems (4Gb
of RAM, which equates to over a gig). i want to limit DB2s memory
usage to a couple of hundred meg in total for the entire application,
but cannot seem to find out how to do this.

When i check the instance memory usage with db2mtrk, it says its only
using a few Megs. I cannot find out how to limit the whole operation
to something like 250M or 500M or there abouts.

Can anyone help?

[Liam Finnie]

Re: DB2 memory usage help

On May 28, 7:51 am, bomahony <bomah...@gmail .comwrote:

Im runnign various versions of DB2 on various UNIX platforms
(Solaris / AIX).
>
DB2 is generally eating up about 30% of memory on these systems (4Gb
of RAM, which equates to over a gig). i want to limit DB2s memory
usage to a couple of hundred meg in total for the entire application,
but cannot seem to find out how to do this.
>
When i check the instance memory usage with db2mtrk, it says its only
using a few Megs. I cannot find out how to limit the whole operation
to something like 250M or 500M or there abouts.
>
Can anyone help?

Hello,

If you're using DB2 9.5, you can set the INSTANCE_MEMORY database
manager configuration parameter to your desired memory cap, and leave
all other memory configuration parameters at their default (AUTOMATIC)
settings.

If you're using DB2 9 or earlier, you'll likely have to update several
different memory configuration parameters. Your best choice may be to
use the AUTOCONFIGURE command, specifying your desired memory limit.
The AUTOCONFIGURE command will then recommend (and apply, if desired)
changes to various memory configuration parameters to get you
reasonably close to your desired limit.

Cheers,
Liam.

[bomahony]

Re: DB2 memory usage help

On May 28, 2:37 pm, Liam Finnie <lfin...@ca.ibm .comwrote:

On May 28, 7:51 am, bomahony <bomah...@gmail .comwrote:
>

Im runnign various versions of DB2 on various UNIX platforms
(Solaris / AIX).

>

DB2 is generally eating up about 30% of memory on these systems (4Gb
of RAM, which equates to over a gig). i want to limit DB2s memory
usage to a couple of hundred meg in total for the entire application,
but cannot seem to find out how to do this.

>

When i check the instance memory usage with db2mtrk, it says its only
using a few Megs. I cannot find out how to limit the whole operation
to something like 250M or 500M or there abouts.

>

Can anyone help?

>
Hello,
>
If you're using DB2 9.5, you can set the INSTANCE_MEMORY database
manager configuration parameter to your desired memory cap, and leave
all other memory configuration parameters at their default (AUTOMATIC)
settings.
>
If you're using DB2 9 or earlier, you'll likely have to update several
different memory configuration parameters. Your best choice may be to
use the AUTOCONFIGURE command, specifying your desired memory limit.
The AUTOCONFIGURE command will then recommend (and apply, if desired)
changes to various memory configuration parameters to get you
reasonably close to your desired limit.
>
Cheers,
Liam.

Thanks Liam

We havent started with DB2 9.5 yet.

I will look into the autoconfigure option. Is this a live change, or
does the db or instance have to be restarted?

I will look into this command now anyways.

[bomahony]

Re: DB2 memory usage help

Liam

when i use autoconfigure, is the mem_precent a percentage of system
memory or free memory available?

[Mark A]

Re: DB2 memory usage help

"bomahony" <bomahony@gmail .comwrote in message
news:1e8a3945-5797-4dd0-b15b-7dd7cd3554f4@e5 3g2000hsa.googl egroups.com...

Im runnign various versions of DB2 on various UNIX platforms
(Solaris / AIX).
>
DB2 is generally eating up about 30% of memory on these systems (4Gb
of RAM, which equates to over a gig). i want to limit DB2s memory
usage to a couple of hundred meg in total for the entire application,
but cannot seem to find out how to do this.
>
When i check the instance memory usage with db2mtrk, it says its only
using a few Megs. I cannot find out how to limit the whole operation
to something like 250M or 500M or there abouts.
>
Can anyone help?

Effective use of DB2 requires adequate bufferpool sizes (in addition to
other memory uses). Failure to allocate sufficient bufferpool memory is a
seriously misguided. If you don't have 1 GB to allocate for DB2, then you
should think about buying a cheap PC with several GB of memory and running
it on there (Linux or Windows).

[Liam Finnie]

Re: DB2 memory usage help

On May 28, 10:05 am, bomahony <bomah...@gmail .comwrote:

Liam
>
when i use autoconfigure, is the mem_precent a percentage of system
memory or free memory available?

Hello,

mem_percent is a percentage of system memory. A lot of the
configuration parameters are updateable on-line, but some aren't, so
you'll likely need to deactivate the db/stop the instance, and
restart, for the full changes to take effect.

From the sounds of it, you really want the 'Simplified Memory
Configuration' feature in DB2 9.5. That, combined with the UNIX/Linux
threaded engine architecture (DB2 on Windows was always threaded), and
other useability enhancements added in 9.5, should make configuring
your DB2 instance much easier.

BTW - if you can't move to 9.5, the more/less equivalent of
sga_max_size in DB2 9 is the 'database_memor y' configuration parameter
(assuming you leave your bufferpool sizes, shared sort space, etc at
AUTOMATIC, DB2 STMM will self-tune those other memory areas to optimal
values within your specified database_memory limit). database_memory
should contain the bulk of memory allocations required by the
instance. However, database_memory does not include agent-private
memory allocations, and instance-wide memory allocations (not sure if
sga_max_size includes all those equivalent memory allocations either).

Cheers,
Liam.

[bomahony]

Re: DB2 memory usage help

I'm curious as to what you could be using DB2 for in a build process
(at least, in the way described - a few connections, no heavy
processing). Are you testing some SQL against various different engines?
>

Exactly.

Never run across DB2 memory leaks, personally. Still, if you've found
one, open a PMR (or report it to the Express-C forum if you're using
the free edition).

I just find that DB2 never releases its memory properly, even when
issued db2stop commands.

I want to limit the total amount of memory DB2 uses to a maximum. If
the connections fail after a while because of that, then so be it,
these will be reported in the logs. Only one out of every three or
four builds (multiple builds per night) uses DB2. Currently, db2 uses
up the memory that should be used for other stuff, and the builds get
slower and slower during the night.

I dont understand how this is so hard. All i have to do in oracle is
set one variable (sga_max_size) and viola. Oracle uses 500m max.

>

The issue is not the quality of DB2 or whether it runs correectly. Its
a matter of limiting what it can use. Nothing else will do.

>
Assuming you're just using DB2 to test some portable SQL syntax in a
build/test process I suspect your best option is simply to use DB2 9.5
Express-C (the free one).

Not possible. we need to test with specific FPs and versions of DB2
for verification.

As mentioned earlier in the thread, memory management is a /lot/ easier
with 9.5 (you've got the "one parameter to rule them all",
INSTANCE_MEMORY - which sounds rather like Oracle's sga_max_size from
your description). Express-C's limits (2 cores, 2Gb of RAM, IIRC?) fit
well within the limits you wish to impose and it should implement all
the functionality you're interested in testing (assuming it's SQL
intended to be portable, it must be pretty basic?).
>
If you wish to stick with whatever version you're currently on, the
AUTOCONFIGURE command's mem_percent parameter is percentage of system
memory, not free memory. However, DB2's quite a memory hog - I wouldn't
recommend allocating less than half a gig to it, even for simple test
purposes (9.5's a bit less memory heavy given the new threaded
architecture, although things like the auto memory tuning process can
gobble up some of the savings).
>
Cheers,
>
Dave.

I wish i caould run 9.5 with the ability to set it like that. However,
im stuck with what ever version the engineers request. Ill get back to
the mem_percent in my next post.

[bomahony]

Re: DB2 memory usage help

Last night i set the mem_percent on both a AIX and SOLARIS builder.
There is two DBs on each instance.

AIX is running DB2 8.1 fp11
SOLARIS is running DB2 9.1 fp4

I ran the command :
db2 autoconfigure using mem_percent 15 apply db and dbm after
connecting to BOTH databases per system.

BTW sorry if i have come accross short in any of these replies, this
is getting on my nerves, and unfortunately i dont have a choice in the
matter of what version / db we use.

All of your help is more than welcome.

BTW is there a network GUI monitoring / administration tool available
for DB2, like Oracles Enterprise Manager?

[bomahony]

Re: DB2 memory usage help

On May 29, 12:31 pm, "Dave Hughes" <d...@waveform. plus.comwrote:

bomahony wrote:

BTW is there a network GUI monitoring / administration tool available
for DB2, like Oracles Enterprise Manager?

>
That's the control center (db2cc) - although whether you'll find it
much use is another matter; I'm not a fan of it, personally I find the
command line the best way to administer the server - along with the
db2mc (http://db2mc.dfw.ibm.com/) tool to aid with monitoring (only
useful with DB2 9 and above, unfortunately). Anyway...

Thanks Dave

Ive used the db2cc, and also prefer the command line. I was hoping
there was some centralised tool i could use to manage lots of
instances / servers from one location.... Guess not ...

thanks anyways

[Pierre StJ]

Re: DB2 memory usage help

On May 29, 8:23 am, bomahony <bomah...@gmail .comwrote:
... clipped
That's exactly what the CC allows you to do.
All You need to do is have the DAS running on each server.
From your CC you can then catalog each server's admin node (local or
remote) and then for each server you can catalog each instance and
their db's (local or remote).
This then gives you a central point to manage systems, instances, and
db's.
By the way each das admin cfg has a parameter allowing you to have at
each server a tools catalog or identify a db in the network that will
allow you to centralize each server's tools catalog at a single db
site.
The tools catalog is only required if you set up tasks in the task
center of a system and/or use db2's task scheduling facilities.
Regards, Pierre.

Ive used the db2cc, and also prefer the command line. I was hoping
there was some centralised tool i could use to manage lots of
instances / servers from one location.... Guess not ...
>
thanks anyways

[Dave Hughes]

Re: DB2 memory usage help

bomahony wrote:

On May 29, 12:31 pm, "Dave Hughes" <d...@waveform. plus.comwrote:

bomahony wrote:

BTW is there a network GUI monitoring / administration tool
available for DB2, like Oracles Enterprise Manager?

That's the control center (db2cc) - although whether you'll find it
much use is another matter; I'm not a fan of it, personally I find
the command line the best way to administer the server - along with
the db2mc (http://db2mc.dfw.ibm.com/) tool to aid with monitoring
(only useful with DB2 9 and above, unfortunately). Anyway...

>
Thanks Dave
>
Ive used the db2cc, and also prefer the command line. I was hoping
there was some centralised tool i could use to manage lots of
instances / servers from one location.... Guess not ...

Well, yeah, that's the db2cc - you can manage multiple instances /
servers from db2cc running on a client, provided the client has all the
instances one wishes to manage cataloged correctly (i.e. both the
remote database and administration instances must be cataloged with all
necessary details).

For example, let's say we've got a couple of DB2 servers called F and G
which each have a bog standard db2inst1 database instance, containing
SAMPLE and TOOLSDB databases, and an administration instance. The
following commands could be used to "fully" catalog the instances and
databases such that they could be remotely administered with db2cc
(specifically, things like SYSTEM must be included to tie database and
administration instances together, and I think REMOTE_INSTANCE needs
specifying too - things that might usually be ommitted from cataloging
commands):

-- Catalog the database instances
CATALOG TCPIP NODE F
REMOTE f.localdomain SERVER 60000
REMOTE_INSTANCE db2inst1
SYSTEM F
OSTYPE LINUX;
CATALOG TCPIP NODE G
REMOTE g.localdomain SERVER 60000
REMOTE_INSTANCE db2inst1
SYSTEM G
OSTYPE LINUX;

-- Catalog the admin instances
CATALOG ADMIN TCPIP NODE FDAS
REMOTE f.localdomain
SYSTEM F;
CATALOG ADMIN TCPIP NODE GDAS
REMOTE g.localdomain
SYSTEM G;

-- Catalog the databases
CATALOG DB TOOLSDB AS FTOOLS AT NODE F;
CATALOG DB SAMPLE AS FSAMPLE AT NODE F;
CATALOG DB TOOLSDB AS GTOOLS AT NODE G;
CATALOG DB SAMPLE AS GSAMPLE AT NODE G;


Still, I'd stick to the command line - better for automating changes to
several servers via scripting and such like :-)

Do check out db2mc for your v9 server though. It doesn't need to run on
the server itself, just on any old box with a DB2 client + Apache + PHP
(and the ibm_db2 php driver). Provided the databases to monitor are
cataloged on the client it should work fine and provides handy screens
like memory usage, buffer pool hit ratios, the dynamic SQL cache, etc.
etc.

(incidentally, db2mc doesn't care about things like administration
instances being cataloged - it uses the new administrative stored
procedures and table functions introduced in DB2 9 to do its stuff)


Cheers,

Dave.

[bomahony]

Re: DB2 memory usage help

<snip>

(incidentally, db2mc doesn't care about things like administration
instances being cataloged - it uses the new administrative stored
procedures and table functions introduced in DB2 9 to do its stuff)
>
Cheers,
>
Dave

Dave is this in DB2 v9 or v9.5? someone else stated it was just
9.5.....

[Dave Hughes]

Re: DB2 memory usage help

bomahony wrote:

<snip>

(incidentally, db2mc doesn't care about things like administration
instances being cataloged - it uses the new administrative stored
procedures and table functions introduced in DB2 9 to do its stuff)

Cheers,

Dave

>
Dave is this in DB2 v9 or v9.5? someone else stated it was just
9.5.....

I'm a bit fuzzy on this (I didn't get much chance to play with 9 - our
production jumped straight from 8 to 9.5), but as I understand it:

DB2 9 introduced a load of administrative stored procedures, some
views, and extended some existing ones from 8 like ADMIN_CMD to work
with lots more things.

DB2 9.5 added a whole pile of administrative views ontop of the
snapshot procedures, and added a few more stored procs itself (you'd
have to check the release notes or compare the infocenters to find out
which ones - I don't know offhand).

I've played with db2mc on 8, 9 (briefly), and 9.5. On 8, almost nothing
works (hardly surprising as it's not really meant to run with it :-).
On 9 I vaguely recall that many of the reports seemed to function
although there was the odd page that complained about a missing
view/proc, and I suspect some of the "actions" one could carry out like
forcing connections might not work. Naturally, everything works on 9.5.


Cheers,

Dave.