Configuring the database (Db2)

Configure the IBM® Db2® database to avoid time-out issues.

Procedure

  1. Open the database console running as a Db2 administrator.
  2. Set the tuning parameters by using the following command: 
    db2 update db cfg for <DATABASE_NAME> using <VARIABLE> <VALUE>

    For example, db2 update db cfg for <DATABASE_NAME> using SELF_TUNING_MEM ON

    The following table describes the tuning parameter settings to use. Values shown in brackets are informational when the parameter is set to AUTOMATIC. This enables Db2 to control the parameter and reflects the current setting.

    Table 1. Database tuning parameters
    Parameter Description Value (default)
    SELF_TUNING_MEM Self tuning memory ON
    DATABASE_MEMORY Size of database shared memory (4KB) AUTOMATIC (2683751)
    DB_MEM_THRESH Database memory threshold 10
    LOCKLIST Max storage for lock list (4KB) AUTOMATIC (82212)
    MAXLOCKS Percentage of lock lists per application AUTOMATIC (97)
    PCKCACHESZ Package cache size (4KB) AUTOMATIC (419456)
    SHEAPTHRES_SHR Sort heap threshold for shared sorts (4KB) AUTOMATIC (63309)
    SORTHEAP Sort list heap (4KB) AUTOMATIC (12661)
    DBHEAP Database heap (4KB) AUTOMATIC (5405)
    CATALOGCACHE_SZ Catalog cache size (4KB) 4000
    LOGBUFSZ Log buffer size (4KB) 2560
    UTIL_HEAP_SZ Utilities heap size (4KB) 306174
    STMTHEAP SQL statement heap (4KB) AUTOMATIC (51200)
    APPLHEAPSZ Default application heap (4KB) AUTOMATIC (51200)
    APPL_MEMORY Application Memory Size (4KB) AUTOMATIC (40000)
    STAT_HEAP_SZ Statistics heap size (4KB) AUTOMATIC (4384)
    DLCHKTIME Interval for checking deadlock (ms) 10000
    LOCKTIMEOUT Lock timeout (sec) -1
    CHNGPGS_THRESH Changed pages threshold 80
    NUM_IOCLEANERS Number of asynchronous page cleaners AUTOMATIC (10)
    NUM_IOSERVERS Number of I/O servers AUTOMATIC (44)
    SEQDETECT Sequential detect flag YES
    DFT_PREFETCH_SZ Default prefetch size (pages) AUTOMATIC
    TRACKMOD Track modified pages NO
      Default number of containers 1
    DFT_EXTENT_SZ Default tablespace extent size (pages) 32
    MAXAPPLS Max number of active applications 300
    AVG_APPLS Average number of active applications 10
    MAXFILOP Max DB files open per application 61440
  3. Save your changes.
  4. Validate the changes using the following command: 
    db2 get db cfg for <DATABASE_NAME>