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).
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:This script creates the TIBCO BusinessEvents database user. This user must be used to run the other scripts. You see messages like the following:
Using your database product, you can configure additional users to access the database, in addition to this 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: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.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.
a. Specify which type of database you are using. The default value is oracle. Do not change the other configuration properties shown:
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).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: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.
Optional. Specifies the property file. If not specified, the default property file is used, that is, be-jdbcdeploy.tra in the current directory. Optional. Specifies the absolute path to the CDD file. See Cases That May Need Additional Setup to understand when the CDD file is needed. 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.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.
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 fileEntries 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.
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.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.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.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.
2. Repeat Task I, Generate the Project-Schema-Specific SQL Scripts, and tasks following as needed.
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:
2. Errors occur due to key word clashes, so you add the appropriate key word mapping entries to the key word mapping file.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.