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@;
   -- Note:
   -- 1. @DB_DIR@ is a folder in the PV.
   -- 2. You must specify the DATAFILE or TEMPFILE clause unless you have enabled Oracle Managed Files by setting a value for the DB_CREATE_FILE_DEST initialization parameter. 
   CREATE TABLESPACE @APP_ENGINE_DB_TBLSPACE@
      DATAFILE '@DB_DIR@/@APP_ENGINE_DB_USER_NAME@/@APP_ENGINE_DB_TBLSPACE@.dbf' SIZE 200M REUSE
      AUTOEXTEND ON NEXT 20M
      EXTENT MANAGEMENT LOCAL
      SEGMENT SPACE MANAGEMENT AUTO
      ONLINE
      PERMANENT
    ;
   CREATE TEMPORARY TABLESPACE @APP_ENGINE_DB_TBLSPACE@_TEMP
      TEMPFILE '@DB_DIR@/@APP_ENGINE_DB_USER_NAME@/@APP_ENGINE_DB_TBLSPACE@_TEMP.dbf' SIZE 200M REUSE
      AUTOEXTEND ON NEXT 20M
      EXTENT MANAGEMENT LOCAL
   ;
   
   ALTER USER @APP_ENGINE_DB_USER_NAME@ QUOTA UNLIMITED ON @APP_ENGINE_DB_TBLSPACE@;
    
   ALTER USER @APP_ENGINE_DB_USER_NAME@ DEFAULT TABLESPACE @APP_ENGINE_DB_TBLSPACE@ TEMPORARY TABLESPACE @APP_ENGINE_DB_TBLSPACE@_TEMP;
   
   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 commands on your local or remote database server:

   -- create a new user
   CREATE USER @DB_USER@ IDENTIFIED BY @DB_PASSWD@;
   
   -- allow the user to connect to the database
   grant connect to @DB_USER@;
   
   -- Note:
   -- 1. @DB_DIR@ is a folder in the PV.
   -- 2. You must specify the DATAFILE or TEMPFILE clause unless you have enabled Oracle Managed Files by setting a value for the DB_CREATE_FILE_DEST initialization parameter. 
   CREATE TABLESPACE @BASDB_TBLSPACE@
        DATAFILE '@DB_DIR@/@DB_NAME@/@BASDB_TBLSPACE@.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 20M
        EXTENT MANAGEMENT LOCAL
        SEGMENT SPACE MANAGEMENT AUTO
        ONLINE
        PERMANENT
    ;
   CREATE TEMPORARY TABLESPACE @BASDB_TBLSPACE@_TEMP
        TEMPFILE '@DB_DIR@/@DB_NAME@/@BASDB_TBLSPACE@_TEMP.dbf' SIZE 200M REUSE
        AUTOEXTEND ON NEXT 20M
        EXTENT MANAGEMENT LOCAL
   ;
   
   ALTER USER @DB_USER@ QUOTA UNLIMITED ON @BASDB_TBLSPACE@;
    
   ALTER USER @DB_USER@
        DEFAULT TABLESPACE @BASDB_TBLSPACE@
        TEMPORARY TABLESPACE @BASDB_TBLSPACE@_TEMP;
   
   -- grant privileges to create database objects:
   grant  CREATE TABLE to @DB_USER@;
   grant  CREATE PROCEDURE to @DB_USER@;
   grant  CREATE SEQUENCE to @DB_USER@;
   grant  CREATE VIEW to @DB_USER@;
   
   -- grant access rights to resolve lock issues
   grant execute on dbms_lock to @DB_USER@;
   
   -- grant access rights to resolve XA related issues:
   grant select on pending_trans$ to @DB_USER@;
   grant select on dba_2pc_pending to @DB_USER@;
   grant select on dba_pending_transactions to @DB_USER@;
   -- If using Oracle 10.2.0.3 or lower JDBC driver, uncomment the following statement:
   -- grant execute on dbms_system to @DB_USER@; 
   -- If not using Oracle 10.2.0.4 or higher JDBC driver, comment the following statement:
   grant execute on dbms_xa to @DB_USER@;

   where

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

   • @DB_PASSWD@ 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 Optional: 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.