DB2 process keep going heavy and memory usage high

Dear Sir/Madam,

My server CPU memory always go up to 98% (1,100 mb) and above due to DB2 process heavy...
How to tune and improve the DB2 performance and reduce  CPU memory usage to between 60% to 70%

DB2 process heavy
Chong Chee LeongProgrammerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Chong,

What version of DB2 are you running?
What is the O/S on the underlying server?
How much memory is on that server?
Chong Chee LeongProgrammerAuthor Commented:
Dear Kdo,

Db2 v10.5.100.64
Window Server 2012 Rs
Memory RAM 4GB
system Type : 64-bit Operating System
During working, about 5 developers login and using the server.
Coding using java and checked all connection and resultset closed properly...

Wondering what other reason was cause the memory heavy?
Kent OlsenDBACommented:
4GB is really small by today's standards.  I suspect that you're using DB2 Express-C (freeware) as it has a 4GB limit.  You can tune DB2 to use less memory.

db2>  connect to MyDatabase
db2>  get db cfg

All of the tuning parameters will be displayed.  Most of the memory parameters are probably "automatic", suggesting a default installation.   The default installation will tune DB2 to use the resources near the limits.  That will give DB2 the best overall performance.

Since DB2 Express-C self limits itself to 4GB, it's probably easier (and cheaper) to add some memory to the server and let DB2 have its 4GB.  If that's not an option, scale back some of the memory tuning parameters.

Good Luck!

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Exploring ASP.NET Core: Fundamentals

Learn to build web apps and services, IoT apps, and mobile backends by covering the fundamentals of ASP.NET Core and  exploring the core foundations for app libraries.

Chong Chee LeongProgrammerAuthor Commented:
Dear Kent,

Noted and thanks for the suggestion. Will add on more memory and test on it.

Yes, we using DB2 Express-C (freeware) edition on our live production server.

In live production server, for better performance, which edition we should use to able handle more workload data?

found few other edition available show in IBM website :
Kent OlsenDBACommented:
IBM has 3 completely different distributions of DB2.  All are derived from separate code bases on run on different equipment.

-- DB2 for Z/OS is DB2 for the IBM mainframe.  It's the oldest DB2 (and the first major DBMS to use SQL).
-- DB2 for iSeries is DB2 for the AS/400.  In this environment, DB2 is coupled very tightly with the AS/400 operating system.  It's a lot different than anything other DBMS that you may have used.  That said, the base code is probably the most "scientific" in the DB2 family.  
-- DB2 for LUW.  Often called UDB/LUW.  This is the version the runs on the non-IBM family of processors, under Linux, Unix, or Windows.  DB2 Express-C is in this family.

Unless you've got a large database, or DB2 Express-C proves incapable of meeting your workload, stick with Express-C.  It's free, and it is the same code base as a fully-licensed version.  IBM puts a few limiters on it, just as do all of the major DBMS that offer a free version.  The DB2 limits are pretty generous.

-- No limit on database size.  If you want to design and build a 1TB database, you can do that with DB2 Express-C.  (You might find that you need the more powerful, licensed version, but you can still design and build the database.)  The other DBMS typically limit the database size in the 1GB to 10GB range so building a large test database may not be possible with other DBMS.

-- 16GB total memory.  Express-C won't use more than 16GB of "real" memory, even on a very large server.  The other DBMS limit memory in the 1GB to 4GB range.  Note that this can be deceptive.  If you build a test database with 2GB of row data, the entire database fits into memory and the performance is great no matter which DBMS you're using.  If you're using Oracle or SQL Server, you have to buy a licensed version to expand the database to 50GB.  By the time you get the database built, you're probably locked into the vendor that you chose.

-- 2 CPUS.

-- High availability environments are not supported.

-- Federation is supported to DB2, Informix, and Oracle servers only.  Licensed versions of DB2 support other DBMS.

If DB2 Express-C is powerful enough for you, stick with it.  You can't beat the price.  :)

DB2 Express-C 32-bit and the 64-bit versions prior to 10.5 supported only 4GB.  10.5 is the first version to support 16GB.  (I didn't realize that the limit had been increased when I wrote the comment above.)  You might find that DB2 wants more memory the minute you add some.  If so, let me know and I'll walk you through cutting the memory limits.

Run these three statements and keep the output.

db2 connect to MyDatabase
db2 get dbm cfg >saveit
db2 get db cfg >>saveit

When you get the additional memory we should be able to tune it back to exactly where it is now based on the current settings.

Good Luck!
Chong Chee LeongProgrammerAuthor Commented:
Hi Kent,

Noted and thanks for your helpful info.
Chong Chee LeongProgrammerAuthor Commented:
Dear Sir,

 At the moment, we might still using current system with 4GB memory ram cause new server may take few month to get.
 Base on current server hardware we have, which area of database manager and database configuration setting we can set for better performance?

 Server Hardware and DB2
 Db2 v10.5.100.64
 Window Server 2012 Rs
 Memory RAM 4GB
 system Type : 64-bit Operating System

 Database Manager Configuration

 Node type = Database Server with local and remote clients

 Database manager configuration release level            = 0x1000

 CPU speed (millisec/instruction)             (CPUSPEED) = 2.007463e-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:\ProgramData\IBM\DB2\DB2COPY1\DB2\
 Current member resolved DIAGPATH                        = C:\ProgramData\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 (4KB)          (INSTANCE_MEMORY) = AUTOMATIC(810811)
 Member instance memory (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) = TFSERVER
 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

 No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
 No. of int. communication 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:10 R:5 M:0 F:1 I:0SB:0
 Current effective code level         (CUR_EFF_CODE_LVL) = V:10 R:5 M:0 F:1 I:0SB:31160

 Database Configuration for Database

 Database configuration release level                    = 0x1000
 Database release level                                  = 0x1000

 Database territory                                      = US
 Database code page                                      = 1208
 Database code set                                       = UTF-8
 Database country/region code                            = 1
 Database collating sequence                             = SYSTEM_1252
 Alternate collating sequence              (ALT_COLLATE) =
 Number compatibility                                    = OFF
 Varchar2 compatibility                                  = OFF
 Date compatibility                                      = OFF
 Database page size                                      = 4096

 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    = NO
 Rollforward pending                                     = NO
 Restore 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) = ON
 Size of database shared memory (4KB)  (DATABASE_MEMORY) = AUTOMATIC(94544)
 Database memory threshold               (DB_MEM_THRESH) = 100
 Max storage for lock list (4KB)              (LOCKLIST) = AUTOMATIC(4096)
 Percent. of lock lists per application       (MAXLOCKS) = AUTOMATIC(97)
 Package cache size (4KB)                   (PCKCACHESZ) = AUTOMATIC(18696)
 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(5000)
 Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(1000)

 Database heap (4KB)                            (DBHEAP) = AUTOMATIC(3304)
 Catalog cache size (4KB)              (CATALOGCACHE_SZ) = 300
 Log buffer size (4KB)                        (LOGBUFSZ) = 2149
 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) = 80
 Number of asynchronous page cleaners   (NUM_IOCLEANERS) = AUTOMATIC(2)
 Number of I/O servers                   (NUM_IOSERVERS) = AUTOMATIC(3)
 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(2337)
 Average number of active applications       (AVG_APPLS) = AUTOMATIC(1)
 Max DB files open per application            (MAXFILOP) = 65535

 Log file size (4KB)                         (LOGFILSIZ) = 1024
 Number of primary log files                (LOGPRIMARY) = 13
 Number of secondary log files               (LOGSECOND) = 12
 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) = OFF
     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) = GRAPHIC_CU16
 Database is in write suspend state                      = NO
 Extended row size support             (EXTENDED_ROW_SZ) = ENABLE

 Thanks and Regards,
Kent OlsenDBACommented:
Hi Chong,

By "better performance" do you mean "use less memory"?

If so, you should be able to reduce memory with a single change.  All of the tuning seems to be set to AUTOMATIC so DB2 should scale everything to the new limit

Global instance memory (4KB)          (INSTANCE_MEMORY) = AUTOMATIC(810811)

DB2 is setting the instance memory to 810,811 4KB pages.  That's 3.3G and should be close the the amount of "free" memory on a 4GB system.  Reducing that value by 262,144 reduces the memory by 1GB so changing it to 548,667 DB2 should result in 1GB less to DB2.

If the server will be accessible via the internet, you should set up fenced mode.  The default is unfenced.  Unfenced is the simpler implementation and modestly more efficient.  Procedures run as part of the base DB2 instance.  Fenced mode runs the procedures as separate tasks.  Running as separate a task isolates the DB2 instance from malicious or dangerous code so that an error doesn't affect the server.

Server Plugin Mode                    (SRV_PLUGIN_MODE) = UNFENCED

Chong Chee LeongProgrammerAuthor Commented:
Hi Kent,

Understood and thanks.
Tomas Helgi JohannssonCommented:

You should set the monitors to ON

 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

That way you can take snapshots and figure out where to tune your database.
Note that 80 - 90 % of database performance problems lies in the SQL running against the database. The other 10 - 20 % are database config,  table/index design and other environment limits.

      Tomas Helgi
Chong Chee LeongProgrammerAuthor Commented:
Thanks again Tomas for the explanation. Will try on that.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.