Database server

• To establish a basic DB2 database parameter configuration set, you can use the DB2 autoconfigure command. This command calculates and displays values for the buffer pool size, database configuration and database manager configuration parameters. The values can be immediately applied or just displayed. Based on this setup, you can start with the fine tuning of the database setup.

  Example of a DB2 autoconfigure command:

  db2 autoconfigure using mem_percent 90 workload_type simple num_stmts 200 Tpm 6000
  num_local_apps 40 num_remote_apps 40 isolation rr apply none

• Fine tuning of the database base setup is usually done by changing the database configuration (db) parameters. Some db parameters useful for tuning are:

       LOGPRIMARY/
       LOGFILSIZ      - High transaction rates for OLTP workloads require a larger log buffer.
       CHNGPGS_THRESH - For databases with a heavy update transaction workload, you can generally ensure that
                        that there are enough clean pages in the buffer pool by setting the parameter value equal
                        or less than the default value.
       DBHEAP         - Database heap per database. Needs to be increased for larger buffer pools.
       LOCKLIST       - The amount of storage that is allocated to the lock list. If lock escalations (warnings in
                        the db2diag.log file) are causing performance concerns you may need to increase the value.
       NUM_IOCLEANERS - For example very large buffer pools require a higher number of asynchronous page cleaners.

• Alternate page cleaning algorithm - DB2 UDB ESE v8.2 introduces a new buffer pool page cleaning algorithm, which is not turned on by default. You might want to test the new page algorithm for your database workload. The alternate page cleaning algorithm can be turned on with the following command:

  db2set DB2_USE_ALTERNATE_PAGE_CLEANING=YES

• The chart below is an example for tuning a database system running an OLTP workload with a high number of update transactions and randomized read requests. The throughput rate is nearly doubled by some basic adaptions.

  

Back to top