Specifying initial database settings

Use this task to specify initial DB2® database settings. Note that this information is provided only as an example.

Why and when to perform this task

Attention: The following information relates to the Business Process Choreographer database. For information about tuning a WebSphere® default messaging database, see Tuning and problem solving for messaging engine data stores in the WebSphere Application Server Network Deployment information center.

To achieve good database operation, specify the initial database settings. You will fine-tune the settings later, in Fine-tuning the database.

Steps for this task

  1. Separate the log files from the data files.

    Putting the database log file on a disk drive that is separate from the data tends to improve performance, provided that sufficient disk drives are available. If few disk drives are available, distributing the table spaces, as described in the previous section, is usually more beneficial than putting the database log on a separate drive.

    For example, if you use DB2 on a Windows® system, you can change the location of the log files for the database named BPEDB to the F:\db2logs directory, by entering the following command:
    db2 UPDATE DB CFG FOR BPEDB USING NEWLOGPATH F:\db2logs
  2. Create table spaces.
    After you create the database, explicitly create table spaces. Example scripts to create table spaces are provided by Business Process Choreographer in the ProcessChoreographer subdirectory of your WebSphere Application Server installation. Customize these scripts to accommodate the needs of a particular scenario. Your goal, when creating the table spaces, is to distribute input and output operations over as many disk drives as possible that are available to DB2. By default, these scripts create the following table spaces:
    AUDITLOG
    Contains the audit trail tables for processes and tasks. Depending on the degree of auditing that is used, access to tables in this table space can be significant. If auditing is turned off, tables in this table space are not accessed.
    COMP
    Contains the compensation tables for business processes from Business Process Choreographer Version 5. Depending on the percentage of compensable processes and activities, the tables in this table space might require high disk bandwidth. If compensation is not used within business processes, the tables in this table space are not used.
    INSTANCE
    Holds the process instance and task tables. It is always used intensively, regardless of the kind of long-running process that is run. Where possible, spread this table space over several disk drives.
    SCHEDTS
    Contains the tables that are used by the WebSphere scheduling component. Access to tables in the scheduler table space is usually low, because of the caching mechanisms used in the scheduler.
    STAFFQRY
    Contains the tables that are used to temporarily store staff query results that are obtained from staff registries like Lightweight Directory Access Protocol (LDAP). When business processes contain many person activities, tables in this table space are frequently accessed.
    TEMPLATE
    Contains the tables that store the template information for processes and tasks. The tables are populated during the deployment of an application. At run time the access rate is low. The data is not updated, and only new data is inserted during deployment.
    WORKITEM
    Holds the tables that are required for work item processing. Work items are used for human task interaction. Depending on the number of human tasks in the business processes, access to the tables in this table space can vary from a low access rate to significantly high access rate. The access rate is not zero, even when no explicit human tasks are used, because work items are also generated to support administration of long-running processes.

    To create a database for high performance, perform the following actions:

    1. Create the database.
      On Windows, you can specify a destination drive. The command creates a database on the destination drive in a directory named the same as the default DB2 instance on the server. So, for example, if the database is to be created on drive D:, and the local default instance is DB2, the database data goes to D:\DB2. Thus, to create a DB2 database in the D: directory for Business Process Choreographer, enter the following command:
      CREATE DATABASE BPEDB ON D: USING CODESET UTF-8 TERRITORY en-us;
      On UNIX® and Linux®, enter the following command:
      CREATE DATABASE BPEDB ON /wasdbfs USING CODESET UTF-8 TERRITORY en-us;
      where /wasdbfs specifies a directory.
    2. Create the table spaces on the disks if the database supports table spaces.
      For example, the following script is based on the createTablespaceDb2.ddl file that is located in the ProcessChoreographer subdirectory of your WebSphere Application Server installation. In the following example, it creates the database, table spaces, and tables that use three disk drives on a Windows system:
      - Scriptfile to create tablespaces for DB2 8.1 and 8.2
      - to run the script call
      -      db2 connect to BPEDB
      -      db2 -tf createTablespaceDb2.ddl
      CREATE TABLESPACE AUDITLOG
        MANAGED BY SYSTEM
        USING( 'F:/BPEDB_TS/AUDITLOG' );
      
      CREATE TABLESPACE COMP
        MANAGED BY SYSTEM
        USING( 'D:/BPEDB_TS/COMP' );
      
      CREATE TABLESPACE INSTANCE
        MANAGED BY SYSTEM
        USING( 'E:/BPEDB_TS/INSTANCE' );
      
      CREATE TABLESPACE STAFFQRY
        MANAGED BY SYSTEM
        USING( 'D:/BPEDB_TS/STAFFQRY' );
      
      CREATE TABLESPACE TEMPLATE
        MANAGED BY SYSTEM
        USING( 'D:/BPEDB_TS/TEMPLATE' );
      
      CREATE TABLESPACE WORKITEM
        MANAGED BY SYSTEM
        USING( 'D:/BPEDB_TS/WORKITEM' );
      
      -- start import scheduler DDL: createTablespaceDB2.ddl
      CREATE TABLESPACE SCHEDTS MANAGED BY SYSTEM USING( 'D:/BPEDB_TS/SCHEDTS' );
      -- end import scheduler DDL: createTablespaceDB2.ddl
    3. Create the Business Process Choreographer tables.

      Use the script for your database that is provided in the ProcessChoreographer directory. For example, for DB2, use the createSchemaDb2.ddl file.

  3. Tune the database.

    Use a capacity planning tool for your initial database settings.

    If you are using DB2, start the DB2 configuration advisor from the DB2 Control Center, by selecting DB2 configuration advisor from the pop-up menu of the Business Process Choreographer database. Do the following actions:

    1. Allocate memory to DB2.

      For Server, allocate to DB2 only as much memory as is physically available to it without swapping.

    2. Specify the type of workload.

      For Workload, select Mixed (queries and transactions).

    3. For Transactions, specify the length of the transactions and the estimated number of transactions to be processed each minute.

      Select More than 10, to indicate that long transactions are used.

      Then, in the Transactions per minute field, select the estimated number of transactions processed each minute. To determine this number, assume that each activity in the process has one transaction. The number of transactions performed in one minute is then as follows:

      number of transactions performed each minute = number of processes completed each minute * number of activities in each process

    4. Tune the database for faster transaction performance and slower recovery.

      For Priority, select Faster transaction performance.

    5. If possible, tune the database populated with the typical amount of data in production. For Populated, select Yes. Otherwise, select No.
    6. Tune the parallel connections setting.
      For Connections, specify the maximum number of parallel connections that can be made to the application server. Guidelines for determining this value are as follows:
      • The number of database connections required is determined by the number of Java™ DataBase Connectivity (JDBC) connections to the WebSphere Application Server. The JDBC connections are provided by the JDBC connection pool, which is in the WebSphere Application Server. For p JDBC connections, p * 1.1 database connections are required. How to estimate a realistic value for p is described in Tuning the application server.
      • If Business Process Choreographer and the database are installed on the same physical server, Business Process Choreographer needs no remote database connections. However, because remote connections might be required for remote database management, specify a low value, rather than zero.
      • If Business Process Choreographer and DB2 are installed on separate servers, set the number of remote applications in accordance with the rule previously described for local connections.
    7. Lock the rows you want to read.

      For Isolation, select Read stability. This isolation level is required for Business Process Choreographer.

    The configuration advisor displays suggested changes. You can either apply the changes now, or save them to a file to apply later.

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:54:57 2006

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