WebSphere Process Server database configuration made easy

Using the database design tool

To provide a uniform and comprehensive approach to database configuration and administration, WebSphere® Process Server V7.0 introduced the database design tool that encapsulates many functions to configure databases used by the respective components. This article describes the tool that will help you configure the database.

Phani Madgula (mabalaji@in.ibm.com), Software Developer, IBM

author imagePhani Madgula is currently working for WebSphere Process Server Support at the India Software Labs (ISL). He has 7 years of experience at IBM. He worked in various product teams, including WebSphere Application Server Community Edition, WebSphere Business Integration Adapters, and DB2. He has experience in developing JEE applications, product support, and database administration. He is an Oracle9i certified professional.


developerWorks Contributing author
        level

27 October 2010

Also available in Portuguese

Introduction

One of the challenges that administrators of WebSphere Process Server environment face is the configuration of the topologies and the required databases. Business integration servers like WebSphere Process Server provide many independent, yet related components or services. Some of these components are Common Event Infrastructure, Business Process Choreographer, and Business Space. Each of these components uses a relational data store for persisting runtime data. As the number of these components that use the relational data store increases, along with the supported database platforms and their versions, configuring the databases as well as creating the appropriate database schema objects become laborious and hectic. To provide a uniform and comprehensive approach to database configuration and administration, WebSphere Process Server V7.0 introduced the database design tool that encapsulates the functions to configure these databases used by the respective components. This article describes the tool that will help you configure the database.


Problems in the database configuration (bottom-up approach)

In previous versions, administrators of Process Server had to be aware of the following to configure the databases:

  • Keep track of components (or services) that use a database for persistence.
  • Keep track of the supported database platforms and their versions for each of the components.
  • Locate the packaged scripts that create the database and schema objects for each of the components.
  • Perform modifications to the packaged scripts to fit the environment.
  • Generate scripts during the profile creation or deployment environment generation.
  • Automatically run the scripts during the profile creation or deployment environment generation.
  • Interact with database administrators for running the scripts if the manual approach is required.
  • Update the database schema of each of the components if there is a fix pack update.
  • Update the database schema and migrate the runtime data of each of the components if there is a migration activity.

Therefore, to configure the database for a component, administrators need to thoroughly go through the documentation for the corresponding component to understand the database requirements and collect the required scripts by generating them when configuring the component. Sometimes these scripts have to be modified according to the requirements before handing them over to the database administrator (DBA).

For some components, administrators have an option to run the scripts automatically when the component is being configured. However, in most of the cases, the database servers exist remotely, which require manually running the scripts. In addition to that, more often, the procedures vary across the components. The same situation arises in the case of other Business Process Management (BPM) products like WebSphere Business Monitor and WebSphere Business Modeler, which use the database for persistence of data. Figure1 illustrates the situation.

Figure 1. Bottom-up approach for database configuration of the components
Bottom-up approach for database configuration of the components

In a typical product stack (like the BPM family), there will be many products. Each product contains a number of components. All these components use databases for persisting data and support number of database platforms. This results in numerous ways to design and configure databases for the components. If each product component and database platforms are supported in this ad-hoc manner, it becomes too difficult to manage the databases. The bottom-up approach as described above, which isolates database configuration of one component from another, leads to inconsistencies, errors, and lengthy repetitive tasks for administrators and extra work for developers. This approach also leads to excessive interactions between the DBA and Process Server administrator as illustrated in Figure 2.

Figure 2. Excessive interactions between Process Server Admin and DBA
Excessive interactions between Process Server Admin and DBA

Database configuration using the database design tool (top-down approach)

To alleviate the above mentioned problems, the Process Server development team has come up with an elegant solution by encapsulating the database functions and operations into a tool called the database design tool (DDT). This is a one-stop solution for database configuration for all components. Using the DDT, administrators follow a top-down approach to configure the databases. The DDT is a command line tool that captures the database configuration values from the user and creates a database design file. The database design file is a simple text file that enumerates the database configuration information as key-value pairs for various services. The Profile Management tool (PMT), manageprofiles command line tool, and deployment environment configuration wizards in the admin console allow administrators to supply a database design file for providing the database configuration information. This greatly simplifies the configuration tasks as users need not worry about differences existing in various database platforms while providing the database configuration information. Figure 3 describes the new approach that allows administrators to configure databases.

Figure 3. Common database framework using the DDT
Common database framework using the DDT

For each component and database platform combination, a component database specification file is provided that defines what information needs to be captured. The spec files are available at <ProcessServer_home>/properties/db/DbSpecs_*.xml. The DDT reads these specifications and prompts the user for database configuration information. Each product use case provides a database design template that captures and describes inter-relationships, dependencies, and constraints between database components. Each product provides some pre-defined database design patterns that captures most of the use cases.

The location of these files is in <ProcessServer_home>/properties/db/designs/*.dbDesign. All database design configurations can be consumed by the same database tooling and APIs. From the above diagram in Figure 3, the DDT provides a single, simple, and uniform interface to create a database design file from which actual database scripts are generated.


Database design tool demo scenarios

To follow and run the commands, you need to have Process Server V7.0 installed on your machine. The illustrations below use DB2® distributed database V9.5 as the target database platform. This section describes how to create a complete database design for:

  • A Process Server standalone environment.
  • The Process Server network deployment topology.

An overview of the features provided by the DDT is also discussed.

Create a complete database design for a Process Server standalone environment

In this section, you will create a database design file and provide that as input to the PMT while creating a standalone profile.

  1. Open a command window and navigate to the <ProcessServer_home>/util/dbUtils directory.
  2. Enter the command DBDesignGenerator.bat/sh. This command creates a file in the same directory with a name of dbDesignGenerator.log. This file contains the information about the user interaction with the DDT and it is required for debugging problems with the DDT.
  3. The above command also starts a command-line interactive mode as shown in Figure 4. Enter option 1 to create the database design for a standalone or deployment environment.
    Figure 4. Starting the DDT
    4. Starting the DDT
  4. The DDT shows the following options to select (Figure 5). Enter option 4 to create a database design for the standalone process server profile.
    Figure 5. DDT options to configure database design
    DDT options to configure database design
  5. The DDT lists the database components in a standalone environment as shown in Figure 6. The DDT designates WBI_CommonDB as the master component. This means the database configurations for all the other components inherit the values of WBI_CommonDB if they are not explicitly configured. This option provides a minimum number of steps to perform the configuration in case a single database is used to persist data from all the components.

    This relationship is established using master, parent, and child relationships between the components for which the database configuration is being performed. Also note that we recommend to first configure the master component, then all the parent components, and then the child components. It also shows the status of the each of the database components. Initially, the value is “not complete”. Enter option 1 to configure WBI_CommonDB.

    Figure 6. Database components for standalone environment
    Database components for standalone environment
  6. The DDT asks you whether the WBI_CommonDB component has to be edited. The default value is yes. Press Enter to continue. Then the DDT prompts you to select the target database platform, as shown in Figure 7. Enter option 1 for the “DB2-distributed” database.
    Figure 7. Selecting the database platform
    Selecting the database platform
  7. The DDT prompts you to provide values for the database name, user, schema name, and other database related values. Then it prompts you to continue to configure the datasource properties for WBI_CommonDB, as shown in Figure 8. Just press Enter to continue to configure the datasource.
    Figure 8. Database properties for WBI_CommponDB
    Database properties for WBI_CommponDB
  8. The DDT prompts you to provide the configuration values for the data source. This shown in Figure 9. Once these values are provided, the DDT indicates that the configuration of WBI_CommonDB is complete.
    Figure 9. Datasource configure for WBI_CommonDB
    Datasource configure for WBI_CommonDB
  9. The DDT comes back to the main menu as shown in Figure 6, but with the status as “complete” for all the database components. This is because WBI_CommonDB is the master component and once it is configured, all the other database components inherit the same values. This is shown in Figure 10.
    Figure 10. Database configuration complete for all database components
    Database configuration complete for all database components
  10. However, you can select individual database components and edit the configuration to use a different database and configuration from WBI_CommonDB.
  11. Select the option 10 to save and exit. The DDT prompts you to enter the output directory where the database design file needs to be placed and the file name. Enter the appropriate values as shown in Figure 11.
    Figure 11. Database design file generation
    Database design file generation
  12. The database design file with a name of wps.standalone.dbDesign is created in the provided output directory. Open this file and observe that it contains several sections where each section provides the database configuration values for the various database components. Also, all the database components use the database provided for WBI_CommonDB because it is the master component and the individual database components are not edited. You will use this database design file to create the standalone profile.
  13. After generating the database design file, the DDT prompts you if the database scripts need to be generated. If the database server is running on a remote server and Process Server administrators do not have sufficient permissions, then it is required to generate the scripts from the database design file and hand over the scripts to the DBA to create the databases and schema objects. To illustrate this, press Enter to continue generating the scripts. The DDT prompts for the output directory to generate scripts for each of the database components and then quits. This is shown in Figure 12.
    Figure 12. Database script generation from the database design file
    Database script generation from the database design file
  14. The scripts are generated for the following database components:
    • WBI_CommonDB
    • WBI_BPCEventCollector
    • WBI_BPC
    • WBI_BSPACE
    • WBI_CEI_EVENT
    • WBI_BPC_ME
    • WBI_CEI_ME
    • WBI_SCA_APP_ME
    • WBI_SCA_SYS_ME
  15. Also note that the DDT does not create database scripts for CEI. This is a limitation currently with the CEI component. For network deployment configuration, the CEI database is either configured automatically as part of the deployment environment generation, or created manually after it is generated.
  16. Launch the PMT from <ProcessServer_home>/bin/ProfileManagement/pmt.bat/sh to create a standalone profile. Navigate through all the panels by providing appropriate values for the profile configuration. On the database design wizard, select the check box as shown in Figure 13 and browse to select the database design file created in the previous steps. Finish the configuration and create the profile. For more information about creating profiles using the PMT, see Creating profiles using the PMT.
    Figure 13. Selecting the database design file on the PMT for database configuration
    Selecting the database design file on the PMT for database configuration
  17. After creating the profile, navigate to the <ProcessServer_home>/profiles/<profile_name>/dbscripts directory. You will find that there are four sub directories created, namely: BusinessSpace, CEI, CommonDB, and ProcessChoreographer. Each of the directories contains the database scripts to manually create and configure the databases. Since you have chosen to configure the databases during the profile creation itself, these databases are already configured by the PMT. Morever, these database scripts are also generated using the DDT and the database design file created earlier by using the following command:
    DBDesignGenerator.bat/sh –g wps.standalone.dbDesign –d <output_directory>
  18. Since the CEI database scripts cannot be generated through the DDT, you can use the scripts generated at the <ProcessServer_home>/profiles/<profile_name>/dbscripts/CEI location to configure the CEI database.
  19. When you use the PMT to create a standalone profile, by default, it uses Derby to configure the Business Process Choreographer (BPC) database, in spite of providing DB2 as the database platform in the database design file. This is a limitation in the PMT while creating the standalone profile. To configure some other database platform other than Derby for the BPC component, remove the BPC configuration using the admin console and re-configure it using a non-Derby database.
  20. You can supply the database design file as input to the manageProfiles command line tool while creating the profile as follows:
    manageprofiles.bat -create -templatePath <TemplateLocation> 
     -wbidbDesign wps.standalone.dbDesign

Create a complete database design for Process Server network deployment topology

In this section, you will create a database design file and provide that as an input in the database configuration panel, while creating a deployment environment using the admin console.

  1. Open a command window and navigate to the <ProcessServer_home>/util/dbUtils directory.
  2. Enter the command DBDesignGenerator.bat/sh. This command creates a file in the same directory with a name of dbDesignGenerator.log. This file contains the information about the user interaction with the DDT and it is required for debugging the problems with the DDT.
  3. The above command also starts a command-line interactive mode as shown in Figure 4 earlier. Enter the option 1 to create a database design for a standalone or deployment environment.
  4. The DDT shows the options to select (shown earlier in Figure 5). Enter option 3 to create a database design for a deployment environment.
  5. The DDT lists out database components in a deployment environment as shown earlier in Figure 6. The DDT designates WBI_CommonDB as the master component. This means, the database configurations for all the other components inherit the values of WBI_CommonDB if they are not explicitly configured. This option provides a minimum number of steps to perform the configuration in case a single database is used to persist data from all the components. This relationship is established using master, parent, and child relationships between the components for which the database configuration is being performed. Also note that we recommend to first configure the master component, then all the parent components, and then the child components. It also shows the status of each database component. Initially, the value is “not complete”. Enter option 1 to configure WBI_CommonDB.
  6. The DDT asks you whether the WBI_CommonDB component has to be edited. The default value is “yes”. Press Enter to continue. Then the DDT prompts you to select the target database platform. This is shown earlier in Figure 7. Enter option 1 for the "DB2-distributed" database.
  7. The DDT prompts you to provide values for the database name, user, schema name, and other database related values. Then it prompts you to continue configuring the datasource properties for WBI_CommonDB. This is shown earlier in Figure 8. Press Enter to continue configuring the datasource.
  8. The DDT prompts you to provide the configuration values for the data source. This is shown earlier in Figure 9. Once these values are provided, the DDT indicates that the configuration of WBI_CommonDB is complete.
  9. The DDT comes back to the main menu as shown earlier in Figure 6, but with the status as “complete” for all the database components. This is because WBI_CommonDB is the master component and once it is configured, all the other database components inherit the same values. This is shown earlier in Figure 10.
  10. However, you can select individual database components and edit the configuration to use a different database and configuration from WBI_CommonDB.
  11. Select option 10 to save and exit. The DDT prompts you to enter the output directory where the database design file needs to be placed and the file name to be given to the same. Enter the appropriate values as shown earlier in Figure 11.
  12. The database design file with a name of wps.nd.topology.dbDesign is created in the provided output directory. Open this file and observe that it contains several sections where each section provides the database configuration values for the various database components. Also, all the database components use the database provided for WBI_CommonDB because it is the master component and the individual database components are not edited. You will use this database design file for the creation of the deployment environment.
  13. After generating the database design file, the DDT prompts you if the database scripts need to be generated. If the database server is running on a remote server and Process Server administrators do not have sufficient permissions, then it is required to generate the scripts from the database design file and to hand over the scripts to the DBA to create the databases and schema objects. To illustrate, press Enter to continue generating the scripts. The DDT prompts for the output directory to generate scripts for each of the database components and then quits. This is shown earlier in Figure 12.
  14. The scripts are generated for the following database components:
    • WBI_CommonDB
    • WBI_BPCEventCollector
    • WBI_BPC
    • WBI_BSPACE
    • WBI_CEI_EVENT
    • WBI_BPC_ME
    • WBI_CEI_ME
    • WBI_SCA_APP_ME
    • WBI_SCA_SYS_ME
  15. Also note that the DDT does not create database scripts for CEI. This is a limitation currently with the CEI component. For the network deployment configuration, the CEI database is either configured automatically as part of the deployment environment generation, or created manually after it is generated.
  16. Create a deployment manager profile for the network deployment environment using the PMT. Launch the PMT from the <ProcessServer_home>/bin/ProfileManagement/pmt.bat/sh to create a deployment manager profile. Navigate through all the panels by providing appropriate values for profile configuration. On the database design wizard, select the check box as shown earlier in Figure 13 and browse to select the database design file created in the previous steps. Finish the configuration and create the profile. For more information about creating profiles using the PMT, see Creating profiles using the PMT.
  17. The deployment manager profile is created along with the database and schema objects as mentioned in the wps.nd.topology.dbDesign file. The profile creation process also generates the database scripts at <ProcessServer_home>/profiles/<dmgr_name>/dbscripts/CommonDB/DB2. No other databases or their corresponding scripts are generated at this time. The other databases are created as part of the deployment environment configuration. Only CommonDB is required for starting up the deployment manager.
  18. Start the deployment manager and check the logs for any errors. Create a custom profile and federate it into the deployment manager. A custom profile is required to create a deployment environment.
  19. Open the admin console to create a deployment environment (DE). Navigate to Servers > Deployment Environments and click the New button.
  20. Select the option to create a deployment environment based on a pattern and provide WPSTestEnv as the name for deployment environment. Click the Next button.
  21. Select WPS for the feature and click the Next button.
  22. Select Remote Messaging and Remote Support pattern and click the Next button.
  23. Select the newly created node for the topology and click the Next button.
  24. Keep clicking on the Next button until the Import database configuration wizard appears. On this wizard, provide the wps.nd.topology.dbDesign file as the input for database configuration, as shown in Figure 14. Click the Next button.
    Figure 14. Importing database configuration for DE on the admin console
    Importing database configuration for DE on the admin console
  25. On the Database wizard, the admin console reads the configuration from the database design file and populates the configuration fields for the various database components. The values reflect the same values that are provided while creating the database design file.
  26. Keep pressing the Next button. Provide the appropriate values in the subsequent wizards and finish and generate the deployment environment.
  27. This activity creates scripts for Business Space and BPC components in the <ProcessServer_home>/profiles/<dmgr_name>/dbscripts directory.
  28. The database configuration for the business space is done manually. This is shown as a pending task in the admin console as shown in Figure 15. Navigate to Deployment Environments > WPSTestEnv > Deferred Configuration in the admin console to view the pending tasks. Navigate to the directory where business space database scripts are generated and run configBusinessSpaceDB.bat to create the database objects for the business space component.
    Figure 15. Pending tasks for DE
    Pending tasks for DE
  29. Once the pending task is finished, you can start the deployment environment using the admin console. If the deployment environment starts successfully, it is implied that the database configuration has been correctly performed. Check in the common database to see if the database objects related to all the database components have been created successfully.

Overview of features provided by the DDT

The DDT can run in interactive mode as well as in command line mode. In the interactive mode as illustrated in the previous sections, it allows you to create a database design file and generate database scripts subsequently. In the command line mode, it provides options to edit or validate the provided design file, or generate scripts from the provided design file. Submit the following command to display various options provided the DDT:

<ProcessServer_home>/util/dbUtils/DbDesignGenerator -help

The options provided are shown in Figure 16.

Figure 16. DDT command line options
DDT command line options

The DDT also helps you to generate upgrade scripts for the BPC shared work items. Using shared work items improve the performance of the BPC when there are large numbers of users and corresponding work items. To reach out to WorkItemMigration, open the DDT and enter the options as shown in Figure 17.

Figure 17. WorkItemMigration in the DDT
WorkItemMigration in the DDT

As shown in Figure 17, in addition to creating a database design file and generation of scripts, it also provides options to generate scripts to remove database components. In addition to that, it provides options to generate scripts to migrate a database schema and runtime data of a component to a higher version of Process Server. For more information, see Creating the database design file using the database design tool.


Conclusion

This article introduced problems with the bottom-up approach while configuring databases for several WebSphere Process Server components. It explained how the configuration and administrative tasks pertaining to Process Server databases can become complex and hectic with this approach. The article presented the database design tool that allows administrators to follow a top-down approach for configuring and managing Process Server databases. It also provided information about how the DDT is integrated with the rest of the Process Server configuration tools and commands and illustrated how artifacts generated from the DDT are used while configuring Process Server environments.

Resources

Learn

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into WebSphere on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=WebSphere, Information Management
ArticleID=556278
ArticleTitle= WebSphere Process Server database configuration made easy
publish-date=10272010