test

Using the BPEL Designer and Service Engine

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.

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

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

Note –

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.