Results 1 to 3 of 3

Thread: DB2 Crawling

  1. #1
    Join Date
    Sep 2003

    Unanswered: DB2 Crawling

    Hi to everyone,

    Our DB2 server is crawling - can anybody give us some idea on how to optimize DB2 and maximize the use of our hardware?

    We have a DB2 UDB 8.1.6, running under Windows 2003, with 6 GB of Ram and 3 physical Xeon processor.

    For comparison purpose: In MS SQL2000 server properties, I can select 3 processor and adjust the fixed memory size, how can I do this in DB2? or what is the equivalent in DB2?

    Can anybody recommend the right value of the parameters or can give some directions or links.

    Attached is our DB2 configuration.

    Thanks in advance...


    ================================================== =======
    Database Connection Information

    Database server = DB2/NT 8.1.6
    SQL authorization ID =
    Local database alias =
    Database Configuration for Database
    Database configuration release level = 0x0a00
    Database release level = 0x0a00
    Database territory = US
    Database code page = 1208
    Database code set = UTF-8
    Database country/region code = 1
    Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
    Discovery support for this database (DISCOVER_DB) = ENABLE
    Default query optimization class (DFT_QUERYOPT) = 0
    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
    Multi-page file allocation enabled = NO
    Log retain for recovery status = NO
    User exit for logging status = NO
    Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
    Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 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) = 1000
    Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
    Catalog cache size (4KB) (CATALOGCACHE_SZ) = 64
    Log buffer size (4KB) (LOGBUFSZ) = 512
    Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
    Buffer pool size (pages) (BUFFPAGE) = 8000
    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) = 2000
    Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 30000
    Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
    Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 1024
    Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
    Sort list heap (4KB) (SORTHEAP) = 128
    SQL statement heap (4KB) (STMTHEAP) = 8000
    Default application heap (4KB) (APPLHEAPSZ) = 6000
    Package cache size (4KB) (PCKCACHESZ) = 20000
    Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
    Interval for checking deadlock (ms) (DLCHKTIME) = 1000
    Percent. of lock lists per application (MAXLOCKS) = 22
    Lock timeout (sec) (LOCKTIMEOUT) = 60
    Changed pages threshold (CHNGPGS_THRESH) = 60
    Number of asynchronous page cleaners (NUM_IOCLEANERS) = 30
    Number of I/O servers (NUM_IOSERVERS) = 12
    Index sort flag (INDEXSORT) = YES
    Sequential detect flag (SEQDETECT) = NO
    Default prefetch size (pages) (DFT_PREFETCH_SZ) = 16
    Track modified pages (TRACKMOD) = OFF
    Default number of containers = 1
    Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
    Max number of active applications (MAXAPPLS) = 5000
    Average number of active applications (AVG_APPLS) = 50
    Max DB files open per application (MAXFILOP) = 500
    Log file size (4KB) (LOGFILSIZ) = 1000
    Number of primary log files (LOGPRIMARY) = 10
    Number of secondary log files (LOGSECOND) = 64
    Changed path to log files (NEWLOGPATH) =
    Path to log files =
    Overflow log path (OVERFLOWLOGPATH) =
    Mirror log path (MIRRORLOGPATH) =
    First active log file =
    Block log on disk full (BLK_LOG_DSK_FUL) = NO
    Percent of max active log space by transaction(MAX_LOG) = 0
    Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0
    Group commit count (MINCOMMIT) = 1
    Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
    Log retain for recovery enabled (LOGRETAIN) = OFF
    User exit for logging enabled (USEREXIT) = OFF
    Auto restart enabled (AUTORESTART) = ON
    Index re-creation time (INDEXREC) = SYSTEM (ACCESS)
    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
    TSM management class (TSM_MGMTCLASS) =
    TSM node name (TSM_NODENAME) =
    TSM owner (TSM_OWNER) =
    TSM password (TSM_PASSWORD) =

  2. #2
    Join Date
    Oct 2004
    You can use the control center to determine the right values for your system.
    Select the Db2 instance --> the Database ---> right click --> Configure performance using Wizard.
    Now put the proper details for your server and database.

    It can give you some idea about the probable expected values of your configuration parameters which can improve the performance of your system.


  3. #3
    Join Date
    Jan 2003
    Provided Answers: 1

    bga: there was one very interesting thread in this forum few months ago. See the following thread How can I improve DB2 database performance?

    Hope this helps,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts