Creating database design files for deployment environments with DB2 on AIX

IBM Business Process Manager, V8.0.1, All platforms > Install and configure IBM Business Process Manager > IBM Business Process Manager Advanced > Install and configure IBM Business Process Manager Advanced > On AIX > Network deployment environment > Creating databases and database design files > For DB2 > Creating database design files

'); $("#tabs").tabs({
Creating database design files for deployment environments with DB2

You can use the database design tool to generate a design file that can be used to configure your databases when creating a deployment manager profile, or when using the deployment environment wizard. The database design tool optionally generates database scripts that you can use to create your database tables.

Before you begin

Ensure that you have installed IBM Business Process Manager. The database design tool is available only from the installation binary files.
Before you run the database design tool, prepare the following information:

Information about the database configuration that you are designing. This might be a document that describes the general purpose of the database configuration, supplied by the database administrator or solution architect. Alternatively, it might be a description of required parameters and properties. This information must include:

The location of the database tables.
The location of the JDBC driver on the system where the server profile will be created.
The user ID and password for authenticating to the database.

Information about how IBM Business Process Manager and its components have been installed, the database software used, and the properties required by that type of database.
An understanding of the profiles that you plan to create, specifically, the functional relationship between the profile types and the databases.
Information about the topology pattern to be implemented, and an understanding of how the database design fits into the pattern that you plan to use.

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 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.
Restriction: 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:

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.
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.
Save the database design file.
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 the Profile Management Tool.
Procedure
Run the DbDesignGenerator.sh command from the install_root/util/dbUtils directory. For example:

/opt/IBM/WebSphere/AppServer/util/dbUtils> DbDesignGenerator.sh

If you see the message The system cannot find the specified path. you might have entered the path name incorrectly. Re-enter the path. 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 :
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
To create a database design pattern for the deployment environment that you plan to configure, type the number for the appropriate option and press Enter. Select options that include ".nd."
For example, to configure the database pattern for a deployment environment for IBM Business Process Manager Advanced, 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]
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] :
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 in this procedure, the scripts are automatically saved to this directory.
Please enter the output directory of the SQL files to be generated for WBI_CommonDB [default=] :
Type an output directory for the SQL scripts and press Enter. You can specify a fully qualified directory path, or a path that is relative to the current directory from which the database design tool is being run. If you have not yet created the directory in your file system, the database design tool automatically creates 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.
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
Type 1 to select (1)DB2-distributed, and press Enter. You obtain a series of prompts for the database properties. These prompts vary based on the database type.
For example, 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] :
Database User name[default=] :
Schema name[default=] :
Regular pagesize[default=32k] :
Regular TableSpace[default=WBISPACE] :
Temporary pagesize[default=32k] :
Temporary TableSpace[default=WBITEMPSPACE] :
At each prompt, either press Enter to accept the default value shown, or type your preferred value and press Enter.
The administrative security prompt (if present) refers to the user name and password for the administrative console.
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 :
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.
If you plan to use the database design tool to generate a database design file for use as input for profile creation or topology configuration, you must configure the data source. If you plan to use the database design tool to generate SQL, 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 provider for the DB2-distributed database type:
[info] Please pick one of the following [database provider(s)] :

(1)DB2 Using IBM JCC Driver # XA data source # DB2 Using IBM JCC Driver (XA)
Type the appropriate number to select a database provider for the data source, and press Enter. After you select this database provider, you see a series of prompts similar to the following example:
[info] Please enter the values for the properties in the data source properties section.
Database server host[default=] :
Database server port[default=50000] :
Data source user name[default=] :
Data source password[default=] :
DB2 JCC driver path[default=${WAS_INSTALL_ROOT}/jdbcdrivers/DB2] :
Universal JDBC driver path[default=${WAS_INSTALL_ROOT}/jdbcdrivers/DB2] :
The password is encrypted in the generated output files.
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 if you are using the database design file as input for creating a profile, because this option is also available in the Profile Management Tool and causes a conflict. If you are using the administrative console to create your deployment environment, you can decide based on whether you want the tables to be created automatically.
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 = 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 : [parent = BPM_PerformanceDW] [status = complete]
(11)[SibMe]     BPM_ProcessServer_ME : [parent = BPM_ProcessServer] [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].
Follow the preceding steps to configure the remaining database components that are listed as [status = not complete]. You can also choose to reconfigure any components that are listed as [status = complete] as the result of configuring the master database component.
If you selected bpm.standard.nd, after you configure the master component, all database components are listed as complete.
[info] Please pick one of the following [database component(s)] :

(1)[BPM_ProcessServer]  BPM_ProcessServer : [master] [status = complete]
(2)[BPM_PerformanceDW]  BPM_PerformanceDW :  [status = complete]
(3)[SibMe]      BPM_PerformanceDW_ME :  [status = complete]
(4)[SibMe]      BPM_ProcessServer_ME :  [status = complete]
(5)[save and exit]

Please enter the number for the database component :5

[status] bpm.standard.nd is complete with 0 remaining item(s):
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. 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] :
Press Enter to accept the default directory for the database design file, or type another directory to which you want to save the file and 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 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] :
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. After you enter the file name at the prompt, you see information similar to the following example:
generate database scripts? (y/n) [default=y] :
Optional: If you also want to generate database scripts based on the information provided to the database design tool, 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/util/dbUtils/DB2-distributed-CommonDB 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:

The database design file is created in the specified directory.
The database scripts are created in one or more specified directories.
A log file named dbDesignGenerator.log is created in the directory from which you ran the DbDesignGenerator command.

What to do next

You can choose to use the output from the database design tool in one of the following ways:

If you generated only the database design file, you can specify the database design file and select the option to have it create the database tables as part of those configuration steps.
If you generated both the database design file and SQL scripts, you can specify only the database design file to ensure that the configured run time matches the database tables created from the SQL scripts.

You can specify the database design file in several ways:

When you use the Profile Management Tool to create a profile
When you use the manageprofiles command-line utility to create a profile
When you use the Deployment Environment wizard to create your environment
The database design file can also be used when you configure a server as a Process Server, Process Center, or Performance Data Warehouse Server in the administrative console.

Related reference:
Troubleshooting the database design tool