Creating the database schema by running SQL scripts

After you create an empty database, you create the schema for the Rule Execution Server database. One way of doing so consists in running SQL scripts.

About this task

The script that creates the database schema is named repository_<DatabaseName>.sql. This procedure shows the Derby and DB2® examples.

Tip: Be careful when you use SQL collation: Operational Decision Manager databases must always remain case-consistent.

Note:

If you want to use Decision Warehouse, you can also create the required database tables by running the script trace_<DatabaseName>.sql. If you also store the Java™ XOMs in 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. To access the database, the database user must have the following rights:

A user ID
A password
Complete privileges on the tables and view of the schema (create, insert, delete)
Privileges for index creation (create index)
On Oracle, additional creation privileges: create trigger and create sequence.

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

Database	Database tool
IBM® DB2	DB2 command line processor
Derby	ij command line processor
MySQL	mysql command line processor
Oracle	sqlplus command line processor - Run all the scripts in the SQL Plus client.
Postgre SQL	Postgre SQL command-line tool
SQL Server	Query Tool
Sybase	isql command line processor

The following example shows how to run the Derby SQL script to create the schema. It is assumed that the embedded version of Derby is used.

Procedure

Stop the application server.
Connect to the database.
For example, to create and connect to the database c:/resdb as the user resdbUser, use the command:
```
ij>connect 'jdbc:derby:c:/resdb;user=resdbUser;password=resdbUser;';
```
If the script runs 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 necessary database schema by running the trace_derby.sql script. For more information, see Additional steps to configure Decision Validation Services.
Run the script that creates the database schema.
Close the ij utility.
```
ij> quit;
```
Start the application server.

Running SQL scripts to create a DB2 schema

If you work with a DB2 database, you can use SQL scripts from an SQL Plus client and specific constraints apply.

About this task

The script that creates the DB2 database schema is named repository_db2.sql. You can also create the database schema for Decision Warehouse by running the script trace_db2.sql. If you are storing the Java XOM in the database, you must create these tables by running the xomrepository_db2.sql script.

When you use DB2, the scripts that create the Rule Execution Server database tables are written for databases that use automatic storage.

Important: Users who work with DB2 on z/OS® as the back-end database for the rules at run time must apply a fix to a database storage overflow problem. This fix is particularly important for the Decision Warehouse trace table, which can contain a lot of high-volume LOB data. You can find information about this fix on the IBM support web site.

The following constraints apply:

BP32K is the buffer pool that is expected in SYSCAT.BUFFERPOOLS. If BP32K is not there, you can use the existing buffer pool or create a new buffer pool named BP32K. Use the following command to query SYSCAT.BUFFERPOOLS for the existing buffer pool:
```
Select * from SYSCAT.BUFFERPOOLS
```
Otherwise, use the following command to create a buffer pool named BP32K:
```
CREATE BUFFERPOOL BP32K SIZE 2000 PAGESIZE 32K
```
You must update the trace_db2.sql script and select the custom option in the Installation Settings wizard to run it. Modify the following line in the script to specify storage for tablespace:
```
CREATE TABLESPACE RESDWTS PAGESIZE 32K BUFFERPOOL BP32K;
```
Here is an example of the tablespace specification in the script:
```
CREATE  TABLESPACE RESDWTS PAGESIZE 32K  MANAGED BY Database  USING [ FILE 'C:\DB2\Container.file' 640 ] BUFFERPOOL  BP32K;
```
You might have to further modify the script, depending on your database settings.

Procedure

Connect to the Rule Execution Server database:
```
CONNECT TO{RESDBName}
QUIT
```
Navigate to the directory <ODM_InstallDir>/executionserver/databases.
Run the following command to create the Rule Execution Server tables:
```
db2 -tvf repository_db2.sql
```
Optional: Run the following command to create the Decision Warehouse tables:
```
db2 -tvf trace_db2.sql
```
Optional: Run the following command to create the Java XOM tables:
```
db2 -tvf xomrepository_db2.sql
```
Disconnect from the database:
```
db2 DISCONNECT {RESDBName}
```
Exit the {DB2AdminName} UserID:
```
EXIT
```