Ask the Expert

How does DB2 manage the memory allocations/deallocations in an AIX environment?

How does DB2 manage the memory allocations and deallocations in the AIX environment? Is there a reason that DB2 allocates more memory than configured?

Example, I have a database instance created with five Buffer pools comprising of 10GB and all other configuration parameters related to shared memory and private memory, etc. are summing up to 800MB. While running the application, I can see that DB2 is using the 16GB of physical memory (max RAM installed) and the 4GB of swap. As soon as all the swap is filled, which is 4GB in my case, the operating system kills all the processes and the machine goes into the single user maintenance mode that needs a hard reboot of the machine.

    Requires Free Membership to View

Although I have used DB2 on Unix and Windows platforms (Oracle and SQL Server too, for that matter) I am not an expert on non-mainframe platforms. So I asked Scott Hayes, BMC Software's resident expert on DB2 for Linux, Unix and Windows platforms to contribute an answer to your question.

Scott responds:

Well, without the required details (e.g. configuration information), here are some educated guesses that might help.

With BP allocations that large, I assume you are running DB2 V8 64bit. On a 16GB AIX box, I would allocate at least that much paging space. 4GB is too small. The symptoms you are describing could occur if DB2 is doing private memory sorts (INTRA_PARALLEL is turned OFF) and the following environment variables have not been set by the instance:

DB2MEMDISCLAIM = YES
DB2MEMMAXFREE = 8192000

Without the DB2MEMDISCLAIM, DB2 will not make a proper effort to release private memory when it is finished with it. The number of connections into DB2 would also have some bearing on memory consumed by the DB2 instance. An easy rule of thumb is 1MB per connection.

You should run the "db2set" command to verify if the AIX environment variables are set and if not, use "db2set DB2MEMDISCLAIM=YES" and "db2set DB2MEMMAXFREE=8192000" commands to properly set them. And, I'd also like to see 20GB of paging space allocated for the operating system.

If this is a data warehouse database with a small number of concurrently executing connections, he could also try using INTRA_PARALLEL=ON which would cause DB2 to do shared memory sorts and exploit CPU parallelism. But, turning this on should be done with great care and in conservative increments to the DFT_DEGREE of parallelism.

Editor's note: Do you agree with this expert's response? If you have more to share, post it in one of our .VO7aaqqaAFk.0@/search390>discussion forums.

This was first published in September 2003

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: