Chapter 5 Databases

Netscape Application Server: Process Automation Edition (PAE) 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 PAE types and the data types used by each kind of database, so process designers can design process definitions that are database independent.

• Overview
• Using Databases With PAE
• Setting Up Your Database

• Oracle 8.0.5
• Sybase 11.9.2 (server), 11.1.1 (client)

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

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

• Cross-Application Tables
• Primary & Foreign Keys
• Database Views
• Database Users
• Database Adapters
• Storing Digital Signatures
• Setting Up Your Database
• Database Connections

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

• 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
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 from process_instance pi, work_item wi

where pi.wf_instance_id = wi.wf_instance_id (+))

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

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.

• Database server type (Oracle or Sybase)
• Database server identifier
• 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 Sybase only)

1. From Netscape Console or the Windows NT Start menu, launch NAS Administrator.
2. On the NAS 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.

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



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

• 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

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

maximum connections	timeout
free slots	interval

2. Click Apply Settings to save the changes to NAS.