JDBC Backing Store Configuration : Resources Required for Setting Up the Database

Resources Required for Setting Up the Database
At time of writing, Oracle Database and Microsoft SQL Server are supported. Any later additions would be noted in the product readme file.
DBMS Software and Installation Requirements
The following sections contain a few relevant notes about the database products. See the product readme file for information about supported database versions.
Database Location
Instructions in this chapter assume you are working with a local database for testing purposes. Adapt the instructions if you are working with a remote database. For example, in production environments, you might have to ask a database administrator to create a database user for you. You should then be able to run the other SQL scripts yourself, logged on as the user created by the administrator.
Minimum User Permissions
By default the TIBCO BusinessEvents user permissions are set to DBA privileges. At a minimum, the user must be able to create tables and views. For example for an Oracle database you could use the following:

 
DROP USER BE_ORCL CASCADE;
CREATE USER BE_ORCL IDENTIFIED BY BE_ORCL;
GRANT CONNECT TO BE_ORCL;
GRANT resource TO BE_ORCL;
GRANT CREATE ANY VIEW TO BE_ORCL;
GRANT CREATE ANY TABLE TO BE_ORCL;

 
SQL Server
Here are a few helpful points about SQL Server:
For non-production purposes, it is convenient to use SQL Server authentication so you can create database users as needed. Select this option when you install Microsoft SQL Server. With Windows Authentication, on the other hand, you may have difficulties creating users without help from others in your enterprise.
Microsoft SQL Server 2008 has added a new data type, datetime2, which has a date range of 0001/01/01 through 9999/12/31. Therefore, if you are using Microsoft SQL Server 2008, then you can manually change the generated SQL script (DDL) for your backing store, and replace any affected columns’ data type from datetime to datetime2.
Use the SQL Server JDBC driver, sqljdbc4.jar. You can download this driver from:
http://msdn.microsoft.com/en-us/data/aa937724.aspx
Oracle
Here are a few helpful points about Oracle:
http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc_111060.html
Provided Configuration Resources
The table below lists resources required and sections following explain the procedures for setting up backing store tables.
The base_types.xml file is used by the deployment utility. Do not edit this file.
(The base_types.sql file in the same location is used by the Oracle-only backing store and is not needed here.)
be-jdbcdeploy executable
Use the appropriate SQL (DDL) script for your DBMS. This script creates the tables that are used to maintain the metadata.
This file contains mappings to handle words used in the TIBCO BusinessEvents project that are database reserved words. See Ontology Identifiers That Use Database Key Words for details.
Note  This script drops the user (and therefore all the tables) and adds the user again.
By default the user is called be_user with the password be_user and the user has DBA rights. Edit the script if you want the user to have a different name or different rights.
For SQL Server, this script also creates the default database, with the name be_user and makes it the default database for the user be_user.
These scripts are generated when you run the be-jdbcdeploy executable. Provide a value for yourname when you generate the scripts.
yourname.aliases
yourname_alter.sql
The yourname_alter.sql script is for use in schema migration. See Updating an Existing Backing Store Database Schema.
yourname_remove.sql
For use as needed. This script removes the database schema. You can use it to reset the project.
yourname_cleanup.sql