DB2 9.5 Standard Parameter Settings

>!Version=1.0
This SAP Note is valid for the following system workload classes:

• OLTP (for example, SAP ERP and SAP CRM)

• OLAP (for example, SAP NetWeaver BW)

Specific settings that are required for only one workload type are documented in separate sections at the end of this note.

You can achieve optimal system performance and optimal usability with a correct setup of the DB2 database and of the database manager parameters. Do not deviate from the standard recommendations below unless you feel experienced enough to understand the impact of your changes.

In some cases, SAP Active Global Support might advice you to temporarily deviate from the settings documented in this SAP Note to solve a specific problem. In this case, follow the advise from SAP Active Global Support. After the problem has been finally solved by an SAP or DB2 code correction, apply the code correction as soon as possible and re-establish the original settings.

Most of the parameters below are dynamic. Changes to the parameters become effective immediately if you use an existing database connection. This means that you are connected to the database (<DBSID>) first and then change the parameters using this existing connection. You can view currently active parameters using the following command:

db2 get db cfg for <dbsid> show detail

As of DB2 Version 9.1, the "Self Tuning Memory Manager" (STMM) was introduced. STMM is a memory-tuning feature that simplifies the task of memory configuration by automatically setting values for several memory configuration parameters. When enabled, the memory tuner dynamically adapts the sizes of consumers in the database shared memory (DATABASE_MEMORY). You can run your DB2 V9.5 database either with fixed memory settings or with STMM enabled. The STMM-relevant settings are marked with [STMM=YES] and [STMM=NO], respectively. For more information, see the section about DB2 STMM later in this SAP Note. Generally, we recommend that you use DB2 STMM for single partition systems.

The following markers are used in the parameter recommendation sections:

• [CUST]: Customer individual setting
  
  There is no particular setting required by SAP. The values listed for these parameters are typical default values that you should start with. If required, you can set the parameter differently.

• [DEPR]: Deprecated
  
  The parameter has become obsolete or deprecated with DB2 V9.5 and is ignored by DB2.

• [STMM=YES], [STMM=NO]: Related to the use of DB2's Self Tuning Memory Manager (STMM)

• If you want to use STMM, follow the recommendations marked with [STMM=YES]. Generally, we recommend that you use DB2 STMM for single partition systems.

• If you want to run your database with fixed memory settings, follow the recommendations marked with [STMM=NO].

• [BI=YES], [BI=NO]: Differs depending if your workload is primarily OLAP or OLTP

• If your workload is primarily OLAP-dominated (originating from SAP NetWeaver BW, SAP SRM or SAP SCM), follow the settings marked with [BI=YES].

• In all other cases, follow the recommendations marked with [BI=NO].

• [OS=WINDOWS], [OS=UNIX]: Requires different settings on Windows or UNIX-based database servers:

• If your database server is running on Windows, apply the settings marked with [OS=WINDOWS].

• If your database server is running on Linux, HP-UX, Solaris or AIX, apply the recommendation marked with [OS=UNIX].

• [FP>=nn.nn.nnnn], [FP<=nn.nn.nnnn]: Recommendation is valid for the given range of DB2 levels only

Multiple markers in one line are to be read as logical "AND", that is, the setting is recommended for configurations where all conditions are true.

For example, PARAM1 = 99 [FP<=09.05.0002], [OS=WINDOWS] means that you should set PARAM1=99 only if your DB2 level is up to and including 09.05.0002 (9.5FP2) AND your database server is running on Windows. If your database server is not running on Windows, or if you are using DB2 V9.5 FP3 or higher, do not apply the setting.

Database Manager Configuration Parameters (STMM Not Enabled)

The following database configuration parameters are grouped according to the output that you receive if you use the following command:
db2 get dbm cfg

To update the parameters, enter the following command:
DB2 UPDATE DBM CFG USING <parameter> <value>



List of Parameters:
===================

> CPUSPEED            = <value>              [CUST]
> COMM_BANDWIDTH      = <value>              [CUST]

Note:
CPUSPEED and COMM_BANDWIDTH settings are generated by DB2 during instance creation based on hardware characteristics of your database server. To indicate that DB2 should regenerate the value, you can set the parameters to -1.

> NUMDB                = 8                   [CUST]
> FEDERATED            = NO                  [CUST]
> TP_MON_NAME          = <empty>             [CUST]
> DFT_ACCOUNT_STR      = <empty>             [CUST]
> JDK_PATH              = <path to JDK>       [CUST]

> DIAGLEVEL             = 3
> NOTIFYLEVEL           = 3

> DIAGPATH        = /db2/<DBSID>/db2dump         [OS=UNIX]
  DIAGPATH        = <drive>:\db2\<DBSID>\db2dump [OS=WINDOWS]

> DFT_MON_BUFPOOL       = ON
> DFT_MON_LOCK          = ON
> DFT_MON_SORT          = ON
> DFT_MON_STMT          = ON
> DFT_MON_TABLE         = ON
> DFT_MON_TIMESTAMP     = ON
> DFT_MON_UOW           = ON
> HEALTH_MON            = OFF                 [CUST]

> SYSADM_GROUP          = DB<DBSID>ADM
> SYSCTRL_GROUP         = DB<DBSID>CTL
> SYSMAINT_GROUP        = DB<DBSID>MNT
> SYSMON_GROUP          =                    [CUST]

> CLNT_PW_PLUGIN        =                    [CUST]
> CLNT_KRB_PLUGIN      =                     [CUST]
> GROUP_PLUGIN          =                    [CUST]
> LOCAL_GSSPLUGIN      =                     [CUST]
> SRV_PLUGIN_MODE      = UNFENCED            [CUST]
> SRVCON_GSSPLUGIN_LIST =                     [CUST]
> SRVCON_PW_PLUGIN      =                    [CUST]
> SRVCON_AUTH          = NOT_SPECIFIED       [CUST]
> CLUSTER_MGR          =                     [CUST]

> AUTHENTICATION        = SERVER_ENCRYPT      [CUST]
> CATALOG_NOAUTH        = NO
> TRUST_ALLCLNTS        = YES                 [CUST]
> TRUST_CLNTAUTH        = CLIENT              [CUST]
> FED_NOAUTH            = NO                  [CUST]

> DFTDBPATH            = /db2/<DBSID>        [CUST]

> MON_HEAP_SZ           = AUTOMATIC
> JAVA_HEAP_SZ          = 2048                [CUST]
> AUDIT_BUF_SZ         >= 0

Note:
AUDIT_BUF_SZ = 0 is sufficient if you are not using the DB2 Audit facility. If you want to use DB2 Audit, read SAP Note 1474404.

> INSTANCE_MEMORY      = AUTOMATIC           [STMM=NO]

> AGENT_STACK_SZ      >= 128                 [OS=WINDOWS]
> AGENT_STACK_SZ      >= 1024                [OS=UNIX],[BI=NO]
> SHEAPTHRES            = 0                   [STMM=NO],[CUST]

> DIR_CACHE             = NO

> ASLHEAPSZ             = 16
> RQRIOBLK              = 65000
> UTIL_IMPACT_LIM      = 10                  [CUST]

> AGENTPRI              = SYSTEM
> NUM_POOLAGENTS        = AUTOMATIC

> NUM_INITAGENTS        = 5                   [CUST]
> MAX_COORDAGENTS       = AUTOMATIC
> MAX_CONNECTIONS       = AUTOMATIC

> KEEPFENCED          = YES                  [FP>=09.05.0003]
> KEEPFENCED          = NO                  [FP<=09.05.0002]

Note:
As of DB2 V9.5 FP3 and higher, use KEEPFENCED=YES (as of DB2 V9.5 FP3 DB2_WORKLOAD=SAP enables DB2_AGENT_CACHING_FMP=OFF, which allows an efficient use of KEEPFENCED=YES). On previous Fix Pack levels, set KEEPFENCED=NO.

> FENCED_POOL         >= 10
> NUM_INITFENCED       = 0

> INDEXREC             = RESTART

> TM_DATABASE          = 1ST_CONN             [CUST]
> RESYNC_INTERVAL      = 180                  [CUST]

> SPM_NAME             =                      [CUST]
> SPM_LOG_FILE_SZ      = 256                  [CUST]
> SPM_MAX_RESYNC      = 20                   [CUST]
> SPM_LOG_PATH         =                      [CUST]

> SVCENAME            = sapdb2<DBSID>        [CUST]
> DISCOVER            = SEARCH               [CUST]
> DISCOVER_INST        = ENABLE               [CUST]

> MAX_QUERYDEGREE      = 1                    [BI=NO]
> INTRA_PARALLEL      = NO                   [BI=NO]

> FEDERATED_ASYNC      = 0

> FCM_NUM_BUFFERS      = AUTOMATIC
> FCM_NUM_CHANNELS     = AUTOMATIC
> CONN_ELAPSE          = 10                   [OS=UNIX]
> MAX_CONNRETRIES      = 5                    [OS=UNIX]
> MAX_TIME_DIFF        = 60                   [OS=UNIX]

> START_STOP_TIME      = 10                   [CUST]

Database Configuration Parameters (STMM Not Enabled)

The following database configuration parameters are grouped according to the output that you receive if you use the following command:


db2 get db cfg for <dbsid>

To update the parameters, enter the following command:


DB2 UPDATE DB CFG FOR <DBSID> USING <parameter> <value>

List of Parameters:
===================

> ALT_COLLATE          = IDENTITY_16BIT       [CUST]

Note:
This setting applies only to non-Unicode databases!

> DYN_QUERY_MGMT        = DISABLE
> DISCOVER_DB          = ENABLE             [CUST]

> DFT_QUERYOPT          = 5
> DFT_DEGREE            = 1
> DFT_SQLMATHWARN       = NO
> DFT_REFRESH_AGE       = 0
> DFT_MTTB_TYPES        = SYSTEM
> NUM_FREQVALUES        = 10
> NUM_QUANTILES         = 20

> DECFLT_ROUNDING       = ROUND_HALF_EVEN

> SELF_TUNING_MEM      = OFF                 [STMM=NO]
> DATABASE_MEMORY      = COMPUTED            [STMM=NO]
> DB_MEM_THRESH        = 10                  [CUST]
> LOCKLIST            >= 20000               [STMM=NO];[BI=NO]
> MAXLOCKS              = 90                  [STMM=NO];[CUST]
> PCKCACHESZ          >= 5120                [STMM=NO]
> SHEAPTHRES_SHR      >= 20000               [STMM=NO];[BI=NO]

Note:
Set SHEAPTHRES_SHR to the number of your SAP workprocesses multiplied with SORTHEAP but not lower than 20000.

> SORTHEAP            >= 2048                [STMM=NO];[BI=NO]

> DBHEAP                = AUTOMATIC
> CATALOGCACHE_SZ      >= 2560
> LOGBUFSZ             >= 1024
> UTIL_HEAP_SZ         >= 50000
> BUFFPAGE             >= 10000
> STMTHEAP              = AUTOMATIC
> APPLHEAPSZ            = AUTOMATIC
> APPL_MEMORY           = AUTOMATIC
> STAT_HEAP_SZ          = AUTOMATIC

> DLCHKTIME            = 10000              [CUST]
> LOCKTIMEOUT          = 3600               [CUST]

> CHNGPGS_THRESH        = 20                 [CUST]
> NUM_IOCLEANERS        = AUTOMATIC          [CUST]
> NUM_IOSERVERS        = AUTOMATIC          [CUST]
> INDEXSORT             = YES
> SEQDETECT             = YES
> DFT_PREFETCH_SZ       = AUTOMATIC

> TRACKMOD              = ON                 [CUST]
> DFT_EXTENT_SZ        = 2                  [CUST]

> MAXAPPLS              = AUTOMATIC
> AVG_APPLS             = AUTOMATIC
> MAXFILOP            >= 61440              [CUST]

> LOGFILSIZ            = 16380              [CUST]
> LOGPRIMARY           >= 60
> LOGSECOND             = 0

Note:
If you want to configure LOGSECOND to a value other than zero, read SAP Note 1493587 and related SAP FAQ note 1497040.

The primary log files are created at the time of the first database activation or after any change to the log configuration.

We recommend that you do not configure less than 4GB log space. If you change any of the parameters LOGFILSIZ, LOGPRIMARY or LOGSECOND you must ensure that you have enough space allocated in your LOGPATH.

> OVERFLOWLOGPATH      =                    [CUST]
> MIRRORLOGPATH        =                    [CUST]
> BLK_LOG_DSK_FUL      = YES                [CUST]
> BLOCKNONLOGGED        = NO                 [FP>=09.05.0004]

Note:
If you want to set BLOCKNONLOGGED to YES, read SAP Note 1523227 for more information.

> MAX_LOG              = 0                  [CUST]
> NUM_LOG_SPAN          = 0                  [CUST]

Note:
After every change of LOGFILSIZ, LOGPRIMARY or LOGSECOND you should check your settings of MAX_LOG and NUM_LOG_SPAN.

> MINCOMMIT             = 1
> SOFTMAX              = 300                [CUST]

Note:
SOFTMAX is the threshold for the LSN gap page cleaner trigger. The value unit is percent, based on the size of one log file (LOGFILSZ * page size 4 KB).
In case you have set LOGFILSIZ to a large value, you may consider to lower the value of SOFTMAX accordingly. For example, if you have LOGFILSIZ set to 50000 or higher, you may want to reduce SOFTMAX from 300 to 100.

> LOGRETAIN            = OFF                [DEPR]
> USEREXIT              = OFF                [DEPR]

> HADR_LOCAL_HOST        =                  [CUST]
> HADR_LOCAL_SVC        =                   [CUST]
> HADR_REMOTE_HOST      =                   [CUST]
> HADR_REMOTE_SVC        =                  [CUST]
> HADR_REMOTE_INST      =                   [CUST]
> HADR_TIMEOUT          = 120               [CUST]
> HADR_SYNCMODE          = NEARSYNC          [CUST]
> HADR_PEER_WINDOW      = 0                 [CUST]

> LOGARCHMETH1          = OFF               [CUST]

Note:
For information about the DB2 log manager, see SAP Note 913481.

> LOGARCHOPT1            =                  [CUST]
> LOGARCHMETH2          = OFF               [CUST]
> LOGARCHOPT2            =                  [CUST]
> FAILARCHPATH          =                   [CUST]
> NUMARCHRETRY          = 5                 [CUST]
> ARCHRETRYDELAY        = 20                [CUST]
> VENDOROPT              =                  [CUST]

Note:
You must enable your  production databases for recovery. For more information about the DB2 log manager, see SAP Note 913481.

> AUTORESTART            = ON
> INDEXREC               = RESTART
> LOGINDEXBUILD          = OFF               [CUST]

Note:
If you are using HADR, set LOGINDEXBUILD=ON.

> DFT_LOADREC_SES        = 1                 [CUST]
> NUM_DB_BACKUPS        = 12                [CUST]
> REC_HIS_RETENTN        = 60                [CUST]
> AUTO_DEL_REC_OBJ      = OFF               [CUST]

> TSM_MGMTCLASS          =                  [CUST]
> TSM_NODENAME          =                   [CUST]
> TSM_OWNER              =                  [CUST]
> TSM_PASSWORD          =                   [CUST]

> AUTO_MAINT             = ON
> AUTO_DB_BACKUP        = OFF               [CUST]
> AUTO_TBL_MAINT         = ON
> AUTO_RUNSTATS          = ON
> AUTO_STMT_STATS        = ON
> AUTO_STATS_PROF        = OFF
> AUTO_PROF_UPD          = OFF
> AUTO_REORG            = OFF               [CUST]

Note:
For more information about parameter AUTO_REORG, see SAP Note 975352.

> ENABLE_XMLCHAR         = YES
> WLM_COLLECT_INT        = 0

DB2 Registry Variables

You can  list the DB2 registry settings by entering the following command:

db2set -all

To alter the DB2 registriy variables, use the following command:

db2set <PARAMETER> = <VALUE>

To delete existing settings, enter the following command:

db2set <PARAMETER> =

You must set the following registry variable(s) unless you receive different instructions from SAP support:

> DB2_WORKLOAD           = SAP

Note:
Make sure that you do not ovewrite DB2_WORKLOAD settings unless SAP support explicitly requests you to do so. Check that the output of the "db2set -all" command does not show any lines that end with "[O]".

> DB2_PARALLEL_IO        = *                [CUST]

Note:
Only set DB2_PARALLEL_IO=* if the following conditions are both met:

• RAID devices are used for the database

• All tablespaces in the database consist of one single container per stripe set

Settings with DB2's Self Tuning Memory Manager (STMM) Enabled

DB2 "Self Tuning Memory Manager" (STMM) simplifies the task of memory configuration by automatically setting values for several memory configuration parameters. When enabled, the memory tuner dynamically distributes available memory resources among the following memory consumers:

• Buffer pools

• Locking memory

• Package cache

• sort memory.

STMM works within the memory limits that are defined by the DATABASE_MEMORY configuration parameter. The value of this parameter can be automatically tuned as well by setting the value of the DATABASE_MEMORY configuration parameter to AUTOMATIC. If the DATABASE_MEMORY configuration parameter is set to a numeric value, the database uses the amount of memory that has been specified, distributing it across the memory consumers as required. If DATABASE_MEMORY is set to COMPUTED, the total amount of DATABASE_MEMORY is computed on the sum of the initial values of the database memory heaps at database startup time.

You can enable the memory consumers for self tuning as follows:

• For bufferpools, use the ALTER BUFFERPOOL statement (specifying the AUTOMATIC keyword) to enable the bufferpool for self tuning

• For locking memory, set LOCKLIST and MAXLOCKS to AUTOMATIC

• For the package cache, set PCKCACHESZ to AUTOMATIC

• For sort memory, set SHEAPTHRES_SHR and SORTHEAP to AUTOMATIC

Changes resulting from self-tuning operations are recorded in memory tuning log files that are located in the "stmmlog" subdirectory. These log files contain summaries of the resource demands from each memory consumer during specific tuning intervals that are determined by timestamps in the log entries.

If self-tuning memory manager (STMM) is active and DATABASE_MEMORY is set to AUTOMATIC, STMM checks the free memory available on the system and automatically determines how much memory to dedicate to performance heaps for optimal performance. All the performance heaps contribute to the overall DATABASE_MEMORY size. The difference between INSTANCE_MEMORY and DATABASE_MEMORY is available for application memory (APPL_MEMORY) use and is allocated as needed.

When using STMM we recommend that you do the following:

• To assign a dedicated portion of memory to DB2, set INSTANCE_MEMORY to a fixed value.

• To enable self tuning of database shared memory, set DATABASE_MEMORY to AUTOMATIC. Alternatively, you can set DATABASE_MEMORY to a numeric value to assign a fixed region for database shared memory. Do not use DATABASE_MEMORY=COMPUTED with STMM.

STMM is globally controlled by the database manager parameter SELF_TUNING_MEM. If at least two tuneable consumers are available, you activate STMM by setting parameter SELF_TUNING_MEM to ON. Setting parameter SELF_TUNING_MEM to OFF deactivates STMM, regardless if any memory consumers are still enabled for STMM. If STMM is turned off, the consumers will remain at their current size.

You can enable all memory consumers or a subset for STMM. You can deactivate STMM for an individual memory consumer or for all memory consumers at any point in time. For STMM to be active, a minimum of two consumers must be eligible. In general, you should enable all consumers for self-tuning when you activate STMM. This allows DB2 to best distribute the available memory to all consumers.

You can enable individual memory consumers for STMM tuning by setting them to AUTOMATIC. Some consumers are controlled by configuration parameters (DATABASE_MEMORY, LOCKLIST, MAXLOCKS, PCKCACHESZ, SHEAPTHRES_SHR, SORTHEAP). Buffer pools, however, must be explicitely enabled using the following statement:

ALTER BUFFERPOOL <bp_name> SIZE AUTOMATIC

In a distributed database server (DPF) STMM tuning works uniformly. That is, STMM runs on one partition only and propagates parameter changes to all other partitions with SELF_TUNING_MEM enabled. The partition that STMM runs on is determined via the "update stmm tuning dbpartitionnum" command. You can query the currently active STMM partition using the "get stmm tuning dbpartitionnum" command.

Uniform STMM with DPF is typically not suitable for SAP workloads. We recommend that in a distributed database server you only enable STMM tuning on the catalog partition (partition 0). You should tune all other partitions manually. For more information about STMM and DPF, see SAP Note 1132282.

How you activate STMM:

1. Set INSTANCE_MEMORY to a fixed value of least 125000 pages or more using the following command:

db2 update dbm cfg using INSTANCE_MEMORY <value>

2. To enable self tuning for all consumers, update your database manager and database configuration using the following command:

db2 update dbm cfg using SHEAPTHRES 0
db2 update db cfg for <DBSID> using DATABASE_MEMORY AUTOMATIC LOCKLIST AUTOMATIC MAXLOCKS AUTOMATIC PCKCACHESZ AUTOMATIC SORTHEAP AUTOMATIC SHEAPTHRES_SHR AUTOMATIC

3. Enable STMM tuning for all your buffer pools using the following command:

db2 alter bufferpool <bp_name> size AUTOMATIC

4. To activate STMM, enter the following command:

db2 update db cfg for <DBSID> using SELF_TUNING_MEM ON

How you deactivate STMM:

1. To deactivate STMM, enter the following command:

db2 update db cfg for <DBSID> using SELF_TUNING_MEM OFF

2. After you changed SELF_TUNING_MEM to value 'OFF', your database continues to use the settings that were active at the time when STMM was switched off. You can either continue to use these settings or update your configuration to fixed values as needed.

Recommended Database Manager Configuration with STMM Enabled:
=============================================================

> INSTANCE_MEMORY      >= 125000          [STMM=YES]


Recommended Database Configuration with STMM Enabled:
=====================================================

> SELF_TUNING_MEM      = ON              [STMM=YES]
> DATABASE_MEMORY       = AUTOMATIC       [STMM=YES]
> LOCKLIST              = AUTOMATIC       [STMM=YES]
> MAXLOCKS              = AUTOMATIC       [STMM=YES]
> PCKCACHESZ            = AUTOMATIC       [STMM=YES]
> SHEAPTHRES_SHR        = AUTOMATIC       [STMM=YES]
> SORTHEAP              = AUTOMATIC       [STMM=YES]

Typical Parameter Settings for SAP NetWeaver Business Warehouse (SAP NetWeaver BW) and All SAP Systems Based on SAP NetWeaver BW:

Database Manager Configuration Parameters for OLAP workloads:
============================================================

> AGENT_STACK_SZ      >= 4096            [OS=UNIX],[BI=YES]

> MAX_QUERYDEGREE      >= 1               [BI=YES]
> MAX_QUERYDEGREE      <= 4               [BI=YES]
> INTRA_PARALLEL        = NO              [BI=YES];[CUST]

Note:
In general, you should run with INTRA_PARALLEL=NO and MAX_QUERYDEGREE=1 and scale out to multiple database partitions (DPF) to make best use of your CPU power.

Only in the exceptional case that you have 8 CPU cores or more and only one database partition, you can consider to set INTRA_PARALLEL=YES and MAX_QUERYDEGREE to a value between 2 and 4.

Database Configuration Parameters for OLAP Workloads:
=====================================================

> SHEAPTHRES_SHR      >= 40000           [STMM=NO];[BI=YES]

Note:
You should set SHEAPTHRES_SHR to "Number SAP WP" x "SORTHEAP". The value should be at least 40000 pages but no more than max 1/4 of the physical memory that is available for one database partition. We also recommend that you read the IBM DB2 Version 9.5 manuals to understand the dependencies between SHEAPTHRES and SORTHEAP.

> LOCKLIST            >= 40000           [STMM=NO];[BI=YES]
> SORTHEAP            >= 15000           [STMM=NO];[BI=YES]

Note:
For SAP APO/SCM systems, a smaller value of 4096 or more might be sufficient. However, if problems related to sort performance occur or iflarge Infocubes with Aggregates are used, you have to increase the parameter to 15000.