Creating users for Oracle databases (deprecated)

Edit online

Draft comment:
This topic only applies to BAW, and is located in the BAW repository. Last updated on 2025-03-13 12:15

You can create the users for Oracle databases either before or after you create the profiles and the 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 database users are not needed for an AdvancedOnly deployment environment.

When you use the Deployment Environment wizard to create the deployment environment, you can use the Create Tables option to specify whether database tables are created when the deployment environment is created:

If the Create Tables option is selected, database tables are automatically created at the same time as the deployment environment. Therefore, the database users must exist before you run the Deployment Environment wizard.
If the Create Tables option is not selected, database table creation is deferred when you create the deployment environment. Therefore, you can create the database users either before or after you run the Deployment Environment wizard. You might find it useful to create the database users after running the wizard because you can use the set of populated scripts, which the wizard generates, to create the database users and database tables at a time that you choose.

Before you begin

For simplicity, the createUser.sql script, which is used to create the users for Oracle databases, assigns more than the minimum required privileges to an IBM Business Automation Workflow database user. The minimum required privileges are listed in the Oracle database privileges topic. If you want to specify a more fine-grained list of privileges, change the createUser.sql script according to the privileges listed in that topic.

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 large enough. Given the default block size of 8 K, specify a UNIFORM SIZE of at least 120 K. 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 120 K, 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 profiles or the deployment environment
Creating users for the databases after creating the profiles and the deployment environment

Creating users for the databases before creating profiles or the deployment environment

Edit online

To create the users for the databases before you create the profiles or before you use the Deployment Environment wizard to create your deployment environment, you can use the createUser.sql template that is provided with your IBM Business Automation Workflow installation.

Procedure

Complete the following steps for each database user that you want to create:

Navigate to the BPM_HOME\BPM\dbscripts\Oracle\Create directory and make a copy of the createUsersql file.
In the copied file, replace @DB_USER@ with the user name that you want to use for the database and replace @DB_PASSWD@ with the user password. Save the file.
Create the database user by running the following command on your local or remote database server:
```
sqlplus oracle_user_ID/oracle_password@db_name @createUser.sql
```
Manually create the table spaces for the Content database.

Tip: In some Content database scripts, there is a @DB_DIR@ variable that should be replaced manually. Use the backslash ("\") to replace the forward slash as the file separator.
1. If you plan to use the embedded Content Platform Engine during deployment environment creation, copy the install_root\BPM\dbscripts\Oracle\Create\createUserTablespace_ECM.sql file to two files in your working directory. Name them createUserTablespace_ECM_DOS.sql (for the Design Object Store) and createUserTablespace_ECM_TOS.sql (for the Target Object Store).
  Update and run the two new files to create the table spaces.
  In createUserTablespace_ECM_DOS.sql:
  - Replace @DB_DIR@ with your Oracle instance home directory.
  - Replace @DB_NAME@ with the name that you want to use for the database (such as orcl), @DB_USER@ with the user name, and @DB_PASSWD@ with the password for that user. Later, you must enter these values on the Configure databases page of the Deployment Environment wizard.
  - Replace @ECM_DATA_TS@ with DOSDATTS. Later, you must enter this value on the Configure databases page of the Deployment Environment wizard.
  - Run:
    sqlplus oracle_user_ID/oracle_password@db_name @createUserTablespace_ECM_DOS.sql
  In createUserTablespace_ECM_TOS.sql:
  - Replace @DB_DIR@ with your Oracle instance home directory.
  - Replace @DB_NAME@ with the name that you want to use for the database (such as orcl), @DB_USER@ with the user name, and @DB_PASSWD@ with the password for that user. Later, you must enter these values on the Configure databases page of the Deployment Environment wizard.
  - Replace @ECM_DATA_TS@ with TOSDATTS. Later, you must enter this value on the Configure databases page of the Deployment Environment wizard.
  - Run:
    sqlplus oracle_user_ID/oracle_password@db_name @createUserTablespace_ECM_TOS.sql
2. Whether you plan to use an embedded or an external Content Platform Engine during deployment environment creation, copy the install_root\BPM\dbscripts\Oracle\Create\createUser.sql file to your working directory. Name it createUser_ICN.sql
  To create one user for IBM Content Navigator, run:
```
sqlplus oracle_user_ID/oracle_password@db_name @createUser_ICN.sql
```
3. Whether you plan to use an embedded or an external Content Platform Engine during deployment environment creation, copy the install_root\BPM\dbscripts\Oracle\Create\createTablespace_ICN.sql file to your working directory.
  Update and run the file to create the IBM Content Navigator table spaces. Remember the values because you will use them later on the Configure databases page of the Deployment Environment wizard.
  - Replace @DB_USER@ with the user name that you want to use and replace @DB_NAME@ with the name that you want to use for the database (such as orcl).
  - Replace the table space name @ECMClient_TBLSPACE@ with ICNTS.
  - Run:
```
sqlplus oracle_user_ID/oracle_password@db_name @createTablespace_ICN.sql
```

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

Edit online

After you create the profiles, you can use the Deployment Environment wizard to create the deployment environment and generate the database scripts. The scripts are populated with the configuration values that you specified in the wizard. 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 used the Profile Management Tool, the BPMConfig command, or the manageprofiles utility to create and augment the profiles. You must also have used the Deployment Environment wizard to configure the deployment environment.

Procedure

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 depends on the deployment environment type and the number of database users that were configured in the Deployment Environment wizard.
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.
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. Use the backslash ("\") to replace the forward slash as the file separator.