Copyright © TIBCO Software Inc. All Rights Reserved
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 Developer’s Guide.
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.
Ensure your DBMS is running. 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
Type exit to exit and commit.
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.
Still in the command window in BE_HOME/bin, type the following command for Oracle.
sqlplus BE_USER/BE_USER@SID @create_tables_oracle.sql
Type exit to exit and commit.
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
Ensure that you have generated the project EAR file before beginning.
1.
In TIBCO BusinessEvents Studio Explorer, right-click the FraudDetectionStore project name and select Export > TIBCO BusinessEvents > JDBC Deployment. Click Next.
2.
You see the Generate JDBC deployment scripts wizard. Complete the values as follows.
a.
b.
c.
d.
3.
Click Finish. The generated scripts appear in the directory you specified. 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 58, Resources Required for JDBC Backing Store Implementation in TIBCO BusinessEvents Developer’s Guide.
Task H Run the Table Creation Script
1.
Open a command window in BE_HOME/bin and type the following command.
   sqlplus BE_USER/BE_USER @fdstore.sql
osql -S Your-Server –d Your-DB -U BE_USER -P BE_USER -n -i @create_tables_oracle.sql
Summary and Next Step
Congratulations! You are ready to start the FraudDetectionStore application.

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved