Creating database design files for specific components with SQL Server in a network deployment environment

You can use the database design tool to generate a design file and database scripts for the database tables required by specific IBM® Business Process Manager components.

-? , -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.

1. Run the DbDesignGenerator.sh command from the install_root/util/dbUtils directory. For example:
   • /opt/IBM/WebSphere/AppServer/util/dbUtils> DbDesignGenerator.sh
   Tip: 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 :

2. 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, as shown in the following example:

   [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

3. 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 IBM Process Server database component, type the number 4 to select option (4)bpm_processserver, 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 BPM_ProcessServer [default=] :

4. 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

5. Type 4 to select (4)SQL Server, 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 server host[default=] :
   Database User name[default=] :
   Database schema[default=] :
   System user name(this is required ONLY for creating the database as a part of standalone profile creation.)[default=] : 
   System password(this is required ONLY for creating the database as a part of standalone profile creation.)[default=] :

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

7. 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: 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 providers for the SQL Server database type:

   [info] Please pick one of the following [database provider(s)] :
   
   (1)Microsoft SQL Server JDCB Driver # XA data source # Microsoft SQL Server JDBC Driver (XA)

   1. 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 port[default=1433] :
      
      [info] Please pick one of the following {Do you want to use windows authentication for the databases ? (s)] :
      
      (1) false
      (2) true
      
      Data source user name[default=] :
      Data source password[default=] :
      MS SqlServer JDBC driver path[default=${WAS_INSTALL_ROOT}/jdbcdrivers/SQLServer] :

      Note: 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 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:

   Please enter the output directory [default=/WebSphere/V8T8DM/DeploymentManager/util/dbUtils] :

8. 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] :

9. 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] :

10. Optional: 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/util/dbUtils/SQLServer-CommonDB for WBI_CommonDB

    [info] thanks, quitting now ...