Post deployment environment database configuration

This section describes the database configuration required after a successful deployment environment creation and generation. In this section, we create and run scripts for the other WebSphere Process Server database components that were not generated during deployment environment generation. These

components are: 򐂰 BPC 򐂰 BPC event collector 򐂰 Business Space 򐂰 CEI 򐂰 Messaging engines

In this section, we also verify and correct the authentication aliases for BPC messaging, CEI messaging, and SCA app messaging.This is a required step because the DDT configures the user name for the above components as the user name of the default system bus. It is important that we change the user name to match the user name/schemas created in Chapter 2, “Business process management production topologies” on page 9.

Business Process Choreographer (BPC)

The database scripts generated during deployment environment creation are copied to following directory:

/WPS_HOME/profiles/<DMGR

profile>/dbscripts/ProcessChoreographer/Oracle/<ORACLE_SID>/<BPC Schema>

Figure 4-11 shows the BPC database scripts on the deployment manager system.

Figure 4-11 Location of BPC database scripts on deployment manager system

Perform the following steps to run the database scripts:

1. Log in to the database server with an Oracle user ID and create a file system on the database server for BPC dbscripts.

2. Copy all of the database scripts from the deployment manager system to the database server directory created in the previous step.

3. Ensure that the scripts have execute permissions.

4. If tablespaces for BPC tables were created according to the instructions in Chapter 3, “Preparing the environment” on page 17, comment out the create tablespace section in the createSchema.sql file.

5. Log in to sqlplus as sys or system user to run the scripts. To capture the output from the scripts, toggle the spool on by using the spool on; command and specify a spool file as shown in Figure 4-12.

Figure 4-12 Output to the spool file

BPC Event Collector

The database scripts generated during deployment environment creation are copied to the following location:

/WPS_HOME/profiles/<DMGR

profile>/dbscripts/ProcessChoreographer/Oracle/<ORACLE_SID>/<BPC Event collector Schema>

Proceed with BPC Event Collector:

1. Copy the scripts to the database server scripts directory.

2. If tablespaces were created according to the instructions in Chapter 3, “Preparing the environment” on page 17, comment out the create tablespace section in createSchemaObserver.sql.

3. Ensure that the scripts have execute permissions.

4. Log in to sqlplus as sys or system user to run the scripts. To capture the output from the scripts, toggle the spool on by using the spool on; command and specify a spool file. Figure 4-13 provides a sample.

Figure 4-13 BPC Observer tables

5. Verify the spool log for any errors before proceeding with the next component. Note that the sqlp.sh script that you see is just a user-written script to connect to the database.

Business Space

The database scripts generated during deployment environment creation are copied to the following location:

/WPS_HOME/profiles/<DMGR

profile>/dbscripts/ProcessChoreographer/Oracle/<ORACLE_SID>/<Business Space Schema>

Proceed with the Business Space:

1. Copy the scripts from the above location to the database server scripts directory.

2. If tablespaces were created according to the instructions in Chapter 3, “Preparing the environment” on page 17, comment out the CREATE TABLESPACE and CREATE USER statements in the following scripts: – createSchema.sql

– createTablespace.sql

– createTablesBusinessSpace.sql

3. Run the configBusinessSpaceDB.sh file as an Oracle user, ensuring that the Oracle user has execute permissions for all files:

a. Log in as the Oracle user.

b. Ensure that the $ORACLE_HOME variable has been set so that the

ORACLE_HOME/bin directory is in the user’s path. c. Navigate to the database scripts location and run the

configBusinessSpaceDB.sh file (Figure 4-14).

Figure 4-14 Business Space database script

d. Enter the database administrator user name and password.

Common event infrastructure (CEI)

The database scripts generated during deployment environment creation are copied to the following location:

/WPS_HOME/profiles/<DMGR

profile>/databases/event/<topologyName>.Support/dbscripts/oracle

Proceed with the CEI:

1. Copy the scripts from the above location to the database server scripts directory.

2. Log in as an the Oracle user.

3. Ensure that the $ORACLE_HOME variable has been set and that the

ORACLE_HOME/bin directory is in the user path. 4. Go to the database scripts location.

5. If you have already created tablespaces and users according to Chapter 3, “Preparing the environment” on page 17, run the following scripts:

– @cr_security.ora – @cr_tbl.ora – @ins_metadata.ora – @cr_tbl_catalog.ora – @catalogSeed.ora – @cr_stored_procedure.ora

6. These scripts can be run from the command line using the syntax shown in Example 4-14:

sqlplus <SYS_UserID>/password@dbname as SYSDBA @file_name

Example 4-14 Run script commands

export ORACLE_HOME=/opt/app/oracle/orabase/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH

sqlplus SYS/Bpm7admin@bpm7ora as SYSDBA @cr_security.ora sqlplus SYS/Bpm7admin@bpm7ora as SYSDBA @cr_tbl.ora sqlplus SYS/Bpm7admin@bpm7ora as SYSDBA @ins_metadata.ora sqlplus SYS/Bpm7admin@bpm7ora as SYSDBA @cr_tbl_catalog.ora sqlplus SYS/Bpm7admin@bpm7ora as SYSDBA @catalogSeed.ora

sqlplus SYS/Bpm7admin@bpm7ora as SYSDBA @cr_stored_procedure.ora

7. To capture the output from the scripts, toggle the spool on by using the spool on; command and specify a spool file as shown in Figure 5 on page 63. 8. Review the spool log for any errors.

Messaging engines

The messaging engine scripts are not generated during deployment environment creation. These are generated seperately by running the sibDDLGenerator.sh

script for all the messaging engine users.

To generate the scripts and run them on the database: 1. Create a folder called ME in <WPS_HOME>/dbscripts.

2. Run the sibDDLGenerator.sh command for all the four messaging engine users (Example 4-15).

Example 4-15 Messaging engine database scripts

./sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema WPS_SYSMSGUSER -statementend \; -user WPS_SYSMSGUSER > /<WPS_HOME>/dbscripts/ME/WPS_SYSMSGUSER.sql

./sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema WPS_APPMSGUSER -statementend \; -user WPS_APPMSGUSER > /<WPS_HOME>/dbscripts/ME/WPS_APPMSGUSER.sql

./sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema WPS_CEIMSGUSER -statementend \; -user WPS_CEIMSGUSER > /<WPS_HOME>/dbscripts/ME/WPS_CEIMSGUSER.sql

./sibDDLGenerator.sh -system oracle -version 11g -platform unix -schema WPS_BPCMSGUSER -statementend \; -user WPS_BPCMSGUSER > /<WPS_HOME>/dbscripts/ME/WPS_BPCMSGUSER.sql

3. Copy the scripts to the database server location,

/Oracle_home/dbscripts/ME.

4. If all of the users were created according to the steps in Chapter 3, “Preparing the environment” on page 17, then comment out the CREATE USER

statements in all of the scripts:

--CREATE USER WPS_xxxMSGUSER IDENTIFIED EXTERNALLY DEFAULT TABLESPACE USERS QUOTA UNLIMITED ON USERS ACCOUNT LOCK;

5. Run the scripts using sqlplus (Example 4-16).

Example 4-16 sqlplus script

export ORACLE_HOME=/opt/app/oracle/orabase/product/11.2.0/dbhome_1 export PATH=$ORACLE_HOME/bin:$PATH

sqlplus SYS/Bpm7admin@bpm7ora as SYSDBA @WPS_SYSMSGUSER.sql

@WPS_APPMSGUSER.sql @WPS_CEIMSGUSER.sql @WPS_BPCMSGUSER.sql

6. Ensure that there are no errors in the output.

Verifying messaging engine authentication aliases

To do this:

1. Log in to the Integrated Solutions Console.

2. Navigate to Security Global Security Java Authentication and

Authorization Service J2C authentication data.

3. Change the user names and passwords for the three components in Table 4-2 to match the user names in Table 4-2 and as detailed in Table 3-5 on page 29.

Table 4-2 Messaging engine user names