Why is DB2 not releasing memory sooner?

[Ian Boyd]

Customer is running a load test against a websphere application that uses
DB2. While the load test is running the memory usage of DB2 climbs from x
up to y. When the load test ends, the memory usage of DB2 returns to x.

However, sometimes after running the load test the memory usage does not
drop down to x right away; sometimes it takes a few hours. This "problem"
exists with their current live version of the software, and the version we
want to install. Customer will not install update until this "problem" is
resolved.

Customer is a government organization, in which the DB2 admin, the DB2
server admin, the server memory usage admin have collectivly as much
knowledge as a sack of door knobs.

Besides, "Go f*ck yourself.", what can we tell the customer?

[Patrick Finnegan]

On Jul 16, 10:13*pm, "Ian Boyd" <[e-mail]:

Customer is running a load test against a websphere application that uses
DB2. *While the load test is running the memory usage of DB2 climbs from x
up to y. When the load test ends, the memory usage of DB2 returns to x.

However, sometimes after running the load test the memory usage does not
drop down to x right away; sometimes it takes a few hours. This "problem"
exists with their current live version of the software, and the version we
want to install. Customer will not install update until this "problem" is
resolved.

Customer is a government organization, in which the DB2 admin, the DB2
server admin, the server memory usage admin have collectivly as much
knowledge as a sack of door knobs.

Besides, "Go f*ck yourself.", what can we tell the customer?

I assume that the "load test" refers to an application load test
rather than a data load using the db2 load utility. Normally most DB2
memory usage is static i.e. the memory required by the database and
table space buffer pools is allocated at database startup and does not
change. However in versions 8 and 9 IBM introduced automatic memory
management which means that DB2 will dynamically expand and contract
it's memory pools depending on load. I always switch this off because
I like to have control over memory usage. It may be that in your case
DB2 gradually releases memory as the load drops off.

Check the database parameters.

db2 connect to "yourDatabase" user xxxxx

db2 get db config for "yourDatabase"

Look at:

Self tuning memory (SELF_TUNING_MEM) = OFF

[Ian Boyd]

(I hope for your sake your customer doesn't use google...)

Perhaps i was a bit critical. But this has been going on for nine months
(October), and i'm tired.

Thanks for the info.

The idea of software deciding to maybe release memory will not be comforting
to people who like to run their controlled tests and want to see consistent
behaviour. But at least it gives me something.

[Ian Boyd]

I always switch this off because

>I like to have control over memory usage.

Thanks for the info.

But in general i assume you wouldn't recommand anyone just turn it off
willy-nilly, especially if they won't, or won't know how, to adjust memory
usage of DB2?

[Larry]

Ian,

What version/release of DB2 are you using and at what fixpak level? Have
you tried installing the latest fixpak for that version (in case there
are any memory release or memory leak defects)?

Larry E

Ian Boyd wrote:

Customer is running a load test against a websphere application that uses
DB2. While the load test is running the memory usage of DB2 climbs from x
up to y. When the load test ends, the memory usage of DB2 returns to x.

However, sometimes after running the load test the memory usage does not
drop down to x right away; sometimes it takes a few hours. This "problem"
exists with their current live version of the software, and the version we
want to install. Customer will not install update until this "problem" is
resolved.

Customer is a government organization, in which the DB2 admin, the DB2
server admin, the server memory usage admin have collectivly as much
knowledge as a sack of door knobs.

Besides, "Go f*ck yourself.", what can we tell the customer?

[Mark A]

Ian Boyd wrote:

>Customer is running a load test against a websphere application that uses
DB2. While the load test is running the memory usage of DB2 climbs from
x up to y. When the load test ends, the memory usage of DB2 returns to x.

However, sometimes after running the load test the memory usage does not
drop down to x right away; sometimes it takes a few hours. This "problem"
exists with their current live version of the software, and the version
we want to install. Customer will not install update until this "problem"
is resolved.

Customer is a government organization, in which the DB2 admin, the DB2
server admin, the server memory usage admin have collectivly as much
knowledge as a sack of door knobs.

Besides, "Go f*ck yourself.", what can we tell the customer?

Make sure that the application is closing the connection every so often, at
least every 100 transactions. Here is a quote from the 8.2 Programming
Client Applications manual.
<begin quote>
"Closing a connection to a JDBC data source

When you have finished with a connection to a data source, it is essential
that you close the connection to the data source. Doing this releases the
Connection object's DB2 and JDBC resources immediately. To close the
connection to the data source, use the close method.

For example:

Connection con;
...
con.close();

If autocommit mode is not on, the connection needs to be on a unit-of-work
boundary before you close the connection."
<end quote>
I had a programming using IBM Content Manager appliction that was running
out of memory on numerous calls to an API to load data. Once the programmer
starting closing the connection after 100 calls, everything worked fine.