Sun ONE logo     Previous      Contents     Index      Next     
iPlanet Process Manager, Version 6.5 Process Administrator's and Business Manager's Guide



Chapter 6   Databases

Process Manager stores user data in a relational database. The database provides a centrally available repository for user data for each application in a cluster, plus a common set of tables that all applications can share. In addition, there are adapters for each type of database that handle mapping between Process Manager types and the data types used by each kind of database, so process designers can design process definitions that are database independent.

This chapter contains the following sections:

  • "Overview"
  • "Using Databases With Process Manager"
  • "Setting Up Your Database"


  • Note

    This chapter only provides general guidelines for using databases with Process Manager. For specific situations and error conditions, consult your database administrator (DBA). For example, if you encounter a Sybase log full error, you should refer the problem to your database administrator for a solution.



Overview

Process Manager uses an Informix, Oracle or Sybase database to store process instance data, so you must have a database available to you on your local system or available over a network on another system. If you are using a remote database, you must have the appropriate database client installed on your local system. You need to have enough privileges to create tables, write to tables, and to create views.

For that list of databases that Process Manager supports, see the Release Notes Process Manager at docs.iplanet.com

There are several ways a Process Manager end user and administrator interact with database tables. Sometimes they need read-only access, but sometimes they add new data or update existing data. Note that the database access is transparent to the end user or administrator. They are only aware that they are viewing, adding, or modifying process instance data.

End users are accessing a database when they perform these operations:

  • Create a new process instance: Adds new data to all tables in the database.
  • Work on a work item: Adds new application-specific data and updates some of the cross-application tables.
  • View a process instance detail: Reads from several tables.
  • View a process instance's history: Reads from the history table.

Administrators are accessing a database when they perform these operations:

  • View a process instance detail: Reads from several tables.
  • View a process instance's history: Reads from the history table.
  • Request process instance statistics: Reads from several tables.
  • Archive data: Updates some tables by flagging the database records that match the archiving criteria.
  • Delete data through the Archive and Delete page: Updates some tables by removing the database records that match the deletion criteria. This is not recoverable.
  • Delete data when uninstalling an application: Deletes the application-specific table for the application. This is not recoverable.
  • Delete data when removing a cluster: Deletes the application-specific and the cross-application tables. This is not recoverable.

At each database operation, Process Manager collects data into implicit database transactions to protect application data in the database. Transaction-based processing allows rollbacks and automatic database cleanup if any Process Manager operation fails.



Note

Each of these transactions generates an entry in the database transaction log, so make sure you have plenty of space available for the log. If you are using a Sybase database, it disconnects automatically when there is not enough space for additional transaction log entries. You can increase the transaction log size for your Sybase database to help avoid this problem.



Using Databases With Process Manager

To provide consistency across all applications in a cluster, all applications share a single relational database.

This section includes these topics:

Cross-Application Tables

The database has six common, cross-application tables that are shared across all applications and one application-specific table for each application (this value is used in the DB Application Table field in the application's definition in Process Builder). The cross-application tables contain standard data for each process instance and work item, while the application-specific tables contain the user data that is unique to each application.

The cross-application tables are:

  • process_instance table: Maintains process_instance data such as each process instance's state, application, and creation and modification dates. It also tracks who initiated the process instance and who last worked on it.
  • work_item table: Tracks work item data such as each work item's expiration date and to whom the work item is currently assigned.
  • history table: Maintains all historical data for each process instance.
  • wf_id_range table: Generates a new unique process instance ID whenever an end-user initiates a new process instance. This table has a single field, next range, indicating the next ID to create.
  • wf-blobs table: Stores digital signatures.
  • dynamic_group table: Stores entries for groups that are not statically defined in the corporate directory or in the application definition.
  • Table 6-1    Cross-application tables 

    process_instance

    work_item

    history

    wf_blobs

    dynamic_group

    wf_instance_id

     

    wf_instance_id

     

    wf_instance_id

     

    wf_instance_id

     

    wf_instance_id

     

    wf_creation_date

     

    wf_fork_id

     

    wf_fork_id

     

    wf_field_cn

     

    wf_fork_id

     

    wf_creator_dn

     

    wf_node_cn

     

    wf_event_id

     

    wf_blob_content

     

    wf_node_cn

     

    wf_title

     

    wf_wi_state

     

    wf_event_type

     

     

    wf_group_id

     

    wf_priority

     

    wf_expired_flag

     

    wf_event_date

     

     

    wf_group_flag

     

    wf_entry_cn

     

    wf_wi_cdate

     

    wf_wi_cdate

     

     

    wf_user_id

     

    wf_exit_cn

     

    wf_exp_date

     

    wf_wi_exp_date

     

     

     

    wf_last_modified

     

    wf_user_dn

     

    wf_user_dn

     

     

     

    wf_pi_state

     

    wf_target_cn

     

    wf_app_cn

     

     

     

    wf_app_cn

     

    wf_performer_url

     

    wf_comment

     

     

     

    wf_observer_url

     

    wf_automated_flag

     

    wf_node_cn

     

     

     

    wf_performer_url

     

    wf_deferred_flag

     

     

     

     

    wf_archived_flag

     

    wf_app_cn

     

     

     

     

     

    wf_execution_stage

     

     

     

     



    Note

    In a Sybase database, the maximum number of bytes per row is 1962. If you create tables with varchar, nvarchar, or varbinary columns whose total defined width is greater than 1962 bytes, a warning message appears, but the table is created.



Primary & Foreign Keys

The database tables are set up to share the process instance, wf_instance_id, as a key. The process_instance table uses the wf_instance_id field as the primary key. The wf_instance_id field in other tables is considered as a foreign key. The work_item table uses a concatenated key of wf_instance_id, wf_fork_id, and wf_node_cn to precisely identify a work item in a parallel processing application, where there may be several work items for a specific process instance.

Database Views

To allow simplified queries, Process Manager provides some standard views into the database information. These offer a predefined subset of the data available in the database.

Process Manager constructs these views:

  • closed_process: Uses the process_instance table in this query:

  • SELECT wf_instance_id, wf_creation_date,
    wf_creator_dn, wf_title,
    wf_priority, wf_entry_cn,
    wf_exit_cn wf_node_cn, wf_last_modified,
    wf_pi_state, wf_app_cn,
    wf_observer_url, wf_performer_url,
    wf_archived_flag
    FROM process_instance WHERE wf_pi_state >= 4


  • worklist: Uses the process_instance and work_item tables in this query:

  • SELECT wi.wf_instance_id, wi.wf_performer_url,
    wf_observer_url, wf_fork_id,
    wf_node_cn, wf_wi_state,
    wf_expired_flag, wf_wi_cdate,
    wf_exp_date, wf_user_dn, wf_title,
    wf_priority, wf_creation_date,
    wf_creator_dn, wf_entry_cn,
    wf_last_modified, wf_pi_state,
    wf_target_cn, wf_app_cn
    wf_automated_flag, wf_deferred_flag,
    wf_execution_stage
    FROM process_instance pi, work_item wi
    WHERE wi.wf_instance_id = pi.wf_instance_id


  • application-specific view: Uses the process_instance and the application-specific tables in a query such as this:

  • SELECT pi.wf_instance_id, fieldrole, datefield, textfield, radio,
    wf_creation_date, wf_creator_dn,
    wf_last_modified, wf_app_cn,
    wf_ pi_state, wf_entry_cn, wf_title,
    wf_priority, wf_exit_cn, wf_archived_flag,
    wf_performer_url,wf_observer_url
    FROM user1.process_instance pi, child app
    WHERE pi.wf_instance_id = app.wf_instance_id


  • searchlist view: Uses the process_instance table in a query such as this:

  • SELECT pi.wf_instance_id wf_instance_id,
    wf_creation_date, wf_creator_dn, wf_title, wf_priority,
    wf_entry_cn, wf_exit_cn, wf_last_modified, wf_pi_state,
    wf_app_cn, wf_observer_url, wf_archived_flag, wf_fork_id,
    wf_node_cn, wf_wi_state, wf_expired_flag, wf_wi_cdate,
    wf_exp_date, wf_user_dn, wf_target_cn, wi.wf_performer_url,
    wf_deferred_flag
    wf_deferred_flag, wf_automated_flag, wf_execution_stage
    from process_instance pi, work_item wi
    where pi.wf_instance_id = wi.wf_instance_id (+))


Database Users

There are three kinds of database users:

  • Administrator
  • Has create and write access to all cross-application and application-specific tables and views. This is the database user that you identify when creating a cluster. This administrator is responsible for setting up your database.

  • Business Manager
  • Also has create and write access to all cross-application and application-specific tables and views. This business manager is responsible for process instance-specific database issues.

  • application-specific user
  • Has create and write access to their own application's tables and views. Has read-only access to cross-application tables and views. Has no access to other application-specific tables.

    If you have an application-specific database user, the Process Builder designer identifies this as the DB User in the application's properties window when deploying the application.

Database Adapters

Each type of database supported by Process Manager has its own adapter. After the database type is determined, the adapter handles storing data in the database and takes care of such database-specific characteristics as type mapping.

Because type mapping in Process Manager is handled by the database adapters, Process Builder designers can use a single set of types that can be correctly interpreted for each type of database. Designers do not need to concern themselves about how their data types are stored in a given database.



Note

Designers who write their own customized scripts cannot use certain transaction control statements in their scripts such as begin, commit, and abort. In addition, if they are using an Oracle database, they cannot use DDL in their script statements. Also, Process Builder does not check for all reserved words for every database. If a custom script contains a reserved word, your application deployment may fail.



Storing Digital Signatures

Process Manager has a special database table for storing digital signatures, wf_blobs.

Table 6-2    Fields in the wf_blobs database table

wf_blobs

wf_instance_id

 

wf_field_cn

 

wf_blob_content

 

You can perform dbadmin queries against this table to verify digital signatures as required. For example, you could execute this query:

SELECT * from wf_blobs WHERE wf_instance_id = <ID> and wf-field_cn = '<sig-field>'

to obtain the user ID, user name, and digital signature for a specific process instance. Note that to do this, you will need the process instance ID.

Setting Up Your Database

Process Manager requires the use of an existing Informix, Oracle or Sybase database. You can use whatever database server you already have installed, or you can install a new database server for your Process Manager data.

Informix and Sybase databases are not created during cluster creation. Note that if you are using Informix or Sybase, a database must already exist for Process Manager's use. The database administrator of your enterprise should create this database before creating a cluster. The database administrator must make sure the Process Manager administrative user has create table/create view privileges on that database.

See the iPlanet Application Server Installation Guide and the iPlanet Application Server Administrator's Guide for more information about setting up database connectivity and registering third-party jdbc drivers. You can locate iPlanet Application Server documentation in the following location: serverRoot/ias/docs/index.htm

Database Connections

Database connections are maintained through iPlanet Application Server. You can set connection parameters using the iPlanet Application Server (iAS) Administration Tool.

To set the database timeout connection parameters

  1. From iPlanet Console, launch iAS Administrator.
  2. Select the Database button on the iAS Administrator toolbar to open the Database window.
  3. In the left pane of the Database window, select the database for which you want to adjust the timeout parameter.


  4. In the right pane of the Database window, enter the desired connection timeout duration as a number in seconds in the Connection Timeout field.


You can also set the minimum and maximum number of threads available for database connections. The thread parameters determine how many threads iAS allocates for asynchronous database queries. Such threads are usually used for queries returning a large number of rows and allowing the application to do other tasks while waiting for the query to finish. Asynchronous database queries are not supported by JDBC 2.0, a Java programming interface used to build on top on database drivers.

The default thread allocations are adequate for most applications. If an application developer uses many asynchronous queries, you might want to increase the maximum number of available threads. Keep in mind that each thread does use a small stack allocation and pulls from the total number of available system threads. Therefore, if an application does not use any asynchronous queries, you can increase performance by setting the maximum available threads to zero.

To set the minimum and maximum database thread parameters

  1. With the iAS Administrator Database properties window still open, enter the desired number of minimum threads in the Minimum Threads field.


  2. Enter the desired number of maximum threads in the Maximum Threads field.
  3. Select Apply Settings to save the changes to the Application Server.

The database cache is an array used to hold active and recently used database connections. iPlanet Application Server adds database connections to cache when an application creates a database connection.

While the application is using that database connection, iAS marks that connection "in use." Once the database operations are finished, the server marks the database connection "free." The cache then holds the free connection in the cache for a configured period of time. This allows the server to use the free cached connection and quickly handle a new request to the same database. Once a free connection exceeds the timeout, a cleaning thread removes the connection from the cache and opens a slot for a new connection to be cached.



Note

Process Express, Process Administrator, and Process Business Manager each use a database connection. Whenever you are tracking the number of connections in use at a given time, remember that two connections are always taken by these components.



You can adjust the following cache parameters:

  • the maximum number of connections allowed in the cache
  • the number of slots held solely for free connections
  • the timeout limit, in seconds, for free connections
  • the interval, in seconds, at which the cache cleaner thread removes timed-out free connections

The default values are adequate for most applications, so adjustments are not usually required for initial application installations.

iAS dynamically adjusts the cache up to the maximum number of allowable connections. If there are no connections to cache, the array is allocated to zero spaces.

To adjust database cache parameters

  1. With the iAS Administrator Database properties window still open, enter the desired values for the following parameters in the Cache property fields:
    • Maximum Connections
    • Free Slots
    • Timeout
    • Interval

  2. Select Apply Settings to save the changes to Application Server.

Previous      Contents     Index      Next     
Copyright 2002 Sun Microsystems, Inc. All rights reserved.


816-6352-10