IBM Support

Setting the user data limit for DB2 on AIX

Technote (FAQ)


This document discusses the user private memory data limit (ulimit -d) and provides recommended values for when you're running DB2 on AIX .


It is recommended that the user data limit be set and private memory usage by DB2® be tuned so that private memory will not be exhausted . Exhausting private memory can result in various errors , suboptimal performance, and overall instability .

For the purposes of the following discussion, the user data limit refers to the "soft" user data limit , which is what is displayed when issuing the ulimit -d command . It is not necessary to set the "hard" user data limit for DB2 on AIX.

By default, the soft user data limit on AIX is 128MB . This is equivalent to the value of 262144 512-byte units as set in /etc/security/limits, or 131072 1KB units as displayed by the ulimit -d command . This limits private memory usage to approximately one half of what is available in the 256MB private memory segment available for a 32-bit process on AIX . (Note that a DB2 server instance cannot make use of the Large Address Space or Very Large Address Space AIX 32-bit memory models due to shared memory requirements) . It is desirable on some systems (for example, those requiring large amounts of sort memory for performance) to increase the user data limit to allow DB2 to allocate more than 128MB of memory in a single process .

One approach is simply to set the user data memory limit to "unlimited" (that is to say, a value of "-1") . This is not recommended for 32-bit DB2 because it allows the data region to overwrite the stack , which grows downward from the top of the 256MB private memory segment . This would typically cause DB2 to abend (end abnormally). It is, however, an acceptable setting for 64-bit DB2 , as the data region and stack are allocated in separate areas of the very large address space available to 64-bit AIX processes .


32-bit DB2 on AIX :

The user data limit should be set high enough to maximize the private memory available to DB2, but should allow enough room for normal stack growth . The default stack limit on AIX is 32768 (1KB units) . DB2 does not require this much stack space , but it is reasonable to base the data user limit on this value. For example, subtract 32768 (1KB units) from the total space available in the 256MB private data segment ( 262144 1KB units )

262144 - 32768 = 229376

The user data limit would then be set to 458752 (512-byte units) . Other common recommendations are 500000 or 491519 (512-byte units) , and these are also acceptable , as they allow sufficient stack space for DB2.

It is commonly stated that data + stack limits must not exceed 256MB for 32-bit AIX processes . While this will result in acceptable settings for the data limit, this rule cannot actually be adhered to for the DB2 server. DB2 sets the stack limit in the db2sysc program to unlimited, a practice which has been carried over from early versions of DB2 on AIX, and this overrides any setting in the user environment . It does no harm to set the stack limit for the DB2 instance owner, but it will not be effective for DB2 server processes. It is only important that the data limit be set appropriately .

64-bit DB2 on AIX :

As stated earlier , there is no danger of the data region being allowed to overlay the stack in 64-bit AIX processes, so there is no longer the same requirement to put an upper ceiling on the user data limit when running 64-bit DB2 on AIX . The main concern is that the user data limit should not impose restrictions on DB2's private memory allocation requirements -- requirements which are based on how DB2 is configured and the nature of requests coming from applications . Excessive private memory requirements should be dealt with by tuning DB2 and/or modifying applications, not by imposing a private memory limit which may cause out-of-memory errors and potential instability . Therefore , "unlimited" is a recommended user data limit setting for the DB2 instance owner . An administrator may wish to impose some limit to protect against unusually high private memory allocation ( or a memory leak ) , but the required private memory by DB2 is very activity/configuration-specific . One option is to set the limit to some percentage of physical memory , for example, 10% , to guard against an extreme case .


1. It is commonly stated that data + stack limits must not exceed 256MB for 32-bit AIX processes . While this will result in acceptable settings for the data limit, this rule is not actually adhered to by DB2 server processes. DB2 sets the stack limit in the db2sysc program for both 32-bit and 64-bit DB2 to 256MB, and this overrides any setting in the user environment . It is only important that the data limit be set appropriately . The user stack limits can be left to the default for both 32-bit and 64-bit DB2 on AIX.

2. Processes on AIX inherit the resource limits of the user that executes the program . If users other than the DB2 instance ID start the instance (by executing the db2start command), the same user data limit requirements apply to those user IDs . One scenario is that if the instance is started remotely using the Control Center, the DAS will start DB2, and it is the resource limits of the ID which starts the DAS that will be in effect.

3. User resource limits are changed permanently either through the smitty interface or by directly editing the /etc/security/limits file . The system administrator would typically perform this task, and information is available in standard AIX documentation to assist you in this task. The limit will not be effective until the instance is started (db2start being issued after the instance has been stopped) in an AIX session which is created after the change is made.

4. Some user resource limits are displayed in DB2 trap files . In the following example, the DB2 instance has abended because the private data region and stack have collided. We can see this because the sbrk value (Data seg top), which represents the high mark of private memory allocation, is very high in segment 2 (the 256MB private memory segment on AIX starts at address 0x20000000) , and is near or exceeding the value of the stack pointer in register gr1 ( GPR[ 01 ] ) :

Resource Limits

Data seg top [sbrk(0)] = 0x2FFF90D0
Cur data size (bytes) = 0x7FFFFFFFFFFFFFFF
Cur stack size (bytes) = 0x0000000010000000
Cur core size (bytes) = 0x000000003FFFFE00

IAR: 00000000 MSR: 0000D0B2 LR: FFFFFFFF
CTR: 00140010 XER: FFFFFFFF FPSCR: 82004000
CR: 00442000

5. This document does not provide recommendations for DB2 client applications. The vendor of the application program on the client should be contacted for their recommendations . For DB2 programs other than db2sysc ( the main DB2 program which is executed from the db2start command ), the default user data limit is acceptable unless otherwise documented .

6. 32-bit db2fmp processes are linked with -bmaxdata:0x50000000, which enables the AIX 32-bit Large Memory Model . This results in an effective user data limit of 1.25GB, which will override the user data limit set for the fenced user ID . Therefore , there is no need to set the user data limit for the fenced user ID in 32-bit DB2. For 64-bit db2fmp processes however, since the 32-bit Large Memory Model does not apply, the user data limit for the fenced user ID will be in effect. The same guidelines as those for the instance apply: unlimited is acceptable unless a more conservative approach is desired (for example a % of RAM). Similar to the instance ID , the fenced user stack limit settings are overridden and can be left at the default values for both 32-bit and 64-bit DB2.

7. DB2 version 9.5 is multithreaded and has different requirements compared with previous releases. Please consult standard product documentation.

For further discussion on this topic, visit this developerWorks forum thread:

Document information

More support for: DB2 for Linux, UNIX and Windows
Operating System Services (OSS) - Memory Management

Software version: 8.2, 9.1

Operating system(s): AIX

Reference #: 1175377

Modified date: 2004-11-18