Configuring Persistence for the BPEL Service Engine

In order to ensure the integrity of your business process data in the case of a system failure, you can configure the BPEL Service Engine to persist business process data to a database. With BPEL Persistence enabled, the BPEL Service Engine recovers business process data that would otherwise be lost, and continues processing from the point of system failure.

The BPEL Service Engine supports MySQL, Derby (JavaDB), and Oracle. This section describes how to configure the BPEL Service Engine, the user, and the database, to persist data.

This section includes the following topics:

• Setting the JVM Classpath to the Database JDBC Drivers

• Configuring the User and Database for Persistence

• Creating an XA Connection Pool and a JDBC Resource

• Creating a Non-XA Connection Pool and JDBC Resource

• Enabling Persistence for the BPEL Service Engine

• Truncating and Dropping Tables

Setting the JVM Classpath to the Database JDBC Drivers

The database JDBC drivers must be either set in the GlassFish JVM classpath (preferred) or placed in the /glassfish/lib directory before you create the connection pools. This is not necessary for the Derby (JavaDB) database, but is required for Oracle or MySQL. For example, if you are using JDK 6, you would set your GlassFish JVM classpath to the ojdbc6.jar file, which is the Oracle database JDBC driver file for JVM 6.

To Set the GlassFish JVM Classpath Settings

1. Open the GlassFish Admin Console. To access the Admin Console, do the following:

   1. From the Services window of the NetBeans IDE, start the GlassFish server.

   2. Right-click the GlassFish server and select View Admin Console from the popup menu.

      The login screen of the Admin Console appears.

   3. Open the Admin Console using the default username and password:

      • Username: admin

      • Password: adminadmin

2. To access the GlassFish JVM classpath settings:

   1. From the Admin Console's Common Tasks window, click Application Server.

      The Application Server settings appear in the console window to the right.

   2. Click the JVM Settings tab and the Path Settings sub-tab.

3. In the Classpath Suffix field, type the full path for your database JDBC driver on your local directory. For example, C:\GlassFishESB\drivers\oracle11gDriver\ojdbc6.jar.

4. Click Save, and restart GlassFish.

Setting the JVM Classpath for GlassFish Clustering

Additional steps must be taken when setting the GlassFish JVM Classpath to the Database drivers in a clustered environment. For additional information see Configuring the BPEL Service Engine for Clustering in Configuring GlassFish ESB for Clustering.

Configuring the User and Database for Persistence

This section describes how to configure an appropriate user and database, to persist data.

Derby (JavaDB)

The BPEL Service Engine uses the JDBC resource created in the Application Server to make the necessary database connection for persistence. NetBeans is bundled with the JavaDB database, which is the Sun's supported distribution of the open source Apache Derby database. The BPEL Service Engine is configured to connect to the JavaDB database by default.

It is not necessary to create a user and database for Derby. Just create the connection pools and include the "create" flag in the database name, and set the value to "true". For example: DatabaseName -> bpelseDB;create=true.

Oracle

Create the Oracle database user with required privileges and tablespace for BPEL Service Engine persistence.

To Create the Oracle Database User

1. Log into Oracle as sysdba. For example, from the command prompt enter: sqlplus "sys/syspassword@tnsentry as sysdba". You need to include sqlplus in the path and the tns entry for the Oracle database.

2. Execute the following script using the default values:

   CREATE TABLESPACE bpelsedb DATAFILE 'bpelsedb.dat' SIZE 512M REUSE AUTOEXTEND ON NEXT 2048M MAXSIZE UNLIMITED; CREATE USER bpelse_user IDENTIFIED BY bpelse_user DEFAULT TABLESPACE bpelsedb QUOTA UNLIMITED ON bpelsedb TEMPORARY TABLESPACE temp QUOTA 0M ON system; GRANT CREATE session to bpelse_user; GRANT CREATE table to bpelse_user; GRANT CREATE procedure to bpelse_user; GRANT select on sys.dba_pending_transactions to bpelse_user; GRANT select on sys.pending_trans$ to bpelse_user; GRANT select on sys.dba_2pc_pending to bpelse_user; GRANT execute on sys.dbms_system to bpelse_user; GRANT select on SYS.dba_2pc_neighbors to bpelse_user; GRANT force any transaction to bpelse_user;

   Click Here, for more information and to download the latest scripts for Oracle.

   ---

   Note –

   The above code is wrapped for display purposes.

   ---

   ---

   Tip –

   The user can also connect to the database from NetBeans or other SQL clients by giving the username "sys as sysdba". The password should be the same as that of the system user. You can also change the user, tablespace and datafile name, and size/quota, as per your choice and requirements.

   ---

MySQL

Create the MySQL database user with required privileges and database for BPEL Service Engine persistence.

To Create the MySQL Database User

1. Log into MySQL as root. For example, from the command prompt enter: mysql -h host -u root -p.

   You need to include the MySQL bin folder in the path. You can omit the host if you are connecting from the same machine.

2. Execute the following script using the default values:

   CREATE DATABASE BPELSE_USER_DB; GRANT ALL ON BPELSE_USER_DB.* TO 'BPELSE_USER'@'%' IDENTIFIED BY 'BPELSE_USER';

   Click Here, for more information and to download the scripts for MySQL.

Setting max_allowed_packet

When a MySQL client or the mySQL server gets a packet that is larger than the max_allowed_packet bytes, it issues a "Packet too large" error and closes the connection. By default this value is configured low. You must increase this value for large messages. Set the value of this parameter to the size of the biggest message you anticipate.

To set max_allowed_packet:

1. Open the "my.ini" file under the MySQL server install directory.

2. Search for the "max_allowed_packet" parameter. If the file does not have it, add the parameter to the file.

3. Set the value as needed. To set the value to 1GB, enter the value as one of the following:max_allowed_packet=1073741824 max_allowed_packet=1G

4. Restart the MySQL Server.

For more information see Setting max_allowed_packet and Using Options to Set Program Variables.

The current version of the JDBC driver for MySQL, MySQL Connector/J, version 5.1.6, has a bug which can cause errors when an XA connection is used. As such, using this driver can cause fatal exceptions in the BPEL Service Engine. A patch for MySQL Connector/J version 5.1.6, is available, and will be included in subsequent releases of MySQL Connector/J. For more information on the patch, see http://bugs.mysql.com/bug.php?id=35489.

Creating an XA Connection Pool and a JDBC Resource

Before you setup and test the Connection Pools, you must first set the GlassFish JVM classpath for your database JDBC drivers. See Setting the JVM Classpath to the Database JDBC Drivers for more information.

To Create an XA Connection Pool

1. Log into the GlassFish Admin Console. To do this, right-click your application server node, in the Services window under Servers, and choose View Admin Console.

   You can also open the Admin Console from your web browser using the correct URL. For example: http://localhost:4848.

2. In the navigation tree, expand the following nodes: Resources -> JDBC.

3. Select Connection Pools, and in the right panel, click the New button.

4. Under General Settings, specify a name (such as bpelseDBXA).

5. Set the Resource Type to javax.sql.XADataSource.

6. Set the database vendor to the appropriate database vendor (Derby, Oracle or MySQL). Click next.

   

7. From the New JDBC Connection Pool (Step 2 of 2) window, under Connection Validation, enable Allow Non Component Callers.

8. Under Additional Properties, specify the following properties for your database:

   • Derby:

     • ServerName -> machine-name

     • DatabaseName -> DatabaseName: bpelseDB;create=true

     • User -> bpelse_user

     • Password -> bpelse_user

   • Oracle:

     • URL -> jdbc:oracle:thin:@machine-name:port:sid

     • User -> bpelse_user

     • Password -> bpelse_user

   • MySQL:

     • URL -> jdbc:mysql://machine-name:port/databasename

     • User -> bpelse_user

     • Password -> bpelse_user

   If you did not use the default values, make sure that the ServerName, User, Password and DatabaseName values are those that you specified when you created the database.

9. Click Finish. The new user and database are created.

10. Click the connection pool name and click the Ping button to verify your database connection.

11. Click Finish.

Create a New JDBC Resource

1. From the Admin Console navigation tree, expand the Resources -> JDBC nodes, and select JDBC Resources.

2. In the right panel, click the New button.

3. Provide a JNDI name (such as jdbc/bpelseDB) and specify the JDBC Connection Pool (bplseDB) you created previously. You will use this JNDI name later when you enable persistence in the BPEL Service Engine properties.

4. Expand the Configuration node and select Transaction Service (or Configuration -> server-config -> Transaction Service, if clustering is configured).

5. For the On Restart parameter, enable Automatic Recovery by selecting the Enabled check box.

Creating a Non-XA Connection Pool and JDBC Resource

To create a non-XA Connection Pool, follow the same procedures used to create an XA Connection Pool and JDBC Resource, but substitute bpelseDB as the name, and javax.sql.ConnectionPoolDataSource as the Resource Type.

Before you setup and test the Connection Pools, you must first set the GlassFish JVM classpath for your database JDBC drivers. See Setting the JVM Classpath to the Database JDBC Drivers for more information.

Enabling Persistence for the BPEL Service Engine

Persistence is configured in the BPEL Service Engine runtime properties.

To Enable Persistence for the BPEL Service Engine

1. In the NetBeans IDE Services window, expand the Sun Java System Application Server (GlassFish) -> JBI -> Service Engines Nodes.

2. Right-click sun-bpel-engine and select Properties.

   The sun-bpel-engine Properties window appears.

3. Set the PersistenceEnabled property to true.

4. Set the Non XA Data Source Name property to specify the JNDIName of the non-XA JDBC resource that you created when you configured the database.

5. Set the XA Data Source Name property to specify the JNDIName of the XA JDBC resource that you created when you configured the database.

   

6. Click Close to save your settings.

   See Configuring the BPEL Service Engine Runtime Properties for property descriptions.

7. Stop, shut down, and start the BPEL Service Engine to enable your new settings.

Truncating and Dropping Tables

The following notes provide drop and truncate scripts as well as additional information about configuring persistence. Some of the instructions mentioned here may change, so check back for updates or contact the BPEL Service Engine team if you have questions.

When the BPEL Service Engine is started, it queries the database for the existence of the tables required for persistence. If these tables are not available, the BPEL Service Engine will create the required tables.

Drop and Truncate Scripts

To download the scripts, click Drop and Truncating Table Scripts. You can also refer to How to Delete From UI for more information.

For the script to drop an Oracle user, use the following: DROP TABLESPACE BPELSE_USER_DB INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;