Creating Oracle databases

Create Oracle databases before installing IBM Business Automation Studio.

1. Connect to your local or remote database server by running the following command:

   sqlplus oracle_user_ID/oracle_password@oracle_instance_name as sysdba

2. To create the Application Engine playback server database, run the following command on your local or remote database server:

   -- create a new user
   CREATE USER APP_ENGINE_DB_USER_NAME IDENTIFIED BY APP_ENGINE_DB_PASSWORD ;
   
   -- grant privileges to system and objects
   grant create session to APP_ENGINE_DB_USER_NAME;
   grant alter session to APP_ENGINE_DB_USER_NAME;
   grant create table to APP_ENGINE_DB_USER_NAME;
   grant unlimited tablespace to APP_ENGINE_DB_USER_NAME;
   grant select any table to APP_ENGINE_DB_USER_NAME;
   grant update any table to APP_ENGINE_DB_USER_NAME;
   grant insert any table to APP_ENGINE_DB_USER_NAME;
   grant drop any table to APP_ENGINE_DB_USER_NAME;
   

   where

   • APP_ENGINE_DB_USER_NAME is the username that the Application Engine playback server uses to connect to the database.
   • APP_ENGINE_DB_PASSWORD is the password that the Application Engine playback server uses to connect to the database.
3. To create the Business Automation Studio database, run the following command on your local or remote database server:

   -- create a new user
   CREATE USER STUDIO_USER_NAME IDENTIFIED BY STUDIO_USER_PASSW0RD;
   
   -- allow the user to connect to the database
   grant connect to STUDIO_USER_NAME;
   
   -- provide quota on all tablespaces with BPM tables
   grant unlimited tablespace to STUDIO_USER_NAME;
   
   -- grant privileges to create database objects:
   grant resource to STUDIO_USER_NAME;
   grant create view to STUDIO_USER_NAME;
   
   -- grant access rights to resolve lock issues
   grant execute on dbms_lock to STUDIO_USER_NAME;
   
   -- grant access rights to resolve XA related issues:
   grant select on pending_trans$ to STUDIO_USER_NAME;
   grant select on dba_2pc_pending to STUDIO_USER_NAME;
   grant select on dba_pending_transactions to STUDIO_USER_NAME;
   
   -- If using Oracle 10.2.0.3 or lower JDBC driver, un-comment the following statement:
   -- grant execute on dbms_system to STUDIO_USER_NAME;
   
   -- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
   grant execute on dbms_xa to STUDIO_USER_NAME;
   

   where

   • STUDIO_USER_NAME is the username that Business Automation Studio uses to connect to the database.

   • STUDIO_USER_PASSWORD is the password that Business Automation Studio uses to connect to the database.
4. Optional: To use your own JDBC driver, complete the following steps.
   1. Package your JDBC files into a compressed file and use the sc_drivers_url configuration parameter to download them from an accessible web server. Follow the steps in Preparing customized versions of JDBC drivers and ICCSAP libraries.
   2. Add the customized JDBC driver information to the configuration parameters.
      • Set bastudio_configuration.database.use_custom_jdbc_drivers to true and insert your own JDBC driver files into bastudio_configuration.database.jdbc_driver_files in the configuration parameters. See Business Automation Studio configuration parameters.
      • Set application_engine_configuration.use_custom_jdbc_drivers to true in the configuration parameters. See Application Engine configuration parameters.