BPEL SE Persistence (for Recovery) Configurations

BPEL-SE uses the JDBC resource created in the appserver to get the database connection required for persistence.
Please follow the steps below to create appserver database connection pool and resource and configure BPEL-SE with persistence.
Currently BPEL-SE supports only Derby, Oracle and MySQL.

Setting the GlassFish JVM Classpath to the Database Drivers

The database JDBC drivers must be either set in the GlassFish JVM classpath (preferred), or placed in the local /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, your GlassFish JVM classpath must be set to the ojdbc6.jar file, which is the Oracle database JDBC driver for JVM6.

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.
   3. Login to the Admin Console. The default username is admin, and password is adminadmin.
2. To access the GlassFish JVM classpath settings, do the following:
   1. From the Common Tasks window, click Application Server.
   2. From the Application Server window, click the JVM Settings tab and 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.

Adding the Database Drivers in a Clustered Environment

Additional steps must be taken to add the JDBC database drivers in a clustered environment. For more information see Configuring the BPEL Service Engine for Clustering.

Configure the User and Database

1. Derby
While installing BPEL-SE component on the configuration screen set PersistenceEnabled to true,it creates connection pools, data sources and sets up database. No manual activity is required, this feature is only available to Derby.

If BPEL-SE component is already installed, modify PersistenceEnabled to true on the configuration screen, it is available in Netbeans Services tab (or admin-gui). Shutdown and restart the component, it would create connection pools, data sources and sets up database. This is equivalent of setting the PersistenceEnabled to true during the installation.

Either approach it is possible to change the data source name(s) by changing them in the configuration screen before installation and shutdown-start.

In case your prefer to go through the steps: You don't need to explicitly create a user and database for Derby. Just make sure that when you create the connection pools to include the 'create' flag in the database name and set it to 'true'.
DatabaseName -> bpelseDB;create=true

2. Oracle
Create the database user and tablespace
You need to create user with required priveliges and tablespace for BPEL SE Persistence. You need to login to oracle as sysdba (For example, from command prompt enter: sqlplus "sys/syspassword@tnsentry as sysdba"; you need to have sqlplus in the path and tns entry for the oracle database) and execute the following script with default values. 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, size/quota as per your choice and requirements.
Click here to download the scripts.

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;

3. MySQL
Create the database user and database
You need to create user with required priveliges and database for BPEL SE Persistence. You need to login to MySQL as root (For example, from command prompt enter: mysql -h host -u root -p (you can omit the host if connecting from the same machine); you need to have MySQL bin folder in the path) and execute the following script with default values. The user can also connect to the database from Netbeans or other sql clients as 'root'.
Click here to download the scripts.

CREATE DATABASE bpelse_user_db;
GRANT ALL ON bpelse_user_db.* TO 'bpelse_user'@'%' IDENTIFIED BY 'bpelse_user';

Setting max_allowed_packet
When a MySQL client or the mysqld server gets a packet bigger than max_allowed_packet bytes, it issues a Packet too large error and closes the connection. This value by default is small and you must increase this value if you are going to use large messages. This parameter should be as big as the biggest message you want to use.

Open the 'my.ini' file under the MySQL server install directory. Search for the 'max_allowed_packet' parameter (If there is none add the parameter to the file). Set the desired value. Restart the MySQL Server.
Eg: To set the max_allowed_packet to 1G you can use one of the following ways.
max_allowed_packet=1073741824
max_allowed_packet=1G
Information on setting 'max_allowed_packet'
Using Options to Set Program Variables

IMPORTANT MySQL Connector/J 5.1.6 bug
The current version of the official JDBC driver for MySQL, MySQL Connector/J, version 5.1.6 has a bug which causes errors when XA connections are used. As such using this driver will cause fatal exceptions in the BPEL-SE. The bug has been fixed and a patch is available. This fix will most probably be included in the next release of MySQL Connector/J. Until a version with the fix is released, please use the patched JDBC Driver for MySQL. You can read the bug description here.
Download the patched MySQL Connector/J

Create an XA Connection Pool and a JDBC resource

Note: Before you create Connection Pools, you must first set the GlassFish JVM classpath for your database JDBC drivers. See Setting the JVM Classpath to the Database Drivers above for more information.

• Login to appserver admin console.
• Create a Connection Pool by navigating to Resources -> JDBC -> Connection Pools.
  • Click on New. Specify a name (say bpelseXAPool), Resource Type as javax.sql.XADataSource and select the appropriate database vendor (Derby, Oracle or MySQL). Click next.
  • In the Connection Validation section click on the "Allow Non Component Callers" box to enable the property.
  • Specify the following additional database properties. If you did not use the default values Make sure that you use the user, password and database name that you chose earlier when you created the database (User and Password are case-sensitive).
    • Derby:
      • ServerName -> machine-name
      • 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
  • Click Finish. This will create new user and database.
  • Ping and verify that the connection pool creation was successfully. You can do this by clicking on the connection pool name and then clicking on "Ping".
  • Click Finish.
• Create a new JDBC resource by navigating to Resources -> JDBC -> JDBC Resources. Give JNDI Name (say jdbc/bpelseXA) and use the connection pool (bplseDBXA) that was created above. The JNDI name given to the resource will be used to configure BPEL Service Engine in step 4.
• Configure Transaction Service.
  • While logged on to Appserver Admin console navigate to: Configurations -> Transaction Service (or Configurations -> server-config -> Transaction Service if clustering is configured).
  • Enable automatic recovery by checking the box labeled Enabled next to the On Restart parameter.

Create a non XA connection pool and a JDBC resource

• Create a Connection Pool by navigating to Resources -> JDBC -> Connection Pools.
  • Click on New. Specify a name (say bpelseNonXAPool), Resource Type as javax.sql.ConnectionPoolDataSource and select the appropriate database vendor (Derby, Oracle or MySQL). Click next.
  • In the Connection Validation section click on the "Allow Non Component Callers" box to enable the property.
  • Specify the following additional database properties. If you did not use the default values Make sure that you use the user, password and database name that you chose earlier when you created the database.
    • Derby:
      • ServerName -> machine-name
      • 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
  • Click Finish. This will create new user and database.
  • Ping and verify that the connection pool creation was sucessfuly. You can do this by clicking on the connection pool name and then clicking on "Ping".
  • Click Finish.
• Create a new JDBC resource by navigating to Resources -> JDBC -> JDBC Resources. Give JNDI Name (say jdbc/bpelseNonXA) and use the connection pool (bplseDB) that was created above. The JNDI name given to the resource will be used to configure BPEL Service Engine in step 4.

Enable persistence in BPEL-SE

• After installing the BPEL-SE, modify the following properties from Netbeans Services tab to enable persistence. Refer screen-shot above.
  
  • PersistenceEnabled - select true
  • DatabaseJNDIName - specify the JNDI name of the JDBC resource that were created above
    
• Stop, Shutdown and Start the BPEL-SE
  

Truncating and Dropping tables

1. Drop and Truncating tables scripts. Click here to download the scripts
   

Also refer how to delete from UI

1. To drop oracle user, you can use the following script. You can also download from here
   

DROP TABLESPACE BPELSE_USER_DB INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

Additional information

1. When the BPEL-SE is started, it queries the database for the existence of the tables required for persistence. If they are not there then BPEL-SE will create the required tables.
2. Some of the instructions mentioned here may be changed, so please check back for updates or contact the BPEL-SE team if you have questions.

This page (revision-41) was last changed on 19-Nov-09 15:18 PM, -0800 by malkit.
This page was created on 09-Apr-07 12:10 PM, -0700 by malkit.

More info...