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 describes these topics:

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

Process Manager supports the following databases:

• Oracle 8.0.5, 8i (server), Oracle 8.1.6 (client)



• Sybase 11.9.2, 12 (server), 11.1.1 (client)



• Informix 7.3, 9.1.4 (server), 2.4 (client)




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



• Primary & Foreign Keys



• Database Views



• Database Users



• Database Adapters



• Storing Digital Signatures



• Setting Up Your Database



• Database Connections

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 it last worked on it.



• work_item table: Tracks work item data such as each work item's expiration date and who it is currently assigned to.



• 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. See the Business Manager's Guide for more information.



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

Note that if you are using Informix or Sybase, a database already exists for Process Manager's use. The database administrator of your enterprise should have created 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. Informix and Sybase databases are no longer created during cluster creation.

Information you need to know before you attempt to create a new cluster or to join an existing cluster are:

• Database server type (Informix, Oracle or Sybase)



• Database server identifier



• Database URL (For third party drivers only)



• Driver Identifier (For third party drivers only)



• Valid database user ID (the administrator's user ID). Note that this user cannot be the same as the database owner for Sybase databases



• Password for that user



• Database (for Informix or Sybase only)

See the Installation Guide and the iPlanet Application Server Administration Guide for more information about setting up your database. You can locate iPlanet Application Server documentation in the following location: serverRoot/ias/docs/index.htm

Configuring a Third Party Database Driver

To use Informix, Oracle or Sybase, you need to configure a third party jdbc driver. This must be done before a cluster is created. This is done through the jdbcsetup utility. You can configure up to three third party drivers for use on your system.

To Configure a Third Party Database Driver:

1. Run the utility jdbcsetup located in install_directory/ias/bin.




The Third Party JDBC Configuration dialog appears.

Figure 6-1    Third Party JDBC Configuration Add Dialog

2. Fill in the configuration fields for the driver.




Driver Identifier The unique name for the driver.



Driver Classname The Classname for the third party driver. For example: oracle.jdbc.driver.OracleDriver



Driver Classpath The Classpath to the third party driver. For example: C:\jdbc\lib\classes111.zip



Third Party Native Driver Directory The path to the third party native driver directory. This field is only required when using a Type2 driver.



3. Select OK. To add more than one third party driver, select Add instead of OK and repeat steps 2 and 3 for each driver.

To Edit or Delete a Configured Third Party Database Driver:

1. Run the utility jdbcsetup located in install_directory/ias/bin.




The Third Party JDBC Configuration dialog appears.



2. Select the Edit tab.

Figure 6-2    Third Party JDBC Configuration Edit Dialog

3. Select the driver to edit from the drop-down list.



4. Edit the configuration fields for the driver selected.



5. Select Update.



6. Select Delete to delete a configured driver.



7. Select OK when you have finished editing or deleting drivers.

Database Connections

Database connections are maintained through iPlanet Application Server. You can set connection parameters in iPlanet Application Server (iAS) Administrator.

To set the connection parameters, perform the following steps:

1. From Netscape Console or the Windows NT Start menu, launch iAS Administrator.



2. On the iAS Administrator toolbar, click the Database button to open the Database window.



3. In the left pane of the Database window, click the database for which you want to adjust the timeout parameter.









4. In the right pane of the Database window, in the Connection Timeout field, enter the number of seconds.

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 thread parameters, perform the following steps:

5. In the right pane of the Database window, in the Minimum Threads field, enter the number of threads.









6. In the right pane of the Database window, in the Maximum Threads field, enter the number of threads.

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.

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.

7. In the right pane of the Database window, under Cache, enter values for the following parameters:



• maximum connections



• timeout



• free slots



• interval



8. Click Apply Settings to save the changes to iAS.

It is perhaps worth noting here that Process Express, Process Administrator, and Process Business Manager also 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.