Tuning database configuration settings

You can specify database configuration settings for each individual database in DB2®. Several database configuration settings can affect performance, such as the log file sizes or asynchronous cleaner options.

Procedure

  1. For all operating systems, in the command prompt on the server on which DB2 is installed, run the following commands:
    db2 update db cfg for dbname using CHNGPGS_THRESH 40
    db2 update db cfg for dbname using DFT_QUERYOPT 5
    db2 update db cfg for dbname using LOGBUFSZ 1024
    db2 update db cfg for dbname using LOGFILSIZ 8096
    db2 update db cfg for dbname using LOGPRIMARY 20
    db2 update db cfg for dbname using LOGSECOND 100
    db2 update db cfg for dbname using LOCKLIST AUTOMATIC
    db2 update db cfg for dbname using LOCKTIMEOUT 300
    db2 update db cfg for dbname using NUM_IOCLEANERS AUTOMATIC
    db2 update db cfg for dbname using NUM_IOSERVERS AUTOMATIC
    db2 update db cfg for dbname using SOFTMAX 1000
    db2 update db cfg for dbname using PCKCACHESZ 524288
    db2 update db cfg for dbname using STAT_HEAP_SZ 51200
  2. Set the maximum number of file handles that can be open per application.
    Operating system Commands
    UNIX or Linux db2 update db cfg for dbname using MAXFILOP 61440
    Windows db2 update db cfg for dbname using MAXFILOP 65535
  3. If you recently upgraded to DB2 version 9.7, ensure that the following database configuration settings are set to the following values:
    Setting Value
    CUR_COMMIT ON
    AUTO_REVAL DEFERRED
    DEC_TO_CHAR_FMT NEW
    STMT_CONC LITERALS

What to do next

When the STMT_CONC parameter is set to LITERALS, the statement concentrator is enabled. All queries are translated into queries with parameter markers, which are used by the query optimizer when selecting an access plan. To assist the query optimizer to select an efficient access plan, enable the REOPT(ONCE) bind option.



Feedback