Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 4 Backing Store Tutorial : Prepare the Database Schema

Prepare the Database Schema
In this task you use provided scripts to set up the database schema.
See Reset the Backing Store Tutorial for various levels of reset you can do if you want to reuse this tutorial example.
Learning Points
What happens if I change the project ontology after setting up the database schema?   If you change the project ontology you must update the database schema. You must also update the schema if you add an entity that you previously excluded from the backing store. A utility is provided that can handle most changes. Certain changes require manual updates. See the section Updating an Existing Backing Store Schema, in TIBCO BusinessEvents Administration.
What are the minimum permissions for the database user?  At a minimum, the user must be able to create tables and views. By default the BE_USER user created by the scripts has DBA privileges.
Task E Run the Initialize Database Script as the DBA or System User
This script creates the TIBCO BusinessEvents user and initializes the database.
The tutorial uses the default username (BE_USER) and password (BE_USER). You can change the username by editing the BE_HOME/bin/ initialize_databaseYourDBMS.sql script.
Running the initialize_databaseYourDBMS.sql script deletes the user before creating it again. Running the create_tables_YourDBMS.sql drops all database tables before creating them again. This means you can run these scripts again during test phases of your project development, without having to take extra cleanup steps.
1.
Open a command window in BE_HOME/bin (default location of the scripts) and type the following command for Oracle (if you use the default SID you can omit @SID):
sqlplus sys_user/sys_user_password@SID @ initialize_database_oracle.sql
If you are using SQL Server, use this instead:
osql -S Your-Server -U sys_user -P sys_user_password -n -i initialize_database_sqlserver.sql
This script creates the TIBCO BusinessEvents database user. This user must be used to run the other scripts. You see messages like the following:
   User dropped.
   User created.
   Grant succeeded.
Task F Run the Create Tables Scripts as the TIBCO BusinessEvents User
Next you log on as the TIBCO BusinessEvents user, BE_USER by default, and run a script to create non-project specific tables.
1.
Open a command window in BE_HOME/bin and type the following command for Oracle.
   sqlplus BE_USER/BE_USER@SID @ create_tables_oracle.sql
If you are using SQL Server, use this instead:
osql -S Your-Server –d Your-DB -U BE_USER -P BE_USER -n -i @create_tables_sqlserver.sql
You see various harmless error messages the first time you run the script, and various messages saying Table created and Index created.
Task G Generate the Project-Specific SQL Scripts
This step requires the EAR file that contains the fraud detection project ontology. Use the file you built at the end of the section Configure the TIBCO BusinessEvents Studio Project
1.
2.
Run be-jdbcdeploy.exe using the following command:
   be-jdbcdeploy -o fdstore yourPath/fdstore.ear
where -o is the schema output file name, and the last element is the EAR file path and name.
If you are using the default example location, you would enter (all on one line):
be-jdbcdeploy -o fdstore C:\tibco\be\5.0\examples\standard\FraudDetectionStore\fdstore.ear
For certain situations, you must also specify the CDD location. However the tutorial example does not require this.
The generated scripts appear in the directory where you run the executable. If you are following the tutorial instructions, the following files appear in the BE_HOME/bin directory:
   fdstore.sql
   fdstore_cleanup.sql
   fdstore_delete.sql
   fdstore_alter.sql
   fdstore_remove.sql.
   fdstore.aliases
The use of these scripts is explained in Table 49, Resources Required for JDBC Backing Store Implementation in TIBCO BusinessEvents Administration.
You see messages like the following:

 
Using bootstrap property file: C:\tibco\be\5.0\bin\be-jdbcdeploy.tra
Using jdbcdeploy property file: C:\tibco\be\5.0\bin\be-jdbcdeploy.tra
Using jdbcdeploy schema file: C:/tibco/be/5.0/bin/base_types.xml
Using jdbcdeploy keyword file: C:/tibco/be/5.0/bin/dbkeywordmap.xml
Deploying to 'Oracle' using ansi type is 'enabled'
Writing schema file :C:\tibco\be\5.0\bin\fdstore.sql
Writing cleanup file :C:\tibco\be\5.0\bin\fdstore_cleanup.sql
Writing remove file :C:\tibco\be\5.0\bin\fdstore_remove.sql
Writing alter file :C:\tibco\be\5.0\bin\fdstore_alter.sql
No valid Database URL/Connection information provided. No data-migration script will be generated.
Writing delete file :C:\tibco\be\5.0\bin\fdstore_delete.sql
Writing aliases file :C:\tibco\be\5.0\bin\fdstore.aliases

 
Note that the line beginning No valid database URL/Connection information provided" refers to a routine for migrating from an older backing store implementation to the current implementation. It does not indicate a problem in your setup.
Summary and Next Step
Congratulations! You are ready to deploy the FraudDetectionStore project.
 

Copyright © TIBCO Software Inc. All Rights Reserved