IBM BPM, V8.0.1, All platforms > Install IBM BPM > IBM BPM Advanced for z/OS: Process Server > Configure IBM BPM Advanced for z/OS: Process Server > Create and augmenting profiles > Set up the configuration file system and creating a profile > Generate a design file and database scripts for the database objects

Create a database design file and database scripts for a stand-alone profile or deployment environment by using the database design tool

You can use the database design tool to generate a database design file that can be used when setting up a stand-alone or ND environment. The database design tool optionally generates database scripts that you can use to create your database tables.

Install IBM BPM.

Collate the following information with the help of your database administrator or solution architect:

Ensure that you have the appropriate file permissions to create the database design file and the database scripts in the intended locations.

Tip: Plan for database use when you review information about your planned usage of IBM BPM so that you make the necessary decisions on information needed by the database design tool. Also establish good naming conventions that will allow you to extend your environment and to correlate your databases to the WebSphere environment.

You can run the database design tool in interactive mode to define your database configuration values and save them to a new database design file or to an existing file. You can optionally generate database scripts that can be used to create the database objects for your database type. When you run the tool, the available options change depending on the environment and database type being configured.

You can also run the database design tool in silent mode with command-line parameters to edit or validate an existing database design file, or to generate database scripts for the configuration defined in an existing database design file. The DbDesignGenerator.sh command has the following command-line parameters: 

-? , -help
   display help info.

-e db_design_file_name
   edit the specified database design file (e.g. *.dbDesign, *.properties).

-v db_design_file | db_scripts_output_directory
   when a db_design_file is given, validation will be done on the specified
 database design file based on the database specs. 
When a db_scripts_output_directory is given, the database scripts 
in the specified directory will be validated. Currently only 
scripts generated from template ddl generator can be validated.

-g db_design_file [-d output_directory] [db_design_file2] [-d output_directory2] ... 
[db_design_fileN] [-d output_directoryN]
   generate the database scripts from the specified design files in batch mode. 
The generated scripts will be put in the corresponding output 
directories or the default locations if output directories are absent.

While defining a DB2 for z/OS database configuration, you can specify how to group the SQL database scripts that you want to generate for the IBM BPM components. The default setting is for the scripts to be generated in individual directories for each component. Before you run the database design tool, decide on a location for storing the files that the tool will generate.

For example, you can use the WAS_HOME/profiles/default/dbscripts directory as a base location and add subdirectories as required. It is important to use a directory path and structure that reflects how you want the database objects to be distributed across the DB2 for z/OS subsystem. For each database to be created, use a single output directory, and generate all the schema that is required to create the database, into that directory. As a reference aide, you can also base the name of the SQL directory on the name of the database that you plan to create.

For example, if you intend to create a single database called W1MYDB, you can generate the database scripts for all the database components in the single directory WAS_HOME/profiles/default/dbscripts/DB2-zOS/W1MYDB. If you require two databases, which are scoped at the cell level and the cluster level, you can structure the database schema as shown in the following example:

The database design tool does not support Common Event Infrastructure (CEI).

Restriction: If you want to use a different Business Space database when you run the database design tool, you cannot use BSPACE as the database name. If you need the database name to be BSPACE, you can complete the following the steps:

  1. Run the database design tool as described in the procedure and accept the default database name selected for Business Space.

    Complete the steps to create a database design file, but do not generate the database scripts when prompted.

  2. Edit the database design file that is generated, and update the database name for Business Space in the [begin] = BSpace : WBI_BSPACE section, changing the databaseName value to BSPACE.
  3. Save the database design file.

  4. Run the database design tool again with the option to generate database scripts from a database design, and use the updated database design file to provide the input for the database scripts.

If you use a database design file that designated BSPACE for the Business Space database, when you create a deployment environment or a stand-alone profile using that database design file, you must manually change the Business Space database name back to BSPACE in the Deployment Environment wizard or in pmt.sh.


Procedure

  1. Run the DbDesignGenerator.sh command from the WAS_HOME/util/dbUtils directory.

    For example:

    cd /WebSphere/V8T8DM/DeploymentManager/util/dbUtils
    ./DbDesignGenerator.sh When the database design tool launches successfully, you see information similar to the following example: 

    [info] running DbDesignGenerator in interactive mode...

[info] Enter 'q' to quit without saving; '-' for back to previous menu; '?' for 
    help at any time.
[info] To accept the given default values, simply press the 'Enter' key.

[info] Please pick one of the following [design option(s)] :

(1)Create a database design for Standalone profile or Deployment Environment (2)Create a database design for a single component (3)Edit an existing database design (4)Generate database scripts from a database design (5)exit [q]

Please enter the number for the design option :

  2. To select the option (1)Create a database design for Standalone profile or Deployment Environment, type the number 1 and press Enter.

    You are prompted to choose a database pattern, as shown in the following example: 

    [info] Please pick one of the following [database pattern(s)] :

(1)bpm.advanced.nd.topology
(2)bpm.advanced.standalone
(3)bpm.standard.nd
(4)bpm.standard.standalone
(5)wesb.nd.topology
(6)wesb.standalone

  3. To create a database design pattern for the stand-alone profile or deployment environment that you plan to configure, type the number for the appropriate option and press Enter. For a stand-alone profile, select options that include ".standalone," and for a deployment environment, select options that include ".nd." Options that include ".standard" are not applicable for z/OS.

    For example, to configure the database pattern for a deployment environment for IBM BPM, type the number 1 to select option (1)bpm.advanced.nd.topology, and press Enter. You see the list of database components that you can configure for the selected environment, with a prompt to choose a component to configure; for example: 

    [info] Please edit any database component with status of 'not complete' for required properties.
[info] Completed database components can be edited to change existing or defaulted property values.
[info] Design the 'master' component first, and then any parent components, since other components may inherit values from them.

[info] Please pick one of the following [database component(s)] :

(1)[WBI_CommonDB]       WBI_CommonDB : [master] [status = not complete]
(2)[BPC]        WBI_BPC :  [status = not complete]
(3)[BPM_PerformanceDW]  BPM_PerformanceDW :  [status = not complete]
(4)[BPM_ProcessServer]  BPM_ProcessServer :  [status = not complete]
(5)[BSpace]     WBI_BSPACE :  [status = not complete]
(6)[SibME]      WBI_BPC_ME :  [status = not complete]
(7)[SibME]      WBI_CEI_ME :  [status = not complete]
(8)[SibME]      WBI_SCA_APP_ME :  [status = not complete]
(9)[SibME]      WBI_SCA_SYS_ME :  [status = not complete]
(10)[SibMe]     BPM_PerformanceDW_ME :  [status = not complete]
(11)[SibMe]     BPM_ProcessServer_ME :  [status = not complete]
(12)[save and exit]
  4. Type the number for the appropriate option to configure the master database component, and press Enter. The database component listed as the master component displays [master] beside the name and must be configured first.

    For example, type the number 1 to select option (1)[WBI_CommonDB] WBI_CommonDB : [master] [status = not complete], and press Enter. You see information similar to the following example: 

    [status] WBI_CommonDB is not complete with 1 remaining item(s):
[ 1 ] WBI_CommonDB.WBI_CommonDB :  : DbType key is not set.

Edit this database component? (y/n) [default=y] :

  5. To edit the database configuration for the component, type y and press Enter.

    You are prompted for an output directory for the SQL scripts that the database design tool can generate, and which you can use to create your database objects. If you choose to generate SQL scripts later, as described in step 14, the scripts will be automatically saved to this directory. 

    Please enter the output directory of the SQL files to be generated for WBI_CommonDB [default=] :
  6. Type the fully qualified directory path where you want to collate all the SQL scripts for a specific database; for example, WAS_HOME/profiles/default/dbscripts/DB2-zOS/W8CELLDB. Then press Enter.
    If you have not yet created the directory in your file system, the database design tool will automatically create it when the SQL scripts are generated. If the specified directory already exists, you can choose to either overwrite its contents or specify another directory.

    Tip: The value that you specify will be saved as a reference in the database design file when this file is generated in step 13.

    You are prompted to select the database type that you are configuring; for example: 

    [info] Please pick one of the following [database type(s)] :

(1)DB2-distributed
(2)DB2-zOS
(3)Oracle
(4)SQL Server
  7. Type the number that corresponds to the database type that you want to use for your environment, and press Enter. You obtain a series of prompts for the database properties. These prompts vary based on the database type.

    For example, type the number 2 to select (2)DB2-zOS as the database type. After you select this database type for configuration of the Common database, you see a series of prompts similar to the following example: 

    [info] Please enter the values for the properties in the database objects section.
Database name[default=CMNDB] :
Data source user name[default=] :
Database schema name (SQLID)[default=] :
Bufferpool of 4K size for tables[default=BP1] :
Bufferpool for indexes[default=BP2] :
Bufferpool of 4K size for LOB data[default=BP3] :
Bufferpool of 8K size for tables[default=BP8K1] :
Bufferpool of 16K size for tables[default=BP16K1] :
Bufferpool of 32K size for tables[default=BP32K1] :
Storage group name[default=] :

  8. At each prompt, either press Enter to accept the default value shown, or type your preferred value and press Enter.

    After you complete the last prompt for the database properties, you see information similar to the following example: 

    [info] You have completed database objects section properties needed for database scripts generation.

To skip data source properties, enter 's'; or enter anything else to continue :

  9. To configure the data source properties component, type anything other than s and press Enter. To skip this configuration and accept the defaults, type s and press Enter.

    Tip: To use the database design tool to generate a database design file for use as input for profile creation or topology configuration, configure the data source. To use the database design tool to generate SQL scripts, this step is optional. If you chose to configure the data source for your selected database type, you see the list of database providers for the data source.

    For example, you might see the following database providers for the DB2-zOS database type: 

    [info] Please pick one of the following [database provider(s)] :

(1)DB2 Universal JDBC Driver Provider (XA) on remote z/OS
(2)DB2 Universal JDBC Driver Provider on local z/OS

    1. Type the appropriate number to select a database provider for the data source, and press Enter.

      For example, to select the option for (1)DB2 Universal JDBC Driver Provider (XA) on remote z/OS as the database provider, type the number 1 and press Enter. After you select this database provider, you see a set of prompts similar to the following example: 

      [info] Please enter the values for the properties in the data source properties section.
Database Location on z/OS system[default=] :
Database server host[default=] :
Database server port[default=] :
Data source user name[default=feedee] :
Data source password[default=] :
DB2 Universal JDBC driver path[default=${WAS_INSTALL_ROOT}/jdbcdrivers/DB2] :
Universal JDBC driver path[default=${WAS_INSTALL_ROOT}/jdbcdrivers/DB2] :
DB2 Universal JDBC driver native path(This should be empty since DB2 z on remote Machine accessed through jdbcDriver Type 4)[default=] :

      The password is encrypted in the generated output files.

    2. At each prompt, either press Enter to accept the default value shown, or type your preferred value and press Enter. If you are asked whether you want to create the database tables during configuration, specify false. This option enables you to run the SQL scripts manually at your designated time, and to create and organize the databases according to your DB2 for z/OS best practices and conventions.
    After you complete the last prompt, you see information similar to the following example: 
    [status] WBI_CommonDB is complete with 0 remaining item(s):

------------------------------------------------------------------------------------

[info] Please edit any database component with status of 'not complete' for required properties.
[info] Completed database components can be edited to change existing or defaulted property values.
[info] Design the 'master' component first, and then any parent components, since other components may inherit values from them.

[info] Please pick one of the following [database component(s)] :

(1)[WBI_CommonDB]   WBI_CommonDB : [master] [status = complete]
(2)[BPC]        WBI_BPC :  [status = complete]
(3)[BPM_PerformanceDW]  BPM_PerformanceDW :  [status = not complete]
(4)[BPM_ProcessServer]  BPM_ProcessServer :  [status = not complete]
(5)[BSpace]     WBI_BSPACE :  [status = complete]
(6)[SibME]      WBI_BPC_ME :  [status = complete]
(7)[SibME]      WBI_CEI_ME :  [status = complete]
(8)[SibME]      WBI_SCA_APP_ME :  [status = complete]
(9)[SibME]     WBI_SCA_SYS_ME :  [status = complete]
(10)[SibMe]     BPM_PerformanceDW_ME :  [status = complete]
(11)[SibMe]     BPM_ProcessServer_ME :  [status = complete]
(12)[save and exit]
    After you finish configuring the master database component, the database design tool propagates the values entered, to the remaining components.  If this can be done successfully, these components are also marked as [status = complete] along with the master component.

    If this cannot be done for any reason, they remain listed as [status = not complete].

    Even though a component is marked as complete, all of its values might not be set as required for the DB2 for z/OS configuration. You must therefore review each component in turn, as described in the next step, to ensure that the required values are specified.

  10. Configure each of the remaining database components as follows: specify the number listed for the component and then complete step 5 to step 9. The following guidelines apply:

    • For any database components that are identified as a parent to another component, configure the parent before configuring the child component. The values that you specify for the parent will be propagated to the child component as default settings.

      For example, the following entry indicates that configure the Performance Data Warehouse component ( BPM_PerformanceDW) before you configure the BPM_PerformanceDW_ME messaging engine:
      BPM_PerformanceDW_ME : [parent = BPM_PerformanceDW]

    • When configuring the Process Server component ( BPM_ProcessServer), you are prompted for an administrative user name and password; for example: 
      The user ID you use for administrative security[default=] :
The password for the name specified with the adminUserName parameter[default=] :

      These administrative security prompts refer to the user ID and password that an administrator can use to log on to the Process Admin Console (from a browser) to manage the process applications. The user ID that you specify should be the same as the WebSphere primary administrative user ID.

    • When configuring the Process Server component ( BPM_ProcessServer), you are also asked to specify whether the database is for a Process Center or Process Server: 
      [info] Please pick one of the following [Is this database for a Process Center?(s)] :

(1)false
(2)true

      If the database is for a Process Server, which is the case for IBM BPM for z/OS, you must choose the false option.

      The true option is relevant only when creating a Process Center profile or cluster in an ND environment on IBM BPM for multiplatforms. Setting the option to true also ensures that you can access the Process Center Console.

    • Apart from the schema name, all values specified for the master database component are inherited by the other components. Therefore, you must ensure that you specify the appropriate schema name for each of the remaining database components. Typically, the Process Server, Performance Data Warehouse, and messaging engine components require unique schema names; the remaining components can be assigned the same schema name. You might also be prompted for other values that are unique to the type of component being configured; for example, a prefix for table space names or a value for VSAM catalog names (VCAT).

      If you intend to use a single database for all the system integration bus messaging engines, unique prefixes for table space names are important because they keep the table space name of each messaging engine unique within the database.

    • You might also want to update the values of some of the inherited components; for example, the database name. This is of particular importance if you are planning to use more than one database.

  11. When all database components for your database pattern have been configured and are listed as [status = complete] in the database design tool, type the appropriate number to select [save and exit], and press Enter.

    For example, after you finish configuring the bpm.advanced.nd.topology database pattern, type the number 12 and press Enter. You see information similar to the following example: 

    [status] bpm.advanced.nd.topology is complete with 0 remaining item(s):

Please enter the output directory [default=/WebSphere/V8T8DM/DeploymentManager/util/dbUtils] :
  12. Type the fully qualified path where you want to save the database design file and then press Enter. This location must correspond to the database design file location that was specified in the z/OS Profile Management Tool (zPMT). If you have not yet created the directory in your file system, the database design tool will automatically create it when the file is generated. After you enter the location at the prompt, you see information similar to the following example: 
    Please enter the output filename [default=bpm.advanced.nd.topology.dbDesign] :
  13. Press Enter to accept the default name for the database design file, or type another file name and then press Enter. If a file of the same name already exists in the specified directory, you can choose to either overwrite the file or specify a different file name. The specified name must end with .dbDesign, and must correspond to the database design file name that was specified in zPMT. After you enter the file name at the prompt, you see information similar to the following example: 
    generate database scripts? (y/n) [default=y] :

  14. To generate the database scripts for each component in the directory that you specified earlier (in step 6), type y and press Enter. A series of information messages is displayed to indicate that the scripts for all the components have been generated. For each component, you see information similar to the following example: 
    [info] The script(s) have been generated in WAS_HOME/profiles/default/dbscripts/DB2-zOS/W8CELLDB for WBI_CommonDB

    After the last set of scripts is generated, you see information similar to the following example: 

    [info] thanks, quitting now ...


Results

The following files are created:


What to do next

Ensure that the value of the bpmdbDesign parameter in the augmentation response file matches the fully qualified path of the database design file that was generated by the database design tool. Then run the augmentation job to configure the profile.

When you use the Deployment Environment wizard to create your ND environment, you must import the database design file in order to set default values for the database configuration parameters.

Generate a design file and database scripts for the database objects


Related concepts:
Create databases in the DB2 for z/OS subsystem


Related tasks:
Augmenting stand-alone and ND profiles


Related reference:
Collating customization information for configuring your environment