Open topic with navigation

TIBCO iProcess® Engine 11.9.1

# 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:

| — | `SIZE` `TABLESIZE`—the initial size (in extents) of a table.

| — | `SIZE` `TABLEPCTINCREASE`—the percentage increase to be applied when growing a table.

| — | `SIZE` `TABLESPACE`—the tablespace to be used by a table.

| — | `SIZE` `INDEXSPACE`—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 `SIZE` `TABLESPACE` and `SIZE` `INDEXSPACE` 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 `SIZE` `TABLESPACE` or `SIZE` `INDEXSPACE` entries.

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

To do this:

1. Copy the `tablesizes` file from the installation directory to a directory of your choice.

2. Edit the file according to your requirements.

3. 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.

## See Also

For more information about how to estimate the size and layout requirements of your iProcess Engine schema, consult the following sources:

| • | TIBCO iProcess Engine (Oracle) Administrator’s Guide—This guide defines the structure of each table, and provides guidance on the number of records a table should contain depending on the iProcess data.

| • | the `swinitora` and `init2Kora.sql` scripts in the installation directory.

| • | your TIBCO representative, who can work with you to prepare a detailed sizing and on figuration estimate.

Did you find this helpful?

Yes No
Great!
Thanks for taking the time to give us some feedback.
Sorry about that

Why wasn't this helpful? (check all that apply)

Could not find what I was looking for
Instructions confusing or unclear
Instructions did not work

Want to tell us more?

Please select at least one option or provide details about the issue!!

Error occurred while submitting feedback.

Error2 occurred while submitting feedback.

TIBCO iProcess® Engine

Copyright © 1994-2024. Cloud Software Group, Inc. All Rights Reserved.