For performance, we recommend setting the following parameters.
db cfg parameters | Value |
---|---|
SELF_TUNING_MEM | ON |
DATABASE_MEMORY | AUTOMATIC |
LOCKLIST | AUTOMATIC |
MAXLOCKS | AUTOMATIC |
PCKCACHESZ | AUTOMATIC |
SHEAPTHRES_SHR | AUTOMATIC |
SORTHEAP | AUTOMATIC |
NUM_IOCLEANERS | AUTOMATIC |
NUM_IOSERVERS | AUTOMATIC |
DFT_PREFETCH_SZ | AUTOMATIC |
MAXAPPLS | AUTOMATIC |
AVG_APPLS | AUTOMATIC |
DBHEAP | 5,000 or higher |
LOGFILSIZ | 262144 |
LOGPRIMARY | more than 15 |
LOGSECOND | 0 |
NUM_LOG_SPAN | LOGPRIMARY - safety buffer |
DFT_DEGREE | 1 |
CUR_COMMIT | ON |
STMT_CONC | LITERALS |
Setting this parameter to ON enables the DB2® self-tuning memory manager (STMM) to automatically and dynamically set memory allocations to the memory consumers such as buffer pools, lock lists, package cache and sort heap.
Setting DATABASE_MEMORY to AUTOMATIC (for AIX® or Windows) or COMPUTED (for Linux, HP-UX or Solaris) allows DB2 to adjust the amount of database memory depending on load, memory pressures, etc.
Setting these parameters to AUTOMATIC allows STMM to dynamically manage their memory allocations.
The default DBHEAP is too small. You should set it anywhere from 5,000 or higher depending on the amount of memory available and the traffic volume.
You should size your transaction logs based on your transaction volumes. As a starting point, you could configure four transaction logs (LOGPRIMARY=4) of 1GB (LOGFILSIZ=262144 4K-pages) for high transaction volume. You may want to increase this value to 15 or higher.
You should adjust these settings as needed.
You should track the following monitor element to assess the effectiveness of these settings:
Setting this parameter limits the number of logs a transaction can span, which prevents situations where DB2 cannot switch transaction logs because all transaction logs are active. For example:
This parameter should be set to at least 3 so that valid long running transactions are not prematurely forced. This parameter should be set to at most LOGPRIMARY minus a safety buffer (e.g., 2). For example, if you have set LOGPRIMARY=10, then set NUM_LOG_SPAN=8.
This parameter sets the default degree of parallelism for intra-partition parallelism. In general, online transactional applications such as Sterling Selling and Fulfillment Foundation, typically experiences high volume of short queries that do not benefit from parallel queries. As a result, we recommend setting DFT_DEGREE=1 which disables intra-partition parallelism.
Parallelism can benefit long running, resource-intensive operations such as creating indexes on a large table. To enable parallelism, you need to:
db2 set current degree = '8'