Extracting, Customizing, and Running Database Setup Scripts

Do not skip this section. To use eeXchange Integrator, you must extract and eventually run the createdb script to set up the eXchange database.

What scripts are supplied, and what do they do?

eXchange Integrator supplies the file oracle510.zip in the Project Explorer tree under SeeBeyond⇒eXchange⇒Download Scripts. The oracle510.zip file contains a collection of command scripts (.cmd files) and SQL scripts (.sql files).

You install the eXchange Integrator schema on the database by doing one of the following:

• Edit setenv.cmd so it reflects your system environment (see Table 2–2), and then run the two other command scripts (see Running Database Scripts to Set Up the eXchange IntegratorDatabase);— or—

• Run the SQL scripts directly, supplying system information at run time; see Running Database Scripts to Set Up the eXchange IntegratorDatabase.

Assumptions

The scripts assume they are run on a machine whose command path includes sqlplus. The network\admin\tnsnames.ora file must include a stanza such as the following:

eXchange_myOracleHostname.domain
  (DESCRIPTION =
    (ADDRESS_LIST =
       (ADDRESS =
         (PROTOCOL = TCP)
        (HOST = myOracleHostname)
        (PORT = 1521))
        ...
     ) )
    (CONNECT_DATA =
       (SID = ORCL)
      ...
  ) )

Some scripts and samples assume defaults or supply values as shown in Table 2–2.

To extract the scripts

1. In Enterprise Explorer, in the project tree, expand the following folders: SeeBeyond⇒eXchange ⇒Download Scripts

2. Right-click oracle510.zip and, on the popup context menu, click Export; then use the Save dialog box to save the file to a local directory, such as C:\JC512\Exported\Oracle510\.

3. Extract the files in oracle510.zip into this local directory, yielding:

   • CleanTrackData.sql

   • Cleanup.sql

   • cleanup_coreServices_tables.sql

   • create_coreServices_tables.sql

   • createdb.cmd

   • createdb.sql

   • createtablespaces.cmd

   • createtablespaces.sql

   • createuser.sql

   • eXchange50Runtime.sql

   • in_user_seq.sql

   • setenv.cmd

To edit the setenv command script

1. Open a command prompt and change directories to the local directory where you saved the scripts in the previous procedure.

2. Use a text editor to edit the as-supplied version of setenv.cmd:

   @REM SET YOUR DATABASE CONNECTION INFORMATION HERE * echo * This file should be edited to use appropriate echo * database connection settings. * echo * SETENV.CMD @REM TNS_NAME @set TNS_NAME= TNS NAME @REM ORACLE_SID @set ORACLE_SID= SID @REM Oracle system login password @set SYSTEMPWD= PWD @set USERID=ex_admin @set USERPWD=ex_admin

3. Supply the appropriate values for TNS_NAME, ORACLE_SID, and SYSTEMPWD. For example:

   @set TNS_NAME=eXchange_myOracleHostname @set ORACLE_SID=ORCL @set SYSTEMPWD=manager @set USERID=ex_admin @set USERPWD=ex_admin

4. If your Oracle location is not c:\oracle\oradata, or if your database instance name (SID) is other then eXchange, then open the createtablespaces.sql file and make the appropriate change or changes in the first line.

   ---

   Note –

   The database user who runs the SQL scripts must have permission to create tables.

   ---

Running Database Scripts to Set Up the eXchange IntegratorDatabase

You install the eXchange Integrator schema on the database by doing one of the following:

• Edit setenv.cmd so it reflects your system environment (see Table 2–2), and then run the two other command scripts (see Running Database Scripts to Set Up the eXchange IntegratorDatabase);— or—

• Run the SQL scripts directly, supplying system information at run time; see Running Database Scripts to Set Up the eXchange IntegratorDatabase.

To run the command scripts that call SQL scripts to install the schema

1. Open a command prompt and change directories to the local directory where you saved the .cmd scripts in the previous procedure.

   It is assumed you have already edited setenv.cmd appropriately, and createtablespaces.sql if necessary.

2. Enter the following command:

   createtablespaces

   The script starts SQL*Plus, invokes an SQL script to create table spaces, and ends.

3. Enter the following command:

   createdb

   The script starts SQL*Plus and invokes an SQL script to create a new user entry:

   • In response to the first prompt, supply an end username, such as: ex510A

   • In response to the prompt, supply a password for this end user, such as: ex510A

     The script creates a new user/password combination, invokes other SQL scripts to update the database instance, and then ends.

4. Repeat step Running Database Scripts to Set Up the eXchange IntegratorDatabase as needed to create other user/password entries for eXchange Integrator users.

   You have installed the eXchange schema onto the eXchange database instance and created user/password combinations. End users can create Oracle OTDs based on this database, and can use it for message tracking and other eXchange Integrator functions.

To directly run the SQL scripts that install the schema

These steps are an alternative to the command scripts described in the previous procedure. Do not use both procedures.

1. Open a command prompt and change directories to the local directory where you saved the .sql scripts in the previous procedure.

   ---

   Note –

   If your Oracle location is not c:\oracle\oradata, or if your database instance name (SID) is other then eXchange Integrator, then open the createtablespaces.sql file and make the appropriate change or changes in the first line.

   ---

2. Enter the following SQL*Plus command:

   path\sqlplus system/SYSTEMPWD@TNSNAME@createtablespaces.sql

   where:

   SYSTEMPWD is the password for the system login ID

   TNSNAME is the name of the Oracle database instance you created for eXchange Integrator.

   Here are two examples of valid commands, depending on the password and name:

   C:\oracle\ora92\bin\sqlplus system/manager1@eX50 @createtablespaces.sql sqlplus system/oraclePW@eXchange @createtablespaces.sql

   When this finishes, you have created new tablespaces.

3. In the command prompt, enter the following SQL*Plus command:

   sqlplus system/SYSTEMPWD@TNSNAME@createuser.sql

   where, as before, SYSTEMPWD is the password for the system login ID and TNSNAME is the name of the Oracle database instance you created for eXchange Integrator.

   Here is an example of a valid command:

   \oracle\ora92\bin\sqlplus system/myPassWd@eX505DB @createuser.sql

4. In response to the system prompt for value #1, enter the username. For example: ex_admin

5. In response to the system prompt for value #2, enter the password. For example: ex_admin

6. Repeat steps Running Database Scripts to Set Up the eXchange IntegratorDatabase and Running Database Scripts to Set Up the eXchange IntegratorDatabase as needed to create user/password entries for eXchange Integrator users.

7. After you run the createtablespaces and createuser SQL scripts, there is one more. In the command prompt, enter the following SQL*Plus command:

   sqlplus ex_admin/ex_admin@TNSNAME @createdb.sql

   where, as before, TNSNAME is the name of the eXchange Oracle database instance, and your eXchange Integrator administrator username and password are both ex_admin.

   After the createdb.sql script ends, you are done — you do not need to run any further SQL scripts. The system populates the tables, and you are ready to use the database instance as your eXchange Integrator database. End users can create Oracle OTDs based on this database, and can use it for message tracking and other eXchange Integrator functions.

To reinitialize the database

1. Open a command prompt, change directories to the location where you extracted the .sql scripts from oracle510.zip

   See Extracting, Customizing, and Running Database Setup Scripts).

2. Enter the following SQL*Plus command:

   sqlplus ex_admin/ex_admin@TNSNAME @createdb.sql

   where, as before, TNSNAME is the name of the eXchange Oracle database instance, and your eXchange Integrator administrator username and password are both assumed to be ex_admin.