Chapter 4 Backing Store Tutorial : Prepare the Database

Prepare the Database
In this task you use provided scripts to set up the database schema. If you want to reset the tutorial, execute Task C and Task D again to recreate the empty tables.
Learning Points
Long Identifiers  A known limitation in Oracle means that each identifier name cannot exceed 30 characters in length. The tutorial project does not have long identifiers. However, if your projects have long identifiers, you will perform an additional procedure to provide shorter aliases and then regenerate and run the scripts, as documented in the section Extra Procedure to Handle Long Identifier Names TIBCO BusinessEvents User’s Guide.
After changing a project that uses a backing store   If you change the project ontology you must update the database schema. A utility is available. However, it cannot handle all changes. See the section Updating an Existing Backing Store Database Schema in TIBCO BusinessEvents User’s Guide.
 
Task A Ensure All Prerequisites are in Place
This tutorial assumes the following prerequisites are in place:
Oracle 10G software. You can use the Express Edition, which you can download from the Oracle web site. (See the product readme file for specific version information.)
The JDBC drivers file (for example, ojdbc14.jar), which you must copy to BE_HOME/lib/ext. The drivers are part of the Oracle Client software.
be-oracle.jar, located in BE_HOME/lib, the default location.
Task B Generate the SQL Scripts
1.
2.
Run be-oradeploy.exe using the following command
be-oradeploy -o FDStore c:\temp\FDCache.ear
In the command window, you see various messages as generated scripts are created. The scripts contain various schema definition commands.
The following generated scripts appear in the BE_HOME/bin directory:
   FDStore.sql
   FDStore_cleanup.sql
   FDStore_remove.sql.
   FDStore.aliases
The aliases file is used to address a known limitation in Oracle: an identifier name cannot exceed 30 characters in length. If there are any long identifier names, they appear in the file. However, the fraud detection project has no long names. For details on how to handle long identifier names, see TIBCO BusinessEvents User’s Guide, Chapter 24, Setting up a Backing Store Database, Task C, Shorten Long Names Using the Aliases File.
Task C Run the Initialize Database Script to Create the Oracle User
Running the provided initialize_database.sql script drops all existing backing store tables. It deletes the user (if one exists) before creating it again.
In the initialize_database.sql script, the Oracle user is set to BE_USER, with password BE_USER. You can edit the script to change these default settings, but the tutorial uses the default username and password.
1.
One way to do the above is to click Start > All Programs > Oracle Database 10g Express Edition > Go To Database Home Page. The database home page appears in a browser window. Type the user name system and the password you assigned when the software was installed.
2.
3.
Type SQLPlus then provide user name system and the password for that user.
4.
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>

 
Task D Login as the BusinessEvents 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 FDStore.sql file.
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 (The username and password in the script you ran in Task C).
2.
3.
Identify yourself as be_user with password be_user.
4.
a.
b.
c.
Your database tables are now configured for use.