JDBC Backing Store Configuration : JDBC Backing Store Database Configuration Tasks

JDBC Backing Store Database Configuration Tasks
Simple Case
Complex Case
 
Task A As Needed, Set Entity Metadata Properties
You may need to modify your TIBCO BusinessEvents Studio project before you generate the EAR file. Check the project and, as needed, do the following:
Set Metadata Properties for Long Identifiers, as Desired
You can let the be-jdbcdeploy utility create aliases for long identifiers, or you can set short database identifier names before generating the EAR, using metadata properties. For more details about this choice, see Names that Exceed the DBMS Maximum Column Length.
To set short database identifiers for affected entities and properties do the following.
1.
2.
For an entity name that is too long, in the Metadata section of that entity’s editor, set the Table Name field to the desired value.
It is recommended that you specify table names that start with "D_" to match the standard naming convention.
3.
Set the Maximum Column Length for Long String Values, as Needed
For properties whose value could be longer than 255 characters, do the following. See String Properties That Exceed the Maximum Database Column Length for more details.
1.
2.
3.
4.
Task B Prepare the EAR, be-jdbcdeploy.tra, and Database Driver Files
As desired, you can configure the JDBC Connection resource for your backing store before creating the database, if you know the values to use. After you have created the database, it's a good idea to open your project again and test the connection. If you need to make corrections to the connection, do so and rebuild the EAR before deploying the project. The procedures in this guide assume you will configure the resource after setting up the database.
1.
Ensure that you have a valid EAR for your project, with up-to-date ontology definitions. Model information in the EAR will be used to build tables in the database.
2.
Ensure that cep-backingstore.jar is located in BE_HOME/lib (or other location in your class path).
3.
As needed, copy your JDBC drivers file to BE_HOME/lib/ext (or other location in your class path).
4.
Open the be-jdbcdeploy.tra file for editing
a.
Specify which type of database you are using. The default value is oracle. Do not change the other configuration properties shown:

 
# BE base type schema file
java.property.jdbcdeploy.bootstrap.basetype.file %BE_HOME%/bin/base_types.xml
java.property.jdbcdeploy.bootstrap.keyword.file %BE_HOME%/bin/dbkeywordmap.xml
java.property.jdbcdeploy.database.type [oracle | sqlserver]

 
b.
Task C Run the Initialize Database Script as the System User
This script creates the TIBCO BusinessEvents user and initializes the database.
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.
As desired, change the default TIBCO BusinessEvents user credentials: Open the initialize_databaseYourDBMS.sql script for editing and change the default username and password. The documentation uses the default username (be_user) and password (be_user)
2.
Navigate to the location of the scripts (by default in the BE_HOME/bin directory) and open an SQLPlus or OSQL prompt. (For example, open a command window, type SQLPlus then provide the system user credentials.)
3.
osql -S Your-Server-Name -U system_user -P sys_user_password -n -i initialize_database_sqlserver.sql
For Oracle you would use a command like this:
sqlplus system_user/sys_user_password@SID @ initialize_database_oracle.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:
 

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

 
Task D 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.
Navigate to the location of the scripts (by default in the BE_HOME/bin directory) and open an SQLPlus or OSQL prompt. (For example, open a command window, type SQLPlus then provide the system user credentials.)
2.
osql -S Your-Server-Name –d Your-DB-Name -U be_user -P be_user -n -i @create_tables_sqlserver.sql
For Oracle you would use a command like this:
   sqlplus be_user/be_user@SID @ create_tables_oracle.sql
Use the credentials defined in the initialize_database_oracle.sql or initialize_database_sqlserver.sql files. By default those are: username be_user, with password be_user.
Task E Generate the Project-Specific SQL Scripts
Open a command window and navigate to BE_HOME\bin. Run be-jdbcdeploy.exe using a command with the following format:
be-jdbcdeploy [-p property file] [-o schema output file] [EAR Path] [-h]
For example:
be-jdbcdeploy -o acme c:/BEProjects/MyEar.ear
The generated scripts appear in the directory where you run the executable. For example, if you provide the schema output filename acme, you would see files called acme.sql, acme.aliases, and acme_remove.sql.
The user-defined part of the database schema is in the schema output file (yourname.sql) as schema definition commands. In Task G you run this script (together with provided scripts) to build the schema in the database.
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-jdbcdeploy.tra in the current directory.
-h, /h, or /help
Task F Check the Aliases File and Modify Aliases as Desired
The aliases file contains any table names that are longer than the database maximum length for table names, and auto-generated short aliases for them. (For example, the Oracle limit is 30 characters, and the SQL Server limit is 128 characters.)
It’s a good idea to check the aliases file for entries, even if the TIBCO BusinessEvents names are not very long. The length of the generated database table names is not easy to predict.
Optionally, you can edit the file to provide more meaningful names.
It is recommended that you keep the aliases file for future reference. If the project ontology changes after the backing store has data in it, you must also update the database schema to match (as explained in Updating an Existing Backing Store Database Schema). If you modified the generated aliases, you must use the same aliases again when you update the schema, to preserve those columns and their data.
Key word mapping file
Entries in the key word mapping file are also added to the aliases file so you can replace the key word aliases with project-specific ones, as desired (generally in a second pass). For details see Task H, If Needed — Map Key (Reserved) Words to Aliases.
1.
Open the yourname.aliases file for editing.
2.
Replace any aliases as desired with more meaningful short names. Make sure that each name is unique. It’s a good idea to leave any system generated prefixes or suffixes in place for consistency of names across the database.
3.
Perform Task E, Generate the Project-Specific SQL Scripts, again. This time, the aliases you created are used.
Task G Run the Project Schema Script (as be_user)
In this step, you log on as the user you created and run a script to create the project related part of the database schema.
The schema combines the definitions in base-types.sql, create-tables.sql, and the generated schema file, yourname.sql (acme.sql as an example).
1.
Login to the database server as be_user, password be_user—or whatever username and password you set in the script in Task C).
2.
Navigate to the location of the scripts and open an SQLPlus prompt (Oracle) or OSQL prompt (SQL Server). Identify yourself as the TIBCO BusinessEvents user again.
3.
@yourname.sql (for example, @acme.sql )
If there are no errors, your database tables are now configured for use. If there are errors you may need to add some mappings to the key word mapping file.
Task H If Needed — Map Key (Reserved) Words to Aliases
Complete this task only if you saw errors after completing Task G, Run the Project Schema Script (as be_user). Such errors are caused when your project ontology uses terms that are key words (reserved terms) in the DBMS you are using. You must map these terms to an alias in the keyword mapping file.
1.
Edit the BE_HOME/dbkeywordmap.xml file to add entries. Below is the format followed by an example:
   <keyword name=“dbKeyWord" mapname=“nonDbKeyWord"/>
   <keyword name="start" mapname="start_"/>
2.
*0
Providing Project-Specific Key Word Aliases  When you repeat Task E, the new key words are added to the yourname.aliases file. You can create project-specific aliases for the key word mappings as desired. Then repeat Task E again and continue.
Note that you must generate the SQL scripts a total of three times if you add keyword mappings to the aliases file — a summary of the whole procedure is as follows:
Next Step
After you have set up the database, perform project configuration activities as explained in See Adding a JDBC Connection Resource to the Studio Project and Configuring Backing Store Properties in the CDD Editor for details.
Also, remember to update your schema if your ontology changes. See Updating an Existing Backing Store Database Schema