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
- 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
- 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 |
- 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.