Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 15 JDBC Backing Store Setup : JDBC Backing Store Database Setup Utility Tasks

JDBC Backing Store Database Setup Utility Tasks
All the tasks in this section explain how to use the database setup utility. Ensure that you have done all earlier tasks that pertain to your case (see Cases That May Need Additional Setup).
Task F Run the Initialize Database Script as the DBA or 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.
The first time you run the create_tables_YourDBMS.sql script, you see harmless error or warning messages because there is nothing to delete.
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.
Open a command window in the BE_HOME/bin directory (default location of the scripts), and the appropriate command for your DBMS at the prompt:
For Oracle:
sqlplus sys_user/sys_user_password@SID @ initialize_database_oracle.sql
For SQL Server:
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 G Run the Create Tables Scripts as the TIBCO BusinessEvents User
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 the BE_HOME/bin directory (default location of the scripts), and type the appropriate command for your DBMS at the prompt:
For Oracle:
   sqlplus BE_USER/BE_USER@SID @ create_tables_oracle.sql
For SQL Server:
osql -S Your-Server –d Your-DB -U BE_USER -P BE_USER -n -i @create_tables_sqlserver.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.
You see various harmless error messages the first time you run the script.
Task H Configure the be-jdbcdeploy TRA file and Database Driver Files
Providing the database URL and driver files enables the setup utility to generate the yourname_alter.sql script, which is used when you have to update the backing store schema.
1.
Open the BE_HOME/bin/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:

 
#
# Base type schema/keyword files:
# Valid database types: oracle, sqlserver
#
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.
2.
If you did not already do so, copy your JDBC drivers file to BE_HOME/lib/ext/tpcl (or other location in your class path).
3.
Task I Generate the Project-Schema-Specific SQL Scripts
This step requires the EAR file for the project. The database utility uses the project ontology information from the EAR file.
1.
2.
Run be-jdbcdeploy.exe using a command with the following format:
be-jdbcdeploy [-h] [-p property file] [-o schema output file] [-c CDDpath] EAR Path
For example:
be-jdbcdeploy -o acme -c D:/myproj/acme.cdd D:/ears/acme.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, acme_alter.sql, acme_delete.sql, 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 K 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
Optional. 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
Optional. Specifies whether ANSI compatible SQL types should be used during script generation or not.
Allowable values are true and false. For ANSI compatible databases, it is set to true by default.
Task J Check the Aliases File and Modify Aliases as Desired
For every entity, property, or state machine whose database identifier name exceeds the database maximum length, a table name entry is created in the generated yourname.aliases file (for example, acme.aliases). See Ontology Identifiers that Exceed the DBMS Maximum Column Length for more information, and for an alternative way to specify short table names.
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 the new schema (as explained in Updating an Existing Backing Store 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 L, 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.
Re-run the be-jdbcdeploy tool, using the same parameters as before. (For details see Task I, Generate the Project-Schema-Specific SQL Scripts.) This time, the aliases you created are used.
Task K 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.
1.
Login to the database server as BE_USER, password BE_USER—or whatever username and password you set in the script in Task F).
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 L If Needed — Map Key (Reserved) Words to Aliases
Complete this task only if you saw errors after completing Task K, 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 I, 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 I again and continue.
Note that you must generate the SQL scripts a minimum of three times if you add keyword mappings to the aliases file — it might be more because you may not catch all errors at once. For example, if there are multiple keyword clashes in one table, only the first are reported. Perform this loop until no more errors occur. A summary of the basic routine is as follows:
Next Step
After you have set up the database, perform any project configuration activities you have not yet completed. These activities can be done before or after database setup. See the following sections:
Also, remember to update your schema if your ontology changes, or if you want to include or exclude different entities in the backing store. See Updating an Existing Backing Store Schema for details.

Copyright © TIBCO Software Inc. All Rights Reserved