Configuring the Oracle Schema

Status

If you are...	This task is...
Installing a new iProcess Engine Version 11.8.0	Optional
Upgrading from an iProcess Engine Version 11.0 or later	N/A

Database

Oracle

Description

When running the installer, it creates a small database (approximately 50 MB) that is suitable for benchmarking or development purposes by default).

You can modify this default database configuration if you want to optimize it for your Oracle environment, taking into account factors specific to your installation, such as the number of cases, the amount of case data, the life of cases, and so on.

The Default iProcess Engine Schema Configuration

By default, the installer performs the following operations when it creates the iProcess Engine schema:

• It creates the following tablespaces for the Oracle instance. In the non- RAC environment, the default tablespaces location is ORACLE_HOME\database. In a RAC environment, the installer does not specify the datefile value, and RAC determines the tablespaces location automatically.

— a data tablespace (with a default name of staffwar). By default, all iProcess Engine tables, indexes, and Oracle AQ queues are stored in this tablespace.

— a temporary tablespace (with a default name of temp), which is used for sorting.

•

It creates the following Oracle users:

— iProcess Engine DB Schema Owner user (with a default name of swpro).

— iProcess Engine DB User (with a default name of swuser).

The staffwar and temp tablespaces are allocated to these users as their default tablespaces.

• It creates the iProcess Engine schema tables, indexes, and Oracle AQ queues in the default staffwar tablespace.

Note	The use of the `staffwar` tablespace is defined in the `tablesizes` file. See How the Installer Sets Up the Default Configuration for details.

How the Installer Sets Up the Default Configuration

The installer uses the init2Kora_tok.sql file and the tablesizes file as the template files to create the iProcess Engine schema:

• the init2Kora_tok.sql file

The init2Kora_tok.sql file defines the following configuration macros for each iProcess Engine table and index that is to be created:

— SIZETABLESIZE—the initial size (in extents) of a table.

— SIZETABLEPCTINCREASE—the percentage increase to be applied when growing a table.

— SIZETABLESPACE—the tablespace to be used by a table.

— SIZEINDEXSPACE—the tablespace to be used by an index.

where SIZE is one of seven different categories, as shown in SIZE Categories .

SIZE Categories
Category	Used For	Example
TINY	Small tables that do not grow.	`flag_table procedure_lock`
SMALL	Generally static tables that contain small amounts of data.	`list_names dbs_names`
MEDIUM	Generally static tables that contain more data.	`user_names user_values`
BIG	Slightly larger tables that typically hold values for references held in SMALL and MEDIUM tables.	`user_values db_str_values`
LARGE	Most of the tables that contain case-related information, typically ones that only have 1 or 2 rows per case.	`case_information outstanding_addr staffo`
HUGE	Tables that hold multiple rows per case, but not for all cases.	`pack_data pack_memo`
MASSIVE	Tables that hold multiple rows per case for all cases.	`audit_trail case_data`

Note	See TIBCO iProcess Engine (Oracle) Administrator’s Guide to see which macros are defined for each table and index in the iProcess Engine database.

• the tablesizes file

The tablesizes file defines a real value for each configuration macro in the init2Kora_tok.sql file. For example, the default values for the different TABLESIZE macros are shown :

# # Initial size (in extents) for each # category of table. # TINYTABLESIZE=1K SMALLTABLESIZE=2K MEDIUMTABLESIZE=40K BIGTABLESIZE=200K LARGETABLESIZE=500K HUGETABLESIZE=500K MASSIVETABLESIZE=1M

How the Installer Uses These Template Files

When you run the installer to install the iProcess Engine:

1. The installer copies the following files to the $SWDIR/util directory:

— init2Kora_tok.sql

— swinitora

— the tablesizes file specified in the Schema Sizing Configuration item on the Oracle Database Connection and Account Details Menu.

2. The installer calls the $SWDIR/util/swinitora script.

3. The $SWDIR/util/swinitora script pre-processes the $SWDIR/util/init2Kora_tok.sql script to:

— replace the configuration macros with the actual values from the $SWDIR/util/tablesizes file.

— replace occurrences of the default data tablespace name (staffwar) with the name specified in the Data Tablespace Name item on the Oracle Database Connection and Account Details Menu.

The $SWDIR/util/swinitora script then saves the results as the $SWDIR/util/init2Kora.sql script.

4. The $SWDIR/util/init2Kora.sql script creates the iProcess Engine database schema.

How to Change the Default Configuration

You can change the default configuration of the iProcess Engine schema to match your particular requirements. Depending on the level of configuration control you need, you can use any combination of the following methods:

•

Change the Default Data or Temporary Tablespace

There are two ways in which you can change the default data tablespace to be used:

—

The installer can automatically create the default data and temporary tablespaces for you. Menu options and prompts allow you to choose:

—the default tablespace names (staffwar and temp), or tablespace names that you specify.

—the default tablespace location ($ORACLE_HOME/dbs), or directories that you specify. You can use the same directory for both tablespaces, or put each tablespace in a different directory as you require.

See Oracle Database Connection and Account Details Menu for more information. TIBCO recommends that you use this method where possible.

—

Alternatively, you can create the default tablespaces manually before running the installer. You may want to do this if, for example, you want to spread the larger iProcess tables across multiple tablespaces.

Note	If you are using Oracle Real Application Clusters (RAC) you must create all required tablespaces on the RAC shared storage devices before running the installer. If you do not do this, the installer creates the data files for the tablespace in the `ORACLE_HOME/dbs` directory.

To do this:

—Manually create the tablespaces you want to use as the default data (or temporary) tablespaces.

—Edit the tablesizes file that you intend to use. Change the value of each SIZETABLESPACE and SIZEINDEXSPACE macro that you want to use the default data tablespace, from staffwar to your new tablespace name.

Note	If you allow the installer to create the data tablespace for you, it automatically updates the `tablesizes` file to use the correct tablespace. If the `tablesizes` file is not modified, the iProcess Engine tables, indexes and AQ queues will be created in the wrong tablespace.

—When you run the installer, make sure that you specify your new tablespace name as the default value when prompted (See Oracle Database Connection and Account Details Menu). If you have created multiple tablespaces, simply specify the name of one of these tablespaces.

•

Use an Alternative tablesizes File

When you run the installer, you can use the Schema Sizing Configuration option on the Oracle Database Connection and Account Details Menu to choose one of the following alternative tablesizes file:

— tablesizes.med—Using this file will create a medium-sized database, requiring at least 2.5 GB of disk space.

— tablesizes.large—Using this file will create a large-sized database, requiring at least 65 GB of disk space.

Note	See Usage Profiles for Tablesizes Files for more information about the intended usage profiles of these files.

•

Customize Specific Configuration Macros in the tablesizes File

If you require more specific configuration control, you can edit the tablesizes file to change the values assigned to specific configuration macros. For example, you may want to:

— increase the initial size of tables that use the LARGETABLESIZE macro.

— spread the larger iProcess tables across multiple tablespaces or filesystems (to aid i/o performance and reduce contention), by modifying individual SIZETABLESPACE or SIZEINDEXSPACE entries.

Note	Remember to create any additional tablespaces before you run the installer.

To do this:

Copy the tablesizes file from the installation directory to a directory of your choice.
Edit the file according to your requirements.
When you run the installer, use the Schema Sizing Configuration option on the Oracle Database Connection and Account Details Menu to choose your customized tablesizes file.

•

Customize the init2Kora_tok.sql File to Assign Different Configuration Macros or Values to Specific Tables or Indexes

Finally, you can edit the init2Kora_tok.sql file to assign different configuration macros (or hard code specific values) to specific tables and indexes. You can do this when you run the installer.

Note

By default, the predict and predict_lock tables use the SMALL macro values. If you intend to enable background case prediction on your system (using the ENABLE_CASE_PREDICTION process attribute), TIBCO recommends that you change these tables to use a larger value in line with the level of background prediction activity you expect. See "Administering Process Attributes" in TIBCO iProcess Engine Administrator’s Guide for more information.