Creating the Monitoring and Recovery Database

To create the monitoring and recovery database tables, you can either modify and run database scripts from NetBeans 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:

• Creating the Database Instance

• Running the Scripts in NetBeans

• Running the Database Scripts from a Local Directory

Creating the Database Instance

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 and SQL Server, but requires additional steps for Sybase and DB2.

Creating the Database in Sybase

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.

Creating the Database in DB2

Using DB2 with Oracle Java CAPS 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 BPM6USERDB.

• 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 Oracle Java CAPS 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

Running the Scripts in NetBeans

From NetBeans, 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.

Configuring the Database Connection

Before you can run the database scripts, you need to configure the database connection information in the NetBeans Projects window. Configure the connection for the administrator user who has privileges to your database.

To Configure the Database Connection

1. In the NetBeans Projects window, expand CAPS Components Library, Business Process Manager, and then expand Run Database Scripts.
2. Right-click Run Database Scripts, click Check Out, and then click Check Out on the dialog box.
3. Right-click Database Scripts and then click Properties.
4. Enter the database configuration information described in Configuring the Database Connection.
5. Click OK.
   

   Property Description Database Type The database vendor and version you are using. JDBC URL The URL to connect with the database. Enter one of the following: For Oracle:: jdbc:SeeBeyond:oracle://host:port;SID=SID For Sybase: jdbc:SeeBeyond:sybase://host:port For SQL Server: jdbc:SeeBeyond:sqlserver://host:port;DatabaseName= dbname For DB2: jdbc:SeeBeyond:db2://host:port;DatabaseName=SID;collectionId=JDBCPKG;packageName=JDBCPKG;embedded=true;createDefaultPackage=FALSE where host is the machine on which the database resides, port is the port number on which the database is listening, and SID and dbname are the name of the database. User The login ID of a database administrator. This user must be able to create users and assign permissions, as well as create and drop tables. Password The password for the administrator user.

Viewing and Modifying Database Scripts in NetBeans

You can open the database scripts and view, modify, and run them within NetBeans. 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 of the created 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 Java CAPS BPM Engine to access the database.

To Modify a Database Script

1. Check out the files as described in Configuring the Database Connection.
2. Expand the Run Database Scripts folder.
3. Under Database Scripts, right-click the script you want to modify, and then click Open.
4. Make any other necessary changes to the script. Do not change any of the table or field names.
5. 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.
6. 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.

Running the Database Scripts

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 NetBeans. The connectivity information must also be configured for the scripts.

To Create the Database Tables

1. In the NetBeans Projects window, expand CAPS Components Library, Business Process Manager, and then expand Run Database Scripts.
2. Right-click the file associated with the appropriate database (Oracle Install, SQL Server Install, DB2 Install, or Sybase Install).
3. Click Run.

To Drop the Database Tables

1. In the NetBeans Projects window, expand CAPS Components Library, Business Process Manager, and then expand Run Database Scripts.
2. Right-click the file associated with the appropriate database (Oracle Uninstall, SQL Server Uninstall, DB2 Uninstall, or Sybase Uninstall).
3. Click Run.

Running the Database Scripts from a Local Directory

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 Sybase 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. You should review this file before running the scripts.

Downloading the Compressed Script Files

In order to work with the compressed script files, you need to export them to a local directory and then extract the compressed files.

To Download a Compressed Script File

1. In the NetBeans Projects window, expand CAPS Components Library, expand Business Process Manager, and then expand Download Database Scripts.
2. Right-click the file associated with the appropriate database (oracle.zip, sqlserver.zip, db2.zip, or sybase.zip).
3. Click Export.
4. Browse to a local folder to store the database files, and then click Save.
5. Navigate to the local folder and extract the compressed files.
6. Follow the instructions in the Readme.txt file provided in the downloaded ZIP file.

Running the Downloaded Scripts

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. Before you run the scripts, make sure you have reviewed the installation scripts the modified them for your environment. For more information, see Viewing and Modifying Database Scripts in NetBeans. Additional information for modifying these files is available in the Readme.txt file included in each ZIP file.

To Run a Downloaded Database Script

1. Open a command window and navigate to the directory where scripts are located.
2. 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.

     ---

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

     ---