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]

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]

On May 28, 2:37 pm, Liam Finnie <[e-mail] .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]

Liam

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

[Mark A]

"bomahony" <bo******@gmail .comwrote in message
news:1e******** *************** ***********@e53 g2000hsa.google groups.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]

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]

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]

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]

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]

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