DB2 database configuration

This topic provides information about configuring and monitoring DB2 database.

Table 1. Recommended IBM DB2 registry variables
Parameter Recommended value
DB2_PARALLEL_IO ON
Table 2. Recommended IBM DB2 DBM CFG Parameters
Parameter Recommended value
HEALTH_MON OFF
MON_HEAP_SZ AUTOMATIC
INSTANCE_MEMORY AUTOMATIC
NUM_POOLAGENTS AUTOMATIC
MAX_COORDAGENTS AUTOMATIC
MAX_CONNECTIONS AUTOMATIC
FCM_NUM_BUFFERS AUTOMATIC
FCM_NUM_CHANNELS AUTOMATIC
MAX_CONNECTIONS AUTOMATIC
MAX_COORDAGENTS AUTOMATIC
   
Table 3. Mandatory IBM DB2 DB CFG parameters
Parameter Recommended value
Database code page 1208
Database code set UTF-8
Database collating sequence IDENTITY
NCHAR_MAPPING GRAPHIC_CU16
Table 4. Recommended IBM DB2 DB CFG parameters
Parameter Recommended value
Database page size 4096
CHNGPGS_THRESH 80
SELF_TUNING_MEM ON
LOCKLIST AUTOMATIC
LOGBUFSZ 2150
MAXLOCKS AUTOMATIC
PCKCACHESZ AUTOMATIC
SHEAPTHRES_SHR AUTOMATIC
SORTHEAP AUTOMATIC
UTIL_HEAP_SZ AUTOMATIC
AUTO_MAINT ON
AUTO_TBL_MAINT ON
AUTO_RUNSTATS ON
AUTO_STMT_STATS ON

The following table mentions DB2 DB CFG minimum log file parameters. It is suggested that you monitor and update these parameters to suit your requirements.

Table 5. Suggested IBM DB2 DB CFG log file parameters
Parameter Recommended value
LOGFILSIZ 51200
LOGPRIMARY 5
LOGSECOND 5
   
Database bufferpool requirements:
  • 4K Pagesize Bufferpool
  • 8K Pagesize Bufferpool
Note: Based on the data, create a 16k or a 32k Pagesize Bufferpool.
Database tablespace requirements:
  • 4K Pagesize Regular Tablespace
  • 4K Pagesize System Temporary Tablespace
  • 8K Pagesize System Temporary Tablespace
  • 4K Pagesize User Temporary Tablespace
Note: Based on the data, create a 16k or a 32k Pagesize Regular Tablespace.
Basic Database privileges required for the db user (master schema user and test mode schema user):
db2 GRANT USE OF TABLESPACE <4K pagesize regular tablespace> TO USER <user>;
db2 GRANT CREATETAB,BINDADD,CONNECT ON DATABASE TO USER <user>;