Tuning DB2 LUW systems, databases, and applications can be sometimes a long drawn out research effort requiring examination of every variable involved. Sometimes the key to the performance is examining the DB2 LUW system installation, its configuration, and the database configuration. This why I call this blog “DB2 Install Next Steps” because if the SYSADMIN or DBA who installed the DB2 LUW would have taken a few extra minutes at the very beginning right after the installation or changed the default configuration parameters, the performance problems might have been avoided.
Below are the after the install “Next Steps Part 1” which can be used to verify your DB2 LUW system installation to avoid performance and recovery issues.
- Understand your starting DB2 LUW system and database configurations. I continue to be amazed at the systems out there that have no documentation or only a little history on their database manager and database specific settings. The first and most important thing to do after the installation and every time a configuration change is done is document the configuration settings for the DB2 LUW instance and the configuration settings for each of the databases defined within the instance. Usually the DB2 LUW default install process defines the SAMPLE database within the DB2 LUW instance. The way to get this information on the instance and database configuration settings is very easily done with a couple of DB2 commands.
- Start with understanding the DB2 LUW instance settings and their impacts. For beginning SYSADMs and DBAs, there are two groups of settings: the DB2 LUW instance has its settings and each database defined within the instance has its own, second set, of configuration settings. This allows granularity of settings so the configuration can be performance tuned to the overall work of the DB2 LUW instance and then further optimized for each of its databases and its associated applications. To get the DB2 LUW instance configuration settings, an authorized SYSADM or DBA needs open a DB2 Command Window session that should present a command prompt to enter the following command: db2 get DBM CFG.
Along with the command in the example below, I like to pipe the command output to a file for safekeeping and documenting the history of different settings. Pick a drive/path and directory that will always be around and a file name that indicates the server, the DB2 Instance version, and it unique Instance number within the network. This helps to troubleshoot issues later on if the DB2 instance is migrated/recovered to other hardware. As an example I usually create and use “>>E:\DB2Doc\SRV43_IV1122_DBMCFG_2017_03_23.txt”. Example of the output is below.
db2 get DBM CFG >>E:\DB2Doc\ SRV43_IV1122_DBMCFG_2017_03_23.txt
The contents of the file below.
Database Manager Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x1400
CPU speed (millisec/instruction) (CPUSPEED) = 1.495757e-007
Max number of concurrently active databases (NUMDB) = 32
Federated Database System Support (FEDERATED) = NO
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit installation path (JDK_PATH) =
C:\PROGRA~1\IBM\SQLLIB\java\jdk
Diagnostic error capture level (DIAGLEVEL) = 3
Notify Level (NOTIFYLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =
C:\DB2T\Databases\\IBM\DB2\DB2COPY1\DB2\
Current member resolved DIAGPATH =
C:\DB2T\Databases\\IBM\DB2\DB2COPY1\DB2\
Alternate diagnostic data directory path (ALT_DIAGPATH) =
Current member resolved ALT_DIAGPATH =
Size of rotating db2diag & notify logs (MB) (DIAGSIZE) = 0
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Timestamp (DFT_MON_TIMESTAMP) = ON
Unit of work (DFT_MON_UOW) = OFF
Monitor health of instance and databases (HEALTH_MON) = OFF
SYSADM group name (SYSADM_GROUP) =
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
SYSMON group name (SYSMON_GROUP) =
Client Userid-Password Plugin (CLNT_PW_PLUGIN) =
Client Kerberos Plugin (CLNT_KRB_PLUGIN) = IBMkrb5
Group Plugin (GROUP_PLUGIN) =
GSS Plugin for Local Authorization (LOCAL_GSSPLUGIN) =
Server Plugin Mode (SRV_PLUGIN_MODE) = UNFENCED
Server List of GSS Plugins (SRVCON_GSSPLUGIN_LIST) =
Server Userid-Password Plugin (SRVCON_PW_PLUGIN) =
Server Connection Authentication (SRVCON_AUTH) = NOT_SPECIFIED
Cluster manager =
Database manager authentication (AUTHENTICATION) = SERVER
Alternate authentication (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Bypass federated authentication (FED_NOAUTH) = NO
Default database path (DFTDBPATH) = C:
Database monitor heap size (4KB) (MON_HEAP_SZ) = AUTOMATIC(66)
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Global instance memory (% or 4KB) (INSTANCE_MEMORY) = AUTOMATIC(838644)
Member instance memory (% or 4KB) = GLOBAL
Agent stack size (AGENT_STACK_SZ) = 16
Sort heap threshold (4KB) (SHEAPTHRES) = 0
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 65535
Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10
Priority of agents (AGENTPRI) = SYSTEM
Agent pool size (NUM_POOLAGENTS) = AUTOMATIC(100)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = AUTOMATIC(200)
Max number of client connections (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS)
Keep fenced process (KEEPFENCED) = YES
Number of pooled fenced processes (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
Initial number of fenced processes (NUM_INITFENCED) = 0
Index re-creation time and redo index build (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) = T495
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) = db2c_DB2
Discovery mode (DISCOVER) = SEARCH
Discover server instance (DISCOVER_INST) = ENABLE
SSL server keydb file (SSL_SVR_KEYDB) =
SSL server stash file (SSL_SVR_STASH) =
SSL server certificate label (SSL_SVR_LABEL) =
SSL service name (SSL_SVCENAME) =
SSL cipher specs (SSL_CIPHERSPECS) =
SSL versions (SSL_VERSIONS) =
SSL client keydb file (SSL_CLNT_KEYDB) =
SSL client stash file (SSL_CLNT_STASH) =
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
Number of FCM buffers (FCM_NUM_BUFFERS) = AUTOMATIC(1024)
FCM buffer size (FCM_BUFFER_SIZE) = 32768
Number of FCM channels (FCM_NUM_CHANNELS) = AUTOMATIC(512)
db2start/db2stop timeout (min) (START_STOP_TIME) = 10
WLM dispatcher enabled (WLM_DISPATCHER) = NO
WLM dispatcher concurrency (WLM_DISP_CONCUR) = COMPUTED
WLM dispatcher CPU shares enabled (WLM_DISP_CPU_SHARES) = NO
WLM dispatcher min. utilization (%) (WLM_DISP_MIN_UTIL) = 5
Communication buffer exit library list (COMM_EXIT_LIST) =
Current effective arch level (CUR_EFF_ARCH_LVL) = V:11 R:1 M:0 F:0 I:0 SB:0
Current effective code level (CUR_EFF_CODE_LVL) = V:11 R:1 M:0 F:0 I:0 SB:0
Keystore type (KEYSTORE_TYPE) = NONE
Keystore location (KEYSTORE_LOCATION) =
To get the individual database configuration settings a DBA for the database needs to connect/log on to the database and issue the simple db2 get DB CFG command.
Again open a DB2 Command session window that should give you a command prompt to enter the following commands. Replace <***user***> with your database user name and <***pwd***> with the password appropriately.
Along with the command, I like to pipe the command output to a file for safe keeping and documenting the history of different settings. Pick a path/drive and directory that will always be around. I usually create and use “>>E:\DB2Doc\ SRV43_IV1122_DBCFG_2017_03_23.txt”.
db2 connect to sample user <**user**> using <**pwd**>
Database Connection Information
Database server = DB2/NT64 11.1.0
SQL authorization ID = DAVE
Local database alias = SAMPLE
db2 get DB CFG >>E:\DB2Doc\ SRV43_IV1122_DBCFG_2017_03_23.txt
The contents of the file below.
Database Configuration for Database
Database configuration release level = 0x1400
Database release level = 0x1400
Database territory = US
Database code page = 1208
Database code set = UTF-8
Database country/region code = 1
Database collating sequence = IDENTITY
Alternate collating sequence (ALT_COLLATE) =
Number compatibility = OFF
Varchar2 compatibility = OFF
Date compatibility = OFF
Database page size = 8192
Statement concentrator (STMT_CONC) = OFF
Discovery support for this database (DISCOVER_DB) = ENABLE
Restrict access = NO
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Default maintained table types for opt (DFT_MTTB_TYPES) = SYSTEM
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Decimal floating point rounding mode (DECFLT_ROUNDING) = ROUND_HALF_EVEN
Backup pending = NO
All committed transactions have been written to disk = YES
Rollforward pending = NO
Restore pending = NO
Upgrade pending = NO
Multi-page file allocation enabled = YES
Log retain for recovery status = NO
User exit for logging status = NO
Self tuning memory (SELF_TUNING_MEM) = OFF
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(51296)
Database memory threshold (DB_MEM_THRESH) = 100
Max storage for lock list (4KB) (LOCKLIST) = 4096
Percent. of lock lists per application (MAXLOCKS) = 22
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*8)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = 5000
Sort list heap (4KB) (SORTHEAP) = 256
Database heap (4KB) (DBHEAP) = AUTOMATIC(600)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = (MAXAPPLS*5)
Log buffer size (4KB) (LOGBUFSZ) = 256
Utilities heap size (4KB) (UTIL_HEAP_SZ) = AUTOMATIC(5000)
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(8192)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(256)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(40000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = AUTOMATIC(4)
Number of I/O servers (NUM_IOSERVERS) = AUTOMATIC(12)
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = AUTOMATIC
Track modified pages (TRACKMOD) = NO
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = AUTOMATIC(40)
Average number of active applications (AVG_APPLS) = AUTOMATIC(1)
Max DB files open per application (MAXFILOP) = 65535
Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 10
Changed path to log files (NEWLOGPATH) =
Path to log files = C:\DB2\NODE0000\SQL00001\LOGSTREAM0000\
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Block non logged operations (BLOCKNONLOGGED) = NO
Percent max primary log space by transaction (MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
Percent log file reclaimed before soft chckpt (SOFTMAX) = 0
Target for oldest page in LBP (PAGE_AGE_TRGT_MCR) = 240
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOST) =
HADR local service name (HADR_LOCAL_SVC) =
HADR remote host name (HADR_REMOTE_HOST) =
HADR remote service name (HADR_REMOTE_SVC) =
HADR instance name of remote server (HADR_REMOTE_INST) =
HADR timeout value (HADR_TIMEOUT) = 120
HADR target list (HADR_TARGET_LIST) =
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR spool log data limit (4KB) (HADR_SPOOL_LIMIT) = AUTOMATIC(0)
HADR log replay delay (seconds) (HADR_REPLAY_DELAY) = 0
HADR peer window duration (seconds) (HADR_PEER_WINDOW) = 0
First log archive method (LOGARCHMETH1) = OFF
Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =
Second log archive method (LOGARCHMETH2) = OFF
Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF
Options for logarchmeth2 (LOGARCHOPT2) =
Failover log archive path (FAILARCHPATH) =
Number of log archive retries on error (NUMARCHRETRY) = 5
Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20
Vendor options (VENDOROPT) =
Auto restart enabled (AUTORESTART) = ON
Index re-creation time and redo index build (INDEXREC) = SYSTEM (RESTART)
Log pages during index build (LOGINDEXBUILD) = OFF
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
Auto deletion of recovery objects (AUTO_DEL_REC_OBJ) = OFF
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
Automatic maintenance (AUTO_MAINT) = ON
Automatic database backup (AUTO_DB_BACKUP) = OFF
Automatic table maintenance (AUTO_TBL_MAINT) = ON
Automatic runstats (AUTO_RUNSTATS) = ON
Real-time statistics (AUTO_STMT_STATS) = ON
Statistical views (AUTO_STATS_VIEWS) = OFF
Automatic sampling (AUTO_SAMPLING) = ON
Automatic reorganization (AUTO_REORG) = OFF
Auto-Revalidation (AUTO_REVAL) = DEFERRED
Currently Committed (CUR_COMMIT) = ON
CHAR output with DECIMAL input (DEC_TO_CHAR_FMT) = NEW
Enable XML Character operations (ENABLE_XMLCHAR) = YES
WLM Collection Interval (minutes) (WLM_COLLECT_INT) = 0
Monitor Collect Settings
Request metrics (MON_REQ_METRICS) = BASE
Activity metrics (MON_ACT_METRICS) = BASE
Object metrics (MON_OBJ_METRICS) = EXTENDED
Routine data (MON_RTN_DATA) = NONE
Routine executable list (MON_RTN_EXECLIST) = OFF
Unit of work events (MON_UOW_DATA) = NONE
UOW events with package list (MON_UOW_PKGLIST) = OFF
UOW events with executable list (MON_UOW_EXECLIST) = OFF
Lock timeout events (MON_LOCKTIMEOUT) = NONE
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE
Lock wait event threshold (MON_LW_THRESH) = 5000000
Number of package list entries (MON_PKGLIST_SZ) = 32
Lock event notification level (MON_LCK_MSG_LVL) = 1
SMTP Server (SMTP_SERVER) =
SQL conditional compilation flags (SQL_CCFLAGS) =
Section actuals setting (SECTION_ACTUALS) = NONE
Connect procedure (CONNECT_PROC) =
Adjust temporal SYSTEM_TIME period (SYSTIME_PERIOD_ADJ) = NO
Log DDL Statements (LOG_DDL_STMTS) = NO
Log Application Information (LOG_APPL_INFO) = NO
Default data capture on new Schemas (DFT_SCHEMAS_DCC) = NO
Default table organization (DFT_TABLE_ORG) = ROW
Default string units (STRING_UNITS) = SYSTEM
National character string mapping (NCHAR_MAPPING) = CHAR_CU32
Database is in write suspend state = NO
Extended row size support (EXTENDED_ROW_SZ) = ENABLE
Encryption Library for Backup (ENCRLIB) =
Encryption Options for Backup (ENCROPTS) =
Encrypted database = NO
Procedural language stack trace (PL_STACK_TRACE) = NONE
HADR SSL certificate label (HADR_SSL_LABEL) =
Yes, this is basic DB2 LUW instance configuration and database configuration documentation. Unfortunately configuration history is something that is always seems to be missing. If you can get into the habit of running these DB2 commands and saving the settings, you will understand your system better. Next we will look at the settings details and discuss their performance implications.
I will be giving two presentation at this year’s IDUG conference. Make your plans and sign up for the IDUG DB2 Technical Conference Anaheim, California up this May 1-4th 2017. Also plan on attending any of my sessions.
- “Performance Enterprise Architectures for Analytic Design Patterns “
Presentation T05 – Monday, May 1st 4:30 – 05:30 PM - “Understanding IDAA performance and justifying an IDAA Appliance”
Presentation B12 – Wednesday – May 3th 2:10 – 3:10 PM
For more details on any of these conference go to www.idug.org and register at http://bit.ly/IDUGNA2017.
Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs. Follow him on Twitter or connect through LinkedIn.
Leave a Reply