Chapter 24 Setting up a Backing Store Database : Backing Store Database Configuration Tasks

Backing Store Database Configuration Tasks
As with any procedure that modifies your data, ensure that you have made backups before you begin.
See Updating an Existing Backing Store Database Schema for the impact of different kinds of changes and how you can update the backing store schema.
 
Task A Prepare Files
1.
Open your project in TIBCO Designer, and build the EAR file. Model information in the EAR will be used to build tables in the database.
2.
Ensure that be-oracle.jar is located in BE_HOME/lib (or other location in your class path).
3.
Copy your JDBC drivers file to BE_HOME/lib/ext (or other location in your class path). These files are part of the Oracle Client software.
Task B Generate the SQL Scripts
Open a command window and navigate to BE_HOME\bin. Run be-oradeploy.exe using a command with the following format:
be-oradeploy [-p property file] [-o Oracle schema output file] [EAR Path] [-h]
For example:
be-oradeploy -o acme c:/BEProjects/MyEar.ear
If you are not running from the default folder (BE_HOME/bin) or if you are not using the default files, you must provide both the --propFile and the -p parameters, and pass them the fully qualified name of the tra file. For example:
The options are explained in the following table:
-p, /p, -property, or /property
Specifies the property file. If not specified, the default property file is used, that is, be-oradeploy.tra in the current directory. See note above.
-h, /h, or /help
In the command window, you see various messages as schema definition commands are created in the generated scripts and the scripts are created.
The generated scripts appear in the directory where you ran the executable. For example, if you provided the schema output filename acme, you would see files called acme.sql, acme.aliases, acme_cleanup.sql, and acme_remove.sql.
The user-defined part of the database schema is created in an Oracle schema file as schema definition commands. In Task E you run this script (together with provided scripts) to build the schema in the database.
Keep the yourname.sql file for future reference. If you modify your ontology, you must also modify your database schema. You can compare the original with the newly generated yourname.sql and run only those commands that are different in the new file.
Task C Shorten Long Names Using the Aliases File
If the aliases file has entries, do the following to provide short aliases for all names longer than 30 characters (see Extra Procedure to Handle Long Identifier Names for more details):
1.
Open the yourname.aliases file for editing.
2.
For each entity, property, and state machine name that exceeds 30 characters in length, provide an alias using a name that is shorter than 30 characters. Ensure the name is unique.
For example, you would modify the following entry:
TABLE.D_NewConceptNewConceptNewConceptNewConcept.alias= D_NewConceptNewConceptNewConceptNewConcept
With a short name such as:
TABLE.D_NewConceptNewConceptNewConceptNewConcept.alias= D_NewConceptNewConceptNewCon
3.
Perform Task B, Generate the SQL Scripts, again. This time, the aliases you created are used.
Task D Run the Initialize Database Script to Create the Oracle User
Running the initialize_database.sql script drops all existing backing store tables. If you have data you need to retain, contact TIBCO support for assistance.
In the script initialize_database.sql script, the Oracle user is set to BE_USER, with password BE_USER. You can edit the script as needed to change these default settings. The documentation uses the default username and password.
1.
2.
Navigate to the location of the scripts (or copy them all to the BE_HOME/bin directory) and open an SQLPlus prompt. (Open a command window, type SQLPlus then provide the system user credentials.)
3.
Type @initialize_database.sql to run the provided script, initialize_database.sql. You see messages like the following:

 
DROP USER be_user CASCADE
*
ERROR at line 1:
ORA-01918: user 'BE_USER' does not exist
 
User created.
Grant succeeded.
SQL>

 
Note that the script assumes that it has been run before. It deletes the user before creating it again. This means you can run the script again without having to take extra steps. This is useful for testing purposes.
Task E Login as the Oracle User and Run SQL Scripts
In this step, you run scripts to create the database schema under the user you created. The schema combines the definitions in base-types.sql, create-tables.sql, and the generated Oracle schema output file file (acme.sql as an example).
Note that these scripts also perform cleanup before creating the schema. The first time you run the scripts, you see harmless error or warning messages because there is nothing to delete.
1.
Login to the Oracle server as BE_USER, password BE_USER (or whatever username and password you set in the script in Task D).
2.
3.
a.
b.
c.
@yourname.sql (for example, @acme.sql )
Your database tables are now configured for use. Additional configuration activities are explained in Chapter 25, Project Configuration for Backing Store.