IBM BPM, V8.0.1, All platforms > Tuning > Advanced tuning > Business Process Choreographer tuning > Tuning BPEL processes > Tuning long-running processes > Tuning the Business Process Choreographer database

Tuning the DB2 database for Business Process Choreographer

You can improve the performance of your BPEL processes by tuning your database.

Verify that you have deleted or archived runtime objects that are no longer needed, by performing Remove unnecessary objects from the Business Process Choreographer database.

The suggestions here only apply to tuning the Business Process Choreographer database to make long-running processes run as fast as possible. Refer to your database management system documentation for more information about monitoring the performance of the database, identifying and eliminating problems, and fine-tuning its performance.


Procedure

  1. Assign sizes to buffer pools according to their usage and hit ratio

    The buffer pool hit ratio indicates the percentage of database requests that can be satisfied from data that is already in the pool. It should be close to 100 percent, but any value over 90 percent is acceptable. Increase the SIZE parameter for a buffer pool until you get a satisfactory hit rate. Monitor the total memory allocation. If you make the buffer pool too large, the system starts to swap. In this case, decrease the size of the buffer pool, or make additional memory available.

    Consult your database administrator for buffer pool hit ratios and lock sizes.

    If you are using DB2 Version 9, use the BP_HITRATIO administrative view to retrieve the hit ratio information. For more information on this view, refer to the DB2 V9 information center.

    The DB2 Configuration Advisor suggests values for buffer pool sizes, the Business Process Choreographer database, by default, uses only the IBMDEFAULTBP default buffer pool. You can set the size of this buffer pool using the following command: 

    DB2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE 120000
    This command displays buffer pools with their size in pages, and the size of each page: 

    DB2 select BPNAME, NPAGES, PAGESIZE from syscat.bufferpools

  2. If you used the DB2 Configuration Advisor, your database throughput is probably pretty 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.
    • Tune the following DB2 parameters:

      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.

      DLCHKTIME

      This parameter specifies the deadlock detection time frame. The default is 10 seconds.

      LOCKTIMEOUT

      This parameter specifies the time an application waits for a lock. The default is -1, which means that the application waits until the lock is granted, or a deadlock occurs. The value of this parameter should be always greater than the value of the DLCHKTIME parameter so that a deadlock is reported as a deadlock and not as a lock timeout. A good initial value for this parameter is 30 seconds.

      You might want to set the value higher if load tests show transaction times to be longer than 30 seconds.

      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.

  3. Adjust database and database manager settings according to workload requirements. After the configuration advisor has configured the database, you can also tune the following settings:

    MINCOMMIT

    A value of 1 is strongly recommended. The DB2 Configuration Advisor might suggest other values.

    NUM_IOCLEANERS

    For query-only applications set the value to 0, for regular processing use values between 1 and the number of disk drives in the system (see also the NUM_IOSERVERS parameter). For large buffer pools a higher number is usually beneficial.

    NUM_IOSERVERS

    Must match the number of physical disks that the database resides on. You should have at least as many IO servers as you have disks. IO servers do not use many system resources, so it is better to set a value that is too high rather than too low.
  4. You can improve the performance of complex Business Process Choreographer API queries by turning on reoptimization for prepared statements. This requires creating a "NULLIDR1" package in the Business Process Choreographer database.

    1. Update DB2 statistics for your database.

      After initially putting load on your system, or whenever the data volume in the database changes significantly, consider updating the DB2 system catalog tables that contain the statistics. Use the RUNSTATS command to update the statistics.

      The RUNSTATS command is best run using a script. The following example shows such a script. It assumes that you are logged on as the user bpeuser with the password password, and you are connected to the Business Process Choreographer database, BPEDB. The DB2 commands generate a Windows command file that updates the statistics for all of the tables in the relevant table spaces in the BPEDB database. The TEMPLATE table space tables are omitted because the information is not accessed or updated frequently. 

      db2 -x "select 'db2 runstats on table '
        concat rtrim(tabschema)
        concat '.'
        concat tabname
        concat ' with distribution and detailed indexes all ' 
from syscat.tables 
where
        type='T'  AND
         tabname not in ('SAVED_ENGINE_MESSAGE_B_T') AND
         TBSPACEID IN (
              select TBSPACEID from sysibm.systablespaces 
              where TBSPACE IN ('INSTANCE', 'WORKITEM', 'BPETS8K'
                    'STAFFQRY', 'AUDITLOG', 'SCHEDTS'))" 
> runStatsScript.cmd

echo db2 connect reset >> runStatsScript.sql

      • The select clause "IN ('INSTANCE', ..., 'SCHEDTS')" contains the names of the default table spaces that are created and used when creating the BPEDB database. If in your environment the tables are located in different table spaces, change the select clause accordingly.

      • For larger databases with more than 500 000 process instances, you can accelerate the collection of statistics by replacing the 'with distribution and detailed indexes all' statement with the 'with distribution and sampled detailed indexes all' statement.

      The resulting SQL file updates the statistics for the specified tables. It contains entries similar to the following: 

      db2 runstats on table BPEUSER.ACTIVITY_INSTANCE_B_T with distribution and 
 detailed indexes all 
db2 runstats on table BPEUSER.AUDIT_LOG_T with distribution and 
 detailed indexes all 
... 
db2 connect reset

      You might want to extend the SQL file to run the REORG command before calling the RUNSTATS command. Refer to the DB2 documentation for information on how to reorganize your database tables using the REORG command.

    2. Run the SQL script by entering the following command: 
      db2 -f runStatsScript.sql

    3. Create the package "NULLIDR1" in the BPEDB database. Change to the bnd directory of your DB2 installation, and enter the following commands: 
      db2 connect to BPEDB
db2 bind db2clipk.bnd collection NULLIDR1
    4. Customize the BPEDB datasource. Using the administrative console, navigate to the custom properties page for the BPEDB data source, and set the value of the property currentPackageSet to NULLIDR1 .
  5. Avoid deadlocks.

    Deadlocks happen when at least two transactions block each other's resource access. Deadlocks can result from poor database configuration. They can also result from the way in which the Business Process Choreographer APIs are used. To avoid deadlocks, each API call or query of the objects in the database should run in its own transaction.

    Business Flow Manager can recover from a database deadlock. However, there might be a major performance impact due to the time between when the deadlock is detected and the rolled-back transactions are retried. Therefore deadlocks should be avoided for performance reasons.

    To check for deadlocks, examine the db2diag.log file and use the DB2 monitors.

    1. Increase the log level for the db2diag.log file to get more information about bottlenecks within the database.

      Increase the value of the DIAGLEVEL parameter from 3 (default) to 4 to include errors, warnings, and informational messages. You can change the value using the following command: 

      db2 update dbm cfg using DIAGLEVEL 4

    2. Create a DB2 event monitor.

      Event monitors provide more information about certain events, for example, deadlocks.

      1. Create an event monitor using the following command: 
        db2 create event monitor  monitor_name for statements, connections, 
  transactions, deadlocks with details write to file  file_name

      2. Start the event monitor using the following command: 
        db2 set event monitor  monitor_name state=1
      3. Collect information using the following command: 
        db2evmon -db  database_name -evm  monitor_name  output_file_name

    3. Use the database snapshot monitors to gather statistics.

      The snapshot monitors use the database monitor switches. For the database instance, the monitor switches have the following default settings:

      • Buffer pool (DFT_MON_BUFPOOL) = ON
      • Lock (DFT_MON_LOCK) = ON
      • Sort (DFT_MON_SORT) = OFF
      • Statement (DFT_MONSTMT) = OFF
      • Table (DFT_MON_TABLE) = OFF
      • Timestamp (DFT_MON_TIMESTAMP) = ON
      • Unit of work (DFT_MON_UOW) = OFF

      To see your current settings for the database instance, use the following command, and search for all of the parameters starting with DFT_MON_: 

      db2 get dbm cfg
      These settings are different from those of the database. For the database, the monitor recording switches have the following default settings:

      • Switch list for db partition number 0
      • Buffer Pool Activity Information (BUFFERPOOL) = ON
      • Lock Information (LOCK) = ON
      • Sorting Information (SORT) = OFF SQL
      • Statement Information (STATEMENT) = OFF
      • Table Activity Information (TABLE) = OFF
      • Take Timestamp Information (TIMESTAMP) = ON
      • Unit of Work Information (UOW) = OFF

      To see your current setting for the database, use the following command: 

      db2 get monitor switches

      • To update the settings for one of the database monitors, for example, the lock monitor, use the following command: 
        db2 update monitor switches using lock on
        This setting is valid only for the current database session.

      • To update the settings for one of monitor switches for the database instance, for example, the lock monitor, use the following command: 
        db2 update dbm cfg using DFT_MON_LOCK OFF
        To activate the setting, restart the database instance.
      • Before you enable a snapshot monitor, reset the counters using the following command: 
        db2 reset monitor all

      • To get a current snapshot after you restart a database instance, use the following command: 
        db2 get snapshot for all on  database_name  output_file_name


Results

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

: Tuning the Business Process Choreographer database


Related tasks:

Remove unnecessary objects from the Business Process Choreographer database


Related information:

Setup, configuration, and maintenance of the Business Process Choreographer database
DB2 database product documentation: DB2 Information Centers