Creating users for Oracle databases

Edit online

You can create the users for Oracle databases either before or after you run the BPMConfig command with the -create -de parameters to create profiles and configure your deployment environment. Create the cell-scoped user, the deployment environment-level user, the Process database user, the Performance Data Warehouse user, and the three users for the Content database: design object store user, target object store user, and IBM® Content Navigator user. The Process, Performance Data Warehouse, and Content users are not needed for an AdvancedOnly deployment environment.

The BPMConfig command requires input from a properties file that contains configuration settings for the profiles, deployment environment, and database users to be created. In this file, the bpm.de.deferSchemaCreation property determines when the database users can be created:
  • If the property is set to false, database tables are automatically created when you run the BPMConfig command to create the profiles and deployment environment. Therefore, the database users must exist before you run the BPMConfig command.
  • If the property is set to true, database table creation is deferred when you run the BPMConfig command to create the profiles and deployment environment. Therefore, you can create the database users either before or after running the command. You might find it useful to create the database users after running the BPMConfig command because you can use the set of populated scripts, which the command generates, to create the database users and database tables at a time that you choose.

Before you begin

Before you create any users for Oracle databases, see the topic Configuring XA transactions for Oracle in a network environment on AIX or Linux.

You can use a single instance of Oracle for configuring IBM Business Automation Workflow. The Oracle instance must exist and be available for access. Consult the Oracle documentation to create an Oracle instance. If you use a single Oracle instance, make sure that you use different user IDs for the Process database, Performance Data Warehouse database, and Common database. You also need three different users for the Content database schemas:
  • Design object store (DOS)
  • Target object store (TOS)
  • IBM Content Navigator (ICN)
Before you install your system in Oracle database, ensure that the minimum initial settings in the following table are met for Business Automation Workflow database schema users:
Table 1. Minimum initial settings for Business Automation Workflow database schema users
Tuning item Minimum memory for initial settings (MB)
Buffer cache 2048
Shared pool size 1024
Note: You might want to enlarge the memory for the preceding settings depending on the capacity of your system. For more tuning details, consult your database administrator.

Recommendations for database table space settings:

On Oracle, IBM Business Automation Workflow stores large objects (LOBs) with the SECUREFILE option. For SECUREFILE, it is recommended to use a table space with the AUTOALLOCATE option. If you use UNIFORM SIZE extents, ensure that the UNIFORM SIZE is big enough. Given the default block size of 8K, specify a UNIFORM SIZE of at least 120K. Business Automation Workflow does not explicitly prescribe the table space options; it relies on the default Oracle settings (such as AUTOALLOCATE) to automatically manage extents.

For new Business Automation Workflow installations, create table spaces with the AUTOALLOCATE option.

For migrations, if you use table spaces with a UNIFORM SIZE less than 120K, create new table spaces with the AUTOALLOCATE option and make it the default table space for Business Automation Workflow database schema users.

About this task

The default database names are BPMDB for the Process database, PDWDB for the Performance Data Warehouse database, CMNDB for the Common database, and CPEDB for the Content database. In the case of an Advanced deployment environment or AdvancedOnly deployment environment, the Common database has two parts: one is scoped to the cell and the other is scoped to the deployment environment. Both parts can be defined to use CMNDB (which is the default) or they can use separate databases. For details about databases and schemas, see Planning the number of databases.

For more information about creating databases, see the following sections:

Creating users for the databases before creating the profiles and configuring the deployment environment

Edit online

To generate the database scripts that can be used by the BPMConfig command to create the users and configure your databases, you can run BPMConfig with the -create -sqlfiles parameters, and additionally include the -outputDir parameter to specify a location for the generated scripts. When you run the BPMConfig command with these parameters, it generates the database scripts without configuring your environment.

Before you begin

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 databases
    • The user ID and password for authenticating to the database
  • Information about how IBM Business Automation Workflow 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.
Important: If you are using an Oracle database, you must include the database user name and password for all databases, including the optional ones.

Procedure

  1. On the computer where you installed IBM Business Automation Workflow, navigate to the following directory where the sample configuration properties files are stored:

    install_root/BPM/samples/config

  2. Find the sample properties file that most closely represents your target deployment environment and make a copy of this file.
    For each of the different product configurations, there is a different folder containing sample properties files. For example, for configuring an Advanced, AdvancedOnly, or Standard deployment environment, there is an advanced, advancedonly, or standard folder containing a set of sample properties files. Within each folder, there is a set of files that are specific to the different database types and configuration environments. The sample properties files are named according to the following format: de_type[-environment_type]-topology-Oracle, where:
    • de_type can be set to Advanced, AdvancedOnly, or Standard.
    • environment_type can be set to PS for Workflow Server or PC for Workflow Center. This variable is not used if de_type is AdvancedOnly.
    • topology can be set to SingleCluster or ThreeClusters.
    For example, the sample configuration properties file for configuring an Advanced deployment environment with Workflow Center and a single cluster topology using an Oracle database is named Advanced-PC-SingleCluster-Oracle.properties.
  3. Edit the copied properties file and update the values as required to reflect your profile, deployment environment, and database configuration.
    When modifying the sample properties file, use the guidance provided within the file for specifying values.
    Tip: You should use this same properties file later when you run the BPMConfig command to create your profiles and deployment environment.
    Additional considerations:
    • Your modified properties file must use UTF-8 encoding.
    • If you want to automatically create your database tables when you run the BPMConfig command later to create profiles and configure your deployment environment, set the bpm.de.deferSchemaCreation property to false.
    • Do not add any custom properties to this file when you perform your modifications or the BPMConfig command will fail when it is run.
    • If you need to use a backslash character (\) in your properties file, for instance when specifying path names or passwords, you must use an escape backslash before it. For example: bpm.dmgr.installPath=c:\\IBM\\BPM85.
    • If you are configuring a three-cluster setup that is based on the Advanced or AdvancedOnly template, and you want your deployment environment to include the optional Business Process Archive Manager, include the properties file entries that are described in the topic Configuring Business Process Archive Manager.

    For more information about the available properties, see the BPMConfig command-line utility topic and the descriptions in the Configuration properties for the BPMConfig command topic.

  4. Run the BPMConfig command on the computer where IBM Business Automation Workflow is installed, passing it the name of the properties file that you created.
    For example:
    install_root/bin/BPMConfig -create -sqlfiles /directory_path/my_environment.properties -outputDir /my_bpmscripts_dir

    In this syntax, directory_path/my_environment.properties is the location and name of your customized properties file, and my_bpmscripts_dir is the directory where you want to generate the database scripts.

    The generated scripts include a set of files named createUser.sql, which can be used to create the users for the databases. The createUser.sql files are generated into the following default locations:
    • my_bpmscripts_dir/dbscripts/cell_name/Oracle/oracle_instance_name/cell_user
    • my_bpmscripts_dir/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/CMN_user
    • my_bpmscripts_dir/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/PS_user
    • my_bpmscripts_dir/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/PDW_user
    • my_bpmscripts_dir/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/DOS_user
    • my_bpmscripts_dir/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/TOS_user
    • my_bpmscripts_dir/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/ICN_user
    The number of subdirectories that are generated is dependent on the deployment environment type and the number of database users that were defined in the properties file.
    Note: These scripts are overwritten if you run the BPMConfig command again.
  5. For each createUser.sql file that was generated, run the following command on your local or remote database server: 
      sqlplus oracle_user_ID/oracle_password@oracle_instance_name @createUser.sql schema_user_password
    Note: Although you can specify the schema user password shown in the command syntax, you can alternatively edit the createUser.sql script and replace the parameter &1 with the schema user password and then run the script without specifying any parameter.
  6. Manually create the table spaces for the Content database by running the createUserTablespace_ECM.sql script that is in each of the DOS_user and TOS_user folders. Also run the createUser.sql script and the createTablespace_Advanced.sql, createTablespace_Standard.sql, or createTablespace_Express.sql script that is in the ICN_user folder.
    Tip: In some Content database scripts, there is a @DB_DIR@ variable that should be replaced manually.

Creating users for the databases after creating the profiles and configuring the deployment environment

Edit online

When you run the BPMConfig command with the -create -de parameters to create the profiles and configure the network deployment environment, database scripts are generated that are populated with the values from the properties file that you specified. You can use some of these scripts to create the users for the databases if you chose to defer the creation of the database tables.

Before you begin

You must have already run the BPMConfig command to create the profiles and configure the network deployment environment.

Procedure

  1. On the computer where you created the deployment manager profile, navigate to one or more of the following default subdirectories where the SQL database scripts were generated:
    • dmgr_profile_root/dbscripts/cell_name/Oracle/oracle_instance_name/cell_user
    • dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/CMN_user
    • dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/PS_user
    • dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/PDW_user
    • dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/DOS_user
    • dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/TOS_user
    • dmgr_profile_root/dbscripts/cell_name.deployment_environment_name/Oracle/oracle_instance_name/ICN_user

    These directories contain the createUser.sql scripts that you can use to create the users for the databases.

    The number of subdirectories that are generated is dependent on the deployment environment type and the number of database users that were defined in the properties file.

  2. For each createUser.sql file that was generated, run the following command on your local or remote database server: 
      sqlplus oracle_user_ID/oracle_password@oracle_instance_name @createUser.sql schema_user_password
    Note: Although you can specify the schema user password shown in the command syntax, you can alternatively edit the createUser.sql script and replace the parameter &1 with the schema user password and then run the script without specifying any parameter.
  3. Manually create the table spaces for the Content database by running the createUserTablespace_ECM.sql script that is in each of the DOS_user and TOS_user folders. Also run the createUser.sql script and the createTablespace_Advanced.sql, createTablespace_Standard.sql, or createTablespace_Express.sql script that is in the ICN_user folder.
    Tip: In some Content database scripts, there is a @DB_DIR@ variable that should be replaced manually.