The topics listed here provide information about how to administer the Sun Business Process Manager Database in the Sun JavaTM Composite Application Platform Suite (Java CAPS).
If you have any questions or problems, see the Java CAPS web site at http://goldstar.stc.com/support.
To create the monitoring and recovery database tables, you can either modify and run database scripts from Enterprise Designer or you can download the scripts and run them from a local directory. Depending on which method you prefer, review one of the following sections:
You must have an existing database instance before you can run any of the scripts. Before beginning to work with the scripts, see Creating the Database Instance below.
Before you can run the database scripts, you must create a database instance in which the tables will be created. This is standard for Oracle 10g, Oracle 9i, and SQL Server, but requires additional steps for Oracle 8.1.7, Sybase, and DB2.
For Oracle 8.1.7, you must either increase the default db_block_size from 8KB to 16KB or edit the database script to run successfully with the default db_block_size of 8KB.
In order to run the database installation scripts in Sybase, the server page size must be 16k. Set the server page size when you create the adaptive server on which you will run the Sybase scripts.
Using DB2 with Sun BPM requires a few extra setup steps. Review this section and ask your database administrator to review the installation script prior to running any scripts against your DB2 database instance. The database administrator needs to use the DBA or Sysadmin/DB2 user to create the following:
A database instance on the server.
A tablespace named BPM60DB.
A new user that has privileges to create objects in the database such as tables, indexes, sequences objects, and so on.
A 32k User temporary tablespace. A User temporary tablespace gives Sun BPM space for declared temporary tables. Use the DB2 administrative tool to create the tablespace. For detailed information about creating a DB2 User temporary tablespace, see the DB2 documentation. When you create the temporary tablespace, use the following parameter settings:
Pagesize: 32KB
Buffer Pool Size: 32KB
System-managed space
Average table size: 2GB to 20GB
Prefetch size: 32KB
Extent size: 32KB
Overhead: 10.5 ms
Transfer rate: 0.14 ms
From Enterprise Designer, you can view, modify, and run the scripts that create or drop the monitoring and recovery database schema. First you must configure the database connection for the scripts.
Before you can run the database scripts, you need to configure the database connection information in the Project Explorer. Configure the connection for the administrator user who has privileges to your database.
In the Project Explorer, expand Sun BPM, and then expand Run Database Scripts.
Right-click Run Database Scripts, click Check Out, and then click Check Out on the dialog box.
Right-click Database Scripts and then click Properties.
Enter the database configuration information described in Configuring the Database Connection.
Click OK.
You can open the database scripts and view, modify, and run them within the Project Explorer. Consult your database administrator when making changes to the database scripts. Some of the changes you might need to make include:
For Oracle, you must specify a valid path for the DATAFILE parameter for both the bpm60db and bpel_data tablespaces. You can also change the tablespace names and disk space allocated for each tablespace.
Changing field length; for example, from varchar(255) to varchar(4000). Do this if you need to accommodate larger field lengths in the tables. You might experience errors if data exceeds the size allowed by the field.
Modifying the login ID and password used to create and access the tables. By default, both the user name and password are “bpm6user”. This is the login information that will be used by the Sun BPM Engine to access the database.
In the Project Explorer, expand Sum BPM.
Make sure Run Database Scripts is checked out, and then expand the folder.
Under Database Scripts, right-click the script you want to modify, and then click Open.
For Oracle 8.1.7 only, reduce the maximum key length for correlation keys from 4000 to 3166. The correlationvalue parameter belongs to the correlationengine and correlationbpinstance tables. The following code examples show the changes.
create table bpm60 correlationengine ( correlationvalue varchar(3166) UNIQUE, applicationreference varchar(255), ...); ... create table bpm60.correlationbpinstance ( correlationvalue varchar(3166) UNIQUE, bpid varchar(50), ...);
Make any other necessary changes to the script. Do not change any of the table or field names.
To run the database script, right-click the script name and click Run. The database instance must be running in order to perform this step.
To save your changes, right-click inside the script file and click Save, or close the file. You are prompted to save or discard your changes. To keep your changes, click Save; otherwise, click Discard.
Before running the database scripts, make sure you have a database instance running and have reviewed the information under Creating the Database Instance and Viewing and Modifying Database Scripts in Enterprise Designer. The connectivity information must also be configured for the scripts.
In the Project Explorer, expand Sum BPM, and then expand Run Database Scripts.
Right-click the file associated with the appropriate database (Oracle Install, SQL Server Install, DB2 Install, or Sybase Install).
Click Run.
In the Project Explorer, expand Sum BPM, and then expand Run Database Scripts.
Right-click the file associated with the appropriate database (Oracle Uninstall, SQL Server Uninstall, DB2 Uninstall, or Sybase Uninstall).
Click Run.
This is an alternative method to running the database scripts from Enterprise Designer. You can download the database scripts and run them outside of the Java CAPS environment. Make sure to review the information under Creating the Database Instance, which contains information specific to Oracle and DB2 databases.
The following files are included in the zipped file.
install_db.bat - Creates the tablespace, users, tables, stored procedures, and any initial values.
uninstall_db.bat - Drops any database components created by the install_db.bat script (that is, it drops tables and users, and deletes stored procedures).
database-specific SQL scripts - Scripts that are called by the install_db.bat and uninstall_db.bat scripts (such as create_tables.sql, drop_tables.sql, and so on).
Readme.txt - Contains additional instructions specific to your database application.
In order to work with the compressed script files, you need to export them to a local directory and then extract the compressed files.
In the Project Explorer, expand Sum BPM, and the expand Download Database Scripts.
Right-click the file associated with the appropriate database (oracle.zip, sqlserver.zip, db2.zip, or sybase.zip).
Click Export.
Browse to a local folder to store the database files, and then click Save.
Navigate to the local folder and extract the compressed files.
Follow the instructions in the Readme.txt file.
Once you download the compressed database scripts and modify them as needed, you can run the scripts from a command line. The database user that executes these scripts must have permission to create tables and users. Review Viewing and Modifying Database Scripts in Enterprise Designer before running the scripts (additional information is also available in the Readme.txt file).
Open a command window and navigate to the directory where scripts are located.
Do one of the following:
For Oracle databases, enter the following command:
install_db user password database
For Sybase and SQL Server databases, enter the following command:
install_db user password server database
For DB2 databases, enter the following command:
install_db user password database server
Where:
user is the database username.
password is the database user password.
server is the name of the machine on which the database resides.
database is the database or SID name.
The default user and password created from these scripts is "bpm6user". You can modify the user, password, disk space allocated for tables, and user permissions. The table and column definitions should not be modified.
You can choose to run the database scripts automatically when you deploy a Project to a server. Scripts can be automatically run for both the monitoring and recovery database and for the Business Process reporting tables.
Create the database instance, as described in Creating the Database Instance.
Modify the monitoring and recovery database scripts and define the database connection properties, as described in Running the Scripts in Enterprise Designer (but do not run the scripts).
Configure the Business Processes in the Project for persistence and configure the database connection properties (but do not run the scripts).
Configure the Sun BPM Engine for persistence. Set Automatic Execution of Database Scripts to true.
When you deploy the Project, the scripts are run automatically for the monitoring and recovery database and for each Business Process configured for persistence.
Scripts for purging and archiving Sun BPM data are included with the compressed database files that you can download from Enterprise Designer (see Downloading the Compressed Script Files). The purge scripts delete older records (instances only) from the database tables based on the specified number of days, Business Process name, or application name. The archive scripts copy the history records into tables ending with _hist based on the specified number of days, Business Process name, or application name. Only records with a status of COMPLETE, ERROR, TERMINATED, ERROR_ON_RECOVER are purged or archived.
In the instructions in this section, the following arguments are used:
user is the database username.
password is the database user password.
server is the name of the machine on which the database resides.
database is the database or TNS name.
Before you can run the purge and archive scripts you need to install the stored procedures and tables to support the processes. Make sure you have created the database and downloaded the compressed database scripts as described in Downloading the Compressed Script Files before beginning this procedure.
Open a command window and navigate to the directory where you downloaded the scripts.
Do one of the following:
For Oracle and DB2 databases, enter the following command:
install_arch_and_purge_scripts user password database
For Sybase and SQL Server databases, enter the following command:
install_arch_and_purge_scripts user password server database
The following scripts are provided to archive and purge records based on the specified number days. The UNIX versions of these scripts have a .sh extension.
purge_by_days.cmd - Purges the history data by the specified number of days.
arch_by_days.cmd - Archives the history data by the specified number of days.
Open a command window and navigate to the directory where you downloaded the scripts.
Do one of the following:
For Oracle and DB2 databases, enter the following command:
purge_by_days user password database days
For Sybase and SQL Server databases, enter the following command:
purge_by_days user password server database days
Where days is the number of days to retain. For example, if you specify 4 days, then any records older than 4 days are purged.
Open a command window and navigate to the directory where you downloaded the scripts.
Do one of the following:
For Oracle and DB2 databases, enter the following command:
arch_by_days user password database days
For Sybase and SQL Server databases, enter the following command:
arch_by_days user password server database days
Where days is the number of days to retain. For example, if you specify 4 days, then any records older than 4 days are archived.
The following scripts are provided to archive and purge records based on the specified Business Process. The UNIX versions of these scripts have a .sh extension.
purge_by_bpname.cmd - Purges the history data by the specified Business Process name.
arch_by_bpname.cmd - Archives the history data by the specified Business Process name.
Open a command window and navigate to the directory where you downloaded the scripts.
Do one of the following:
For Oracle and DB2 databases, enter the following command:
purge_by_bpname user password database name
For Sybase and SQL Server databases, enter the following command:
purge_by_bpname user password server database name
Where name is the Business Process name to purge. For example, if you specify BusinessProcess1, then all instances of BusinessProcess1 are purged.
Open a command window and navigate to the directory where you downloaded the scripts.
Do one of the following:
For Oracle and DB2 databases, enter the following command:
arch_by_bpname user password database name
For Sybase and SQL Server databases, enter the following command:
arch_by_bpname user password server database name
Where name is the Business Process name to archive. For example, if you specify BusinessProcess1, then all instances of BusinessProcess1 are archived.
The following scripts are provided to archive and purge records based on the specified application. The UNIX versions of these scripts have a .sh extension.
purge_by_appname.cmd - Purges the history data by the specified application name.
arch_by_appname.cmd - Archives the history data by the specified application name.
Open a command window and navigate to the directory where you downloaded the scripts.
Do one of the following:
For Oracle and DB2 databases, enter the following command:
purge_by_appname user password database name
For Sybase and SQL Server databases, enter the following command:
purge_by_appname user password server database name
Where name is the application name to purge. For example, if you specify Application1, then all instances of Application1 data are purged.
Open a command window and navigate to the directory where you downloaded the scripts.
Do one of the following:
For Oracle and DB2 databases, enter the following command:
arch_by_appname user password database name
For Sybase and SQL Server databases, enter the following command:
arch_by_appname user password server database name
Where days is the application name to archive. For example, if you specify Application1, then all instances of Application1 data are archived.