Populating a database by running SQL scripts

After you have created an empty database schema, you populate the Rule Execution Server database. One way to do this is to run SQL scripts.

The name of the script that creates the database schema is repository_<DatabaseName>.sql. By default, the script is in <InstallDir>/executionserver/databases.

Note:

If you want to use Decision Warehouse, you can also create the required database table by running the script trace_<DatabaseName>.sql. If you have set Java XOM persistence to a database, you must create these tables by running the xomrepository_<DatabaseName>.sql script.

If you use Command Editor to run the scripts, you must log in with the credentials that you use for the data source for Rule Execution Server.

Use any tool that can handle SQL to import and run the SQL scripts. The following table lists the tools that are provided for each database.

Table 1. Database scripting tools
Database	Database tool
IBM® DB2®	DB2 command line processor
Derby	ij command line processor
H2	H2 console
HSQL	HSQL database manager
MySQL	mysql command line processor
Oracle	sqlplus command line processor
SQL Server	Query Tool
Sybase	isql command line processor

To access the database, the database user must secure the following credentials and privileges.

A user ID and a password
Complete privileges on the tables, and a view of the schema (create, insert, delete)
Create index privileges
On Oracle, create trigger and create sequence privileges

Install a database client for the database that you use, and refer to the documentation of your database system for more information. The following example assumes that you have installed the Derby database.

Example: Running the Derby SQL scripts

A sample Derby data schema is used in describing the installation of Rule Execution Server. You must copy the derby.jar and derbytools.jar files to the <TOMCAT_HOME>/lib directory. (You can download the Derby database from The Apache DB Project.) You must add the <TOMCAT_HOME>/lib directory to the classpath.

Note: The following steps are optional if you are going to use a JDBC data source. After you have defined the Derby data source, you can modify the createDatabase custom property and set it to create. The database is created by the first database connection.

To create a schema in Derby on Windows:

Stop Tomcat:
- Open Apache Tomcat 7.0 Tomcat Properties on your Windows taskbar, or click Tomcat7w.exe in {TOMCAT_HOME}/bin.
- Click Stop in the General tab.
Launch <DERBY_HOME>/bin/ij.bat.
Create and connect to the database. For example, to create and connect to the new database resdb as the user resAdmin, use the command:
```
ij> connect 'jdbc:derby:resdb;user=resAdmin;password=resAdmin;create=true';
```
Run the script that creates the database schema:

ij> run '<InstallDir>/executionserver/databases/repository_derby.sql';

If the script is being run for the first time, some errors related to the drop statements might occur.

Note:
If you have installed Decision Validation Services, you must also create the required database schema by running the script trace_derby.sql. See Additional steps to configure Decision Validation Services for more information.
Close the ij utility:

ij> quit;
Start Tomcat:
- Open Apache Tomcat 7.0 Tomcat Properties on your Windows taskbar, or click Tomcat7w.exe in {TOMCAT_HOME}/bin.
- Click Start in the General tab.