I am in the process of exploring the possibility of providing our products on databases other than Oracle.I am able to migrate the data, procedures etc without too much effort (latest version of DB2 seems to have improved a lot compared to the earlier ones).*
Unfortunately, I have limited knowledge of DB2, especially in setting the server parameters, their impact on an environment with high concurrency etc. As a result, we are getting very high response times for DB2. DB2 being an enterprise-grade database, I feel we are not doing this correctly and that is why we are getting such poor performance from DB2 compared to other databases.
Please find the configurations for my database named “PROF”.
Database Configuration for Database*
Database configuration release level = 0x0d00
Database release level = 0x0d00
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 = ON
Varchar2 compatibility = ON
Date compatibility = ON
Database page size = 4096
Dynamic SQL Query management (DYN_QUERY_MGMT ) = DISABLE
Statement concentrator (STMT_CONC) = OFF
Discovery support for this database (DISCOVER_DB) = ENABLE
Restrict access = NO
Default query optimization class (DFT_QUERYOPT) = 9
Degree of parallelism (DFT_DEGREE) = 2
Continue upon arithmetic exceptions (DFT_SQLMATHWAR N) = NO
Default refresh age (DFT_REFRESH_AG E) = 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_ROUNDIN G) = ROUND_HALF_UP
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_ME M) = ON
Size of database shared memory (4KB) (DATABASE_MEMOR Y) = 153600
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = 6199
Percent. of lock lists per application (MAXLOCKS) = 97
Package cache size (4KB) (PCKCACHESZ) = 9600
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR ) = AUTOMATIC(424)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(64)
Database heap (4KB) (DBHEAP) = 4096
Catalog cache size (4KB) (CATALOGCACHE_S Z) = 300
Log buffer size (4KB) (LOGBUFSZ) = 1200
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 16278
Buffer pool size (pages) (BUFFPAGE) = 15000
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(2048)
Default application heap (4KB) (APPLHEAPSZ) = 4096
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(15001 6)
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 ) = 4
Number of I/O servers (NUM_IOSERVERS) = 6
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_S Z) = AUTOMATIC
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 1200
Average number of active applications (AVG_APPLS) = 500
Max DB files open per application (MAXFILOP) = 30720
Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 13
Number of secondary log files (LOGSECOND) = 4
Changed path to log files (NEWLOGPATH) =*
Path to log files = /home/db2inst/db2inst/NODE0000/SQL00002/SQLOGDIR/
Overflow log path (OVERFLOWLOGPAT H) =*
Mirror log path (MIRRORLOGPATH) =*
First active log file =*
Block log on disk full (BLK_LOG_DSK_FU L) = 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_SPA N) = 0
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOS T) =*
HADR local service name (HADR_LOCAL_SVC ) =*
HADR remote host name (HADR_REMOTE_HO ST) =*
HADR remote service name (HADR_REMOTE_SV C) =*
HADR instance name of remote server (HADR_REMOTE_IN ST) =*
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds) (HADR_PEER_WIND OW) = 0
First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =*
Second log archive method (LOGARCHMETH2) = 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_SE S) = 1
Number of database backups to retain (NUM_DB_BACKUPS ) = 12
Recovery history retention (days) (REC_HIS_RETENT N) = 366
Auto deletion of recovery objects (AUTO_DEL_REC_O BJ) = 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
Automatic statement statistics (AUTO_STMT_STAT S) = ON
Automatic statistics profiling (AUTO_STATS_PRO F) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF
Auto-Revalidation (AUTO_REVAL) = DEFERRED_FORCE
Currently Committed (CUR_COMMIT) = ON
CHAR output with DECIMAL input (DEC_TO_CHAR_FM T) = NEW
Enable XML Character operations (ENABLE_XMLCHAR ) = YES
WLM Collection Interval (minutes) (WLM_COLLECT_IN T) = 0
Monitor Collect Settings
Request metrics (MON_REQ_METRIC S) = BASE
Activity metrics (MON_ACT_METRIC S) = BASE
Object metrics (MON_OBJ_METRIC S) = BASE
Unit of work events (MON_UOW_DATA) = NONE
Lock timeout events (MON_LOCKTIMEOU T) = NONE
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE
Lock wait event threshold (MON_LW_THRESH) = 5000000
Please find the apache JMeter output for a flow of one application
Number of Threads: 50
Ramp up Period: 1
See the last seven threads (shown in bold). The time taken is little big. Rest is OK
1. Samples Start Time Thread Name Label Sample Time(ms) Status
1 30:04.7 Thread Group 1-3 Java Request 493 Success
2 30:04.8 Thread Group 1-5 Java Request 638 Success
3 30:04.7 Thread Group 1-2 Java Request 708 Success
4 30:04.7 Thread Group 1-4 Java Request 925 Success
5 30:04.8 Thread Group 1-8 Java Request 884 Success
6 30:04.9 Thread Group 1-11 Java Request 995 Success
7 30:04.7 Thread Group 1-1 Java Request 1296 Success
8 30:04.9 Thread Group 1-12 Java Request 1091 Success
9 30:04.8 Thread Group 1-9 Java Request 1219 Success
10 30:04.9 Thread Group 1-14 Java Request 1314 Success
11 30:04.9 Thread Group 1-10 Java Request 1518 Success
12 30:05.2 Thread Group 1-26 Java Request 1190 Success
13 30:05.4 Thread Group 1-36 Java Request 1046 Success
14 30:05.0 Thread Group 1-15 Java Request 1488 Success
15 30:05.3 Thread Group 1-33 Java Request 1175 Success
16 30:05.2 Thread Group 1-25 Java Request 1522 Success
17 30:04.9 Thread Group 1-13 Java Request 1814 Success
18 30:05.0 Thread Group 1-16 Java Request 1753 Success
19 30:05.1 Thread Group 1-23 Java Request 1715 Success
20 30:05.1 Thread Group 1-22 Java Request 1776 Success
21 30:05.3 Thread Group 1-31 Java Request 1611 Success
22 30:05.4 Thread Group 1-34 Java Request 1607 Success
23 30:05.5 Thread Group 1-39 Java Request 1699 Success
24 30:05.1 Thread Group 1-21 Java Request 2094 Success
25 30:05.5 Thread Group 1-40 Java Request 1716 Success
26 30:05.1 Thread Group 1-19 Java Request 2156 Success
27 30:05.5 Thread Group 1-42 Java Request 1680 Success
28 30:05.6 Thread Group 1-44 Java Request 1654 Success
29 30:05.7 Thread Group 1-49 Java Request 1571 Success
30 30:05.6 Thread Group 1-43 Java Request 1713 Success
31 30:05.4 Thread Group 1-35 Java Request 1903 Success
32 30:05.2 Thread Group 1-27 Java Request 2070 Success
33 30:05.7 Thread Group 1-50 Java Request 1603 Success
34 30:05.4 Thread Group 1-37 Java Request 1916 Success
35 30:05.5 Thread Group 1-41 Java Request 1834 Success
36 30:05.6 Thread Group 1-46 Java Request 1749 Success
37 30:05.0 Thread Group 1-17 Java Request 2359 Success
38 30:05.1 Thread Group 1-20 Java Request 2369 Success
39 30:05.0 Thread Group 1-18 Java Request 2434 Success
40 30:05.4 Thread Group 1-38 Java Request 2691 Success
41 30:05.6 Thread Group 1-45 Java Request 2615 Success
42 30:05.3 Thread Group 1-29 Java Request 3140 Success
43 30:05.3 Thread Group 1-32 Java Request 3809 Success
44 30:04.8 Thread Group 1-6 Java Request 35668 Success
45 30:05.6 Thread Group 1-47 Java Request 48985 Success
46 30:05.2 Thread Group 1-24 Java Request 52014 Success
47 30:05.7 Thread Group 1-48 Java Request 51951 Success
48 30:05.3 Thread Group 1-30 Java Request 57415 Success
49 30:05.2 Thread Group 1-28 Java Request 58674 Success
50 30:04.8 Thread Group 1-7 Java Request 60648 Success
Please help me resolve this issue f high response time towards the end.
Unfortunately, I have limited knowledge of DB2, especially in setting the server parameters, their impact on an environment with high concurrency etc. As a result, we are getting very high response times for DB2. DB2 being an enterprise-grade database, I feel we are not doing this correctly and that is why we are getting such poor performance from DB2 compared to other databases.
Please find the configurations for my database named “PROF”.
Database Configuration for Database*
Database configuration release level = 0x0d00
Database release level = 0x0d00
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 = ON
Varchar2 compatibility = ON
Date compatibility = ON
Database page size = 4096
Dynamic SQL Query management (DYN_QUERY_MGMT ) = DISABLE
Statement concentrator (STMT_CONC) = OFF
Discovery support for this database (DISCOVER_DB) = ENABLE
Restrict access = NO
Default query optimization class (DFT_QUERYOPT) = 9
Degree of parallelism (DFT_DEGREE) = 2
Continue upon arithmetic exceptions (DFT_SQLMATHWAR N) = NO
Default refresh age (DFT_REFRESH_AG E) = 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_ROUNDIN G) = ROUND_HALF_UP
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_ME M) = ON
Size of database shared memory (4KB) (DATABASE_MEMOR Y) = 153600
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = 6199
Percent. of lock lists per application (MAXLOCKS) = 97
Package cache size (4KB) (PCKCACHESZ) = 9600
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR ) = AUTOMATIC(424)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(64)
Database heap (4KB) (DBHEAP) = 4096
Catalog cache size (4KB) (CATALOGCACHE_S Z) = 300
Log buffer size (4KB) (LOGBUFSZ) = 1200
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 16278
Buffer pool size (pages) (BUFFPAGE) = 15000
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(2048)
Default application heap (4KB) (APPLHEAPSZ) = 4096
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(15001 6)
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 ) = 4
Number of I/O servers (NUM_IOSERVERS) = 6
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_S Z) = AUTOMATIC
Track modified pages (TRACKMOD) = OFF
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 1200
Average number of active applications (AVG_APPLS) = 500
Max DB files open per application (MAXFILOP) = 30720
Log file size (4KB) (LOGFILSIZ) = 1024
Number of primary log files (LOGPRIMARY) = 13
Number of secondary log files (LOGSECOND) = 4
Changed path to log files (NEWLOGPATH) =*
Path to log files = /home/db2inst/db2inst/NODE0000/SQL00002/SQLOGDIR/
Overflow log path (OVERFLOWLOGPAT H) =*
Mirror log path (MIRRORLOGPATH) =*
First active log file =*
Block log on disk full (BLK_LOG_DSK_FU L) = 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_SPA N) = 0
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 520
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
HADR database role = STANDARD
HADR local host name (HADR_LOCAL_HOS T) =*
HADR local service name (HADR_LOCAL_SVC ) =*
HADR remote host name (HADR_REMOTE_HO ST) =*
HADR remote service name (HADR_REMOTE_SV C) =*
HADR instance name of remote server (HADR_REMOTE_IN ST) =*
HADR timeout value (HADR_TIMEOUT) = 120
HADR log write synchronization mode (HADR_SYNCMODE) = NEARSYNC
HADR peer window duration (seconds) (HADR_PEER_WIND OW) = 0
First log archive method (LOGARCHMETH1) = OFF
Options for logarchmeth1 (LOGARCHOPT1) =*
Second log archive method (LOGARCHMETH2) = 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_SE S) = 1
Number of database backups to retain (NUM_DB_BACKUPS ) = 12
Recovery history retention (days) (REC_HIS_RETENT N) = 366
Auto deletion of recovery objects (AUTO_DEL_REC_O BJ) = 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
Automatic statement statistics (AUTO_STMT_STAT S) = ON
Automatic statistics profiling (AUTO_STATS_PRO F) = OFF
Automatic profile updates (AUTO_PROF_UPD) = OFF
Automatic reorganization (AUTO_REORG) = OFF
Auto-Revalidation (AUTO_REVAL) = DEFERRED_FORCE
Currently Committed (CUR_COMMIT) = ON
CHAR output with DECIMAL input (DEC_TO_CHAR_FM T) = NEW
Enable XML Character operations (ENABLE_XMLCHAR ) = YES
WLM Collection Interval (minutes) (WLM_COLLECT_IN T) = 0
Monitor Collect Settings
Request metrics (MON_REQ_METRIC S) = BASE
Activity metrics (MON_ACT_METRIC S) = BASE
Object metrics (MON_OBJ_METRIC S) = BASE
Unit of work events (MON_UOW_DATA) = NONE
Lock timeout events (MON_LOCKTIMEOU T) = NONE
Deadlock events (MON_DEADLOCK) = WITHOUT_HIST
Lock wait events (MON_LOCKWAIT) = NONE
Lock wait event threshold (MON_LW_THRESH) = 5000000
Please find the apache JMeter output for a flow of one application
Number of Threads: 50
Ramp up Period: 1
See the last seven threads (shown in bold). The time taken is little big. Rest is OK
1. Samples Start Time Thread Name Label Sample Time(ms) Status
1 30:04.7 Thread Group 1-3 Java Request 493 Success
2 30:04.8 Thread Group 1-5 Java Request 638 Success
3 30:04.7 Thread Group 1-2 Java Request 708 Success
4 30:04.7 Thread Group 1-4 Java Request 925 Success
5 30:04.8 Thread Group 1-8 Java Request 884 Success
6 30:04.9 Thread Group 1-11 Java Request 995 Success
7 30:04.7 Thread Group 1-1 Java Request 1296 Success
8 30:04.9 Thread Group 1-12 Java Request 1091 Success
9 30:04.8 Thread Group 1-9 Java Request 1219 Success
10 30:04.9 Thread Group 1-14 Java Request 1314 Success
11 30:04.9 Thread Group 1-10 Java Request 1518 Success
12 30:05.2 Thread Group 1-26 Java Request 1190 Success
13 30:05.4 Thread Group 1-36 Java Request 1046 Success
14 30:05.0 Thread Group 1-15 Java Request 1488 Success
15 30:05.3 Thread Group 1-33 Java Request 1175 Success
16 30:05.2 Thread Group 1-25 Java Request 1522 Success
17 30:04.9 Thread Group 1-13 Java Request 1814 Success
18 30:05.0 Thread Group 1-16 Java Request 1753 Success
19 30:05.1 Thread Group 1-23 Java Request 1715 Success
20 30:05.1 Thread Group 1-22 Java Request 1776 Success
21 30:05.3 Thread Group 1-31 Java Request 1611 Success
22 30:05.4 Thread Group 1-34 Java Request 1607 Success
23 30:05.5 Thread Group 1-39 Java Request 1699 Success
24 30:05.1 Thread Group 1-21 Java Request 2094 Success
25 30:05.5 Thread Group 1-40 Java Request 1716 Success
26 30:05.1 Thread Group 1-19 Java Request 2156 Success
27 30:05.5 Thread Group 1-42 Java Request 1680 Success
28 30:05.6 Thread Group 1-44 Java Request 1654 Success
29 30:05.7 Thread Group 1-49 Java Request 1571 Success
30 30:05.6 Thread Group 1-43 Java Request 1713 Success
31 30:05.4 Thread Group 1-35 Java Request 1903 Success
32 30:05.2 Thread Group 1-27 Java Request 2070 Success
33 30:05.7 Thread Group 1-50 Java Request 1603 Success
34 30:05.4 Thread Group 1-37 Java Request 1916 Success
35 30:05.5 Thread Group 1-41 Java Request 1834 Success
36 30:05.6 Thread Group 1-46 Java Request 1749 Success
37 30:05.0 Thread Group 1-17 Java Request 2359 Success
38 30:05.1 Thread Group 1-20 Java Request 2369 Success
39 30:05.0 Thread Group 1-18 Java Request 2434 Success
40 30:05.4 Thread Group 1-38 Java Request 2691 Success
41 30:05.6 Thread Group 1-45 Java Request 2615 Success
42 30:05.3 Thread Group 1-29 Java Request 3140 Success
43 30:05.3 Thread Group 1-32 Java Request 3809 Success
44 30:04.8 Thread Group 1-6 Java Request 35668 Success
45 30:05.6 Thread Group 1-47 Java Request 48985 Success
46 30:05.2 Thread Group 1-24 Java Request 52014 Success
47 30:05.7 Thread Group 1-48 Java Request 51951 Success
48 30:05.3 Thread Group 1-30 Java Request 57415 Success
49 30:05.2 Thread Group 1-28 Java Request 58674 Success
50 30:04.8 Thread Group 1-7 Java Request 60648 Success
Please help me resolve this issue f high response time towards the end.
Comment