In this post I am going to guide you through the process of configuring external database/data source as the WSO2 BPS BPEL engine’s(Apache ODE) persistence storage. This will help you to improve the performance of BPS when compared with the embedded Derby database.

Let’s start with the Database

WSO2 BPS 1.0.1 support Micrsoft SQL Server and Oracle Database Server. Database schemas for MS SQL Server and Oracle can be found in the BPS 1.0.1 binary package inside ‘dbscripts’ directory.

1. Log in to your database server management console
2. Create database to use with WSO2 BPS
3. Load the schema into the database you have created using provided SQL scripts.

Once you have finished database creation, you have to configure a data source in WSO2 BPS to use it with BPEL engine. You must define your data source in datasources.properties file located at BPS_HOME/conf directory. Data source support is developed using Apache dbcp. WSO2 BPS data sources support all the parameters mentioned in common dbcp parameters document from Apache commons site. You must follow the synapse.datasources.<data source name>.<parameter>=<parameter value> pattern when specifying parameters for the data source. You can use any of the parameters supported by Apache dbcp to replace ‘parameters’ field.

Let’s create the data source configuration

1. Create file named ‘datasources.properties’ inside conf directory located under the WSO2 BPS root directory(assuming fresh start).
2. First specify data source name, initial context factory and provider port. Here is an example which creates only one data source named ‘bpsds’.

   synapse.datasources=bpsds
   synapse.datasources.icFactory=com.sun.jndi.rmi.registry.RegistryContextFactory
   synapse.datasources.providerPort=2199

3. Then specify the properties for previously defined data sources. In this case we have only one data source named ‘bpsds’ and here is the completed datasource.properties file for MS SQL server database called ‘bps’ with the username ‘sa’ and password ‘root123′. In this particular configuration we used jTDS database driver. You have to specify driverClassName, url, username, password and dsName properties for ‘bpsds’ data source according to the JDBC driver you are using and Database server configuration you are using.

   synapse.datasources=bpsds
   synapse.datasources.icFactory=com.sun.jndi.rmi.registry.RegistryContextFactory
   synapse.datasources.providerPort=2199
   
   synapse.datasources.bpsds.registry=JNDI
   synapse.datasources.bpsds.type=BasicDataSource
   synapse.datasources.bpsds.driverClassName=net.sourceforge.jtds.jdbc.Driver
   synapse.datasources.bpsds.url=jdbc:jtds:sqlserver://192.168.1.2:1433/bps
   synapse.datasources.bpsds.username=sa
   synapse.datasources.bpsds.password=root123
   synapse.datasources.bpsds.dsName=bpsds
   synapse.datasources.bpsds.maxActive=100
   synapse.datasources.bpsds.maxIdle=20
   synapse.datasources.bpsds.maxWait=10000

4. Once you done with the datasources.properties file, copy the JDBC driver jar file into the ‘lib/extensions’ directory under WSO2 BPS root directory.

After you configure the data source properly you have to configure BPEL engine to use the data source you have created. For that you have to create ‘conf’ directory under BPS_HOME/repository directory and create a file called ode-axis2.properties inside it.

Let’s configure BPEL engine to use the data source

1. First add the following properties to configure Hibernate data access layer. You can use ‘hibernate.dialect’ property to specify the Database type. For MS SQL Server use “org.hibernate.dialect.SQLServerDialect”, and for Oracle use “hibernate.dialect=org.hibernate.dialect.Oracle9Dialect”. Other properties are not changed with the Database.

   # Hibernate Configuration
   hibernate.dialect=org.hibernate.dialect.SQLServerDialect
   hibernate.hbm2ddl.auto = update
   hibernate.current_session_context_class=jta
   hibernate.transaction.manager_lookup_class=org.apache.ode.daohib.HibernateTransactionManagerLookup

2. Then you need to configure ODE to use the data source. Here is an example of ODE configuration. For the jndi provider url you have to use rmi://<host name>:<port specified earlier>.

   # ODE Configuration
   ode-axis2.db.mode=EXTERNAL   # Database mode property specify whther to use embedded db or external.
   ode-axis2.db.ext.dataSource=bpsds # Previously created data source name.
   ode-axis2.jndi.context.factory=com.sun.jndi.rmi.registry.RegistryContextFactory # Initial context factory.
   ode-axis2.jndi.provider.url=rmi://milinda-laptop:2199

3. Completed ode-axis2.properties file will look like following.

   # Hibernate Configuration
   hibernate.dialect=org.hibernate.dialect.SQLServerDialect
   hibernate.hbm2ddl.auto = update
   hibernate.current_session_context_class=jta
   hibernate.transaction.manager_lookup_class=org.apache.ode.daohib.HibernateTransactionManagerLookup 
   
   # ODE Configuration
   ode-axis2.db.mode=EXTERNAL   # Database mode property specify whther to use embedded db or external.
   ode-axis2.db.ext.dataSource=bpsds # Previously created data source name.
   ode-axis2.jndi.context.factory=com.sun.jndi.rmi.registry.RegistryContextFactory # Initial context factory.
   ode-axis2.jndi.provider.url=rmi://milinda-laptop:2199

After you are done with configuration start the WSO2 BPS. Make sure that you have put the JDBC driver JAR into the BPS_HOME/lib/extensions directory and all the configurations are correct. If there are any errors with your data source configurations, BPS will show them in the console when you try to start.