Fine-tuning the database

Use this task to fine-tune the database.

Before you begin

The business process container and business processes must be running.

Why and when to perform this task

A common problem is that the database runs out of lock list space, resulting in lock escalation, which severely impacts performance. Depending on the structure of the business processes run, you might therefore need to customize the settings of certain performance-related parameters in your database management system.
Note: If you are not using DB2®, refer to your database management system documentation for information about monitoring the performance of the database, identifying and eliminating bottlenecks, and fine-tuning its performance. The rest of this topic offers advice for DB2 users.

Steps for this task

  1. Tune the lock list space, to help ensure optimum performance.
    Check the db2diag.log file for your DB2 instance. Look for entries like the following example:
    2005-07-24-15.53.42.078000 Instance:DB2 Node:000 
    PID:2352(db2syscs.exe) TID:4360 Appid:*LOCAL.DB2.027785142343 
    data management sqldEscalateLocks Probe:4 Database:BPEDB 
    
    ADM5503E The escalation of "10" locks on table "DB2ADMIN.ACTIVITY_INSTANCE_B_T" 
    to lock intent "X" has failed. The SQLCODE is "-911".

    This type of message indicates that the parallelism for business process applications has improved to the point where the number of available locks is now too small. Increase the LOCKLIST value to approximately 10 * p, where p is your estimate for the maximum number of parallel JDBC connections that are required at any time.

    For example, if you sized your Business Process Choreographer database, BPEDB, with a value of p=50, enter the following command:
    db2 UPDATE DB CFG FOR BPEDB USING LOCKLIST 500
  2. If you used the DB2 configuration advisor, your database throughput is already good. You can, however, further improve the performance, in the following ways:
    • Follow the best practices for database tuning that are described in the DB2 online documentation, books, and articles.
    • Use DB2 monitors, and examine the db2diag.log file for more information about bottlenecks within the database.
    • Regularly run runstats on your database.
    • Tune the following DB2 parameters:
      LOCKLIST
      See the description in step 1.
      AVG_APPLS
      It is better to set this parameter too high rather than too low. For example, if there are a maximum of 20 connected applications, set AVG_APPLS to 50.
      LOGBUFSZ
      Increasing the size of the buffer for the DB2 log decreases how often a full log buffer must be written to disk.
      LOG_FILSIZ
      Increasing the size of the log files reduces how often they are switched.

Result

Your long-running processes are running as fast as possible under the current environment and loading conditions.

Terms of use |

Last updated: Thu Apr 27 14:56:27 2006

(c) Copyright IBM Corporation 2006.
This information center is powered by Eclipse technology (http://www.eclipse.org)