Create a database design file and database scripts for a specific component by using the database design tool

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 specific component by using the database design tool

You can use the database design tool to generate a design file, and optionally database scripts, for the database tables required by specific IBM BPM components.

Install IBM BPM.
Collate the following information with the help of your database administrator or solution architect:

Details about the database software used and the configuration information required for setting up one or more IBM BPM databases in the database management system; for example:

The database product (DB2 , DB2 for z/OS , Oracle, SQL Server)
The database server details
The data source details
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 the type of environment in which the databases will be used (stand-alone or ND environment), and an understanding of how the database design fits into this environment

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 for a specific component, and to save these values 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 component 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.
The database design tool does not support Common Event Infrastructure (CEI).
If you intend to generate database scripts for the service integration bus messaging engines, you must repeat step 1 to step 10 of the following procedure, to define the database configuration and generate the scripts for each messaging engine that you require.
For example, if you require scripts for six messaging engines, perform this procedure six times. For more information about the considerations for creating messaging engine data stores in a DB2 for z/OS subsystem, see Create the messaging engine data stores.

Procedure
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 :
To select the option (2)Create a database design for a single component, type the number 2 and press Enter.
You are prompted to choose a component:
[info] Please pick one of the following [component(s)] :

(1)bpc
(2)bpcreporting
(3)bpm_performancedw
(4)bpm_processserver
(5)bspace
(6)cei
(7)sca
(8)sibme
(9)wbi_commondb
To create a database design for the component that you plan to configure, type the number for the appropriate option and press Enter.

For example, to configure the Common database component, type the number 9 to select option wbi_commondb, 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 10, 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=] :
Type a fully qualified directory path for the SQL scripts for this component; for example, WAS_HOME/profiles/default/dbscripts/DB2-zOS/CommonDB. 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 9.
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 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. The 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=] :
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 WebSphere 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.
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, 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
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 series 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.
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, you see information similar to the following example:
Please enter the output directory [default=/WebSphere/V8T8DM/DeploymentManager/util/dbUtils] :
Type the fully qualified path where you want to save the database design 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, you see information similar to the following example:
Please enter the output filename [default=WBI_CommonDB_DB2-zOS.properties] :
Press Enter to accept the default file name, or type another file name and then press Enter. After you enter the file name, you see information similar to the following example:
generate database scripts? (y/n) [default=y] :
If you also want to generate database scripts based on the information provided to the database design tool, type y and press Enter.
You see information similar to the following example:
[info] The script(s) have been generated in WAS_HOME/profiles/default/dbscripts/DB2-zOS/CommonDB for WBI_CommonDB
[info] thanks, quitting now ...
Results

A database design file is created and, optionally, database scripts are created at the locations that you specified.

What to do next

You can use the generated SQL scripts to create the database tables for the component.

Generate a design file and database scripts for the database objects

Related tasks:
Create the databases