MQSeries.net :: View topic

MaheshPN · Posted: Wed Mar 17, 2004 8:15 pm Post subject:

HI guys,
I am wondering what parameters in Database needs to be tweaked to get the good performance. Like bufferpool, DB configuration parameters and Database manager configuration stuffs. Is there any possibility that, changing some of these parameters might affect the performance?
In general, I am looking for what are the parameters that will be usually tuned in real environment.

Here are the default parameters,

Bufferpool config

BPNAME BUFFERPOOLID NGNAME NPAGES PAGESIZE ESTOR
E
------------------ ------------ ------------------ ----------- ----------- -----
-
IBMDEFAULTBP 1 - 32000 4096 N =====> 131M (all tablespaces, except adttrail, tmp32 and gcontain)

FMCAUDIT 2 - 2000 4096 N ======> 8M (ADTTRAIL tablespace)

FMCBP32 3 - 500 32768 N ======> 16M (TMP32, GCONTAIN tablespace)

DATABASE CONFIGURATION

Database configuration release level = 0x0900
Database release level = 0x0900

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country code = 1

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Directory object name (DIR_OBJ_NAME) =
Discovery support for this database (DISCOVER_DB) = ENABLE

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
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO
Log retain for recovery status = RECOVERY
User exit for logging status = YES

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 20000
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 64
Log buffer size (4KB) (LOGBUFSZ) = 16
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 30000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 1000

Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128

Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 1500
Package cache size (4KB) (PCKCACHESZ) = (MAXAPPLS*

Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 25
Lock timeout (sec) (LOCKTIMEOUT) = -1

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 2
Number of I/O servers (NUM_IOSERVERS) = 6
Index sort flag (INDEXSORT) = YES
Multi-page file allocation enabled = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

Track modified pages (TRACKMOD) = OFF

Default number of containers = 10
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 128

Log file size (4KB) (LOGFILSIZ) = 8190
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 100
Changed path to log files (NEWLOGPATH) =
Path to log files = /udb/db2inst1/uo51/lp05/dblogs/
First active log file = S0000326.LOG

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = RECOVERY
User exit for logging enabled (USEREXIT) = ON

Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM (RESTART)
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

DATABASE MANAGER CONFIGURATION

Node type = Database Server with local and remote clients

Database manager configuration release level = 0x0900

CPU speed (millisec/instruction) (CPUSPEED) = 4.251098e-07

Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) = MQ

Default charge-back account (DFT_ACCOUNT_STR) =

Java Development Kit 1.1 installation path (JDK11_PATH) =

Diagnostic error capture level (DIAGLEVEL) = 3
Diagnostic data directory path (DIAGPATH) = /opt/IBMdb2/home/db2i
nst1/sqllib/db2dump

Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = ON
Lock (DFT_MON_LOCK) = ON
Sort (DFT_MON_SORT) = ON
Statement (DFT_MON_STMT) = ON
Table (DFT_MON_TABLE) = ON
Unit of work (DFT_MON_UOW) = ON

SYSADM group name (SYSADM_GROUP) = DBA
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =

Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT

Default database path (DFTDBPATH) = /opt/IBMdb2/home/db2i
nst1

Database monitor heap size (4KB) (MON_HEAP_SZ) = 56
UDF shared memory set size (4KB) (UDF_MEM_SZ) = 256
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0

Backup buffer default size (4KB) (BACKBUFSZ) = 10000
Restore buffer default size (4KB) (RESTBUFSZ) = 10000

Sort heap threshold (4KB) (SHEAPTHRES) = 20000

Directory cache support (DIR_CACHE) = YES

Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Query heap size (4KB) (QUERY_HEAP_SZ) = 1500
DRDA services heap size (4KB) (DRDA_HEAP_SZ) = 128

Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 200
Agent pool size (NUM_POOLAGENTS) = 4 (calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS) = MAX_COORDAGENTS
Max number of logical agents (MAX_LOGICAGENTS) = MAX_COORDAGENTS

Keep DARI process (KEEPDARI) = YES
Max number of DARI processes (MAXDARI) = MAX_COORDAGENTS
Initialize DARI process with JVM (INITDARI_JVM) = NO
Initial number of fenced DARI process (NUM_INITDARIS) = 0

Index re-creation time (INDEXREC) = RESTART

Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180

SPM name (SPM_NAME) = o5dpwwfv
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) = db2cdb2inst1
APPC Transaction program name (TPNAME) =
IPX/SPX File server name (FILESERVER) =
IPX/SPX DB2 server object name (OBJECTNAME) =
IPX/SPX Socket number (IPX_SOCKET) = 879E

Discovery mode (DISCOVER) = SEARCH
Discovery communication protocols (DISCOVER_COMM) = TCPIP
Discover server instance (DISCOVER_INST) = ENABLE

Directory services type (DIR_TYPE) = NONE
Directory path name (DIR_PATH_NAME) = /.:/subsys/database/
Directory object name (DIR_OBJ_NAME) =
Routing information object name (ROUTE_OBJ_NAME) =
Default client comm. protocols (DFT_CLIENT_COMM) =

Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO

No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 1024
Number of FCM request blocks (FCM_NUM_RQB) = 512
Number of FCM connection entries (FCM_NUM_CONNECT) = (FCM_NUM_RQB * 0.75)
Number of FCM message anchors (FCM_NUM_ANCHORS) = (FCM_NUM_RQB * 0.75)

Any thoughts are appreciated!!!

-Mahesh
IBM Certified Solution Expert â€“ MQWorkflow