Data Sources and the DB Adapter

I have done a little bit of work with a client recently that was experiencing some severe performance problems with their BPEL process, specifically in the DB adapter.  One of the things I wanted to try was adjusting the number of rows fetched by default in a select statement.  To do this I needed to set the "defaultRowPrefetch" preoperty on the JDBC connection.

Well this meant that I had to set up the DB adapter to use a container managed data source.  First I needed to set up the data source, then I needed to create a JCA resource that used this data source.

In my opinion it is a good idea to have this level of indirection because it means that your BPEL server can now have its database connections managed in the same way as all other J2EE applications (including the BPEL server when accessing its dehydration store).

Setting up the Data Source

I created a new data source by adding the following to my data-sources.xml file found in $ORACLE_HOME/integration/orabpel/system/appserver/oc4j/j2ee/home/config directory.  This makes the database connection known to the J2EE engine.

<data-source class="com.evermind.sql.DriverManagerDataSource"
     name="TestDataSource"
     location="jdbc/TestDS"
     xa-location="TestDataSourceXA"
     ejb-location="jdbc/TestDataSource"
     connection-driver="oracle.jdbc.OracleDriver"
     max-connections="150"
     min-connections="10"
     connection-retry-interval="30"
     max-connect-attempts="10"
     url="jdbc:oracle:thin:test/test@localhost:1521:AJR">
     <property name="defaultRowPrefetch" value="400"/>
</data-source>

Setting up the JCA Database Adapter

To set up the JCA adapter I added the following entry to the oc4j-ra.xml file found in $ORACLE_HOME/integration/orabpel/system/oc4j/j2ee/home/application-deployments/default/DbAdapter directory.  The Database Adapter actually uses TopLink but appears to BPEL PM as yet another JCA adapter.  Normally the details of the connection are contained in the WSDL binding for the DB Adapter, but this makes moving from development to test to porduction very messy, so they can be stored as part of the JCA configuration, or and this is my preference, indirected to an existing data source.

<connector-factory location="eis/DB/test" connector-name="Database Adapter">
    <config-property name="driverClassName" value="oracle.jdbc.driver.OracleDriver"/>
    <config-property name="usesExternalConnectionPooling" value="true"/>
    <config-property name="dataSourceName" value="jdbc/TestDS"/>
    <config-property name="usesExternalTransactionController" value="false"/>
    <config-property name="platformClassName" value="oracle.toplink.internal.databaseaccess.Oracle9Platform"/>
    <config-property name="usesNativeSequencing" value="true"/>
    <config-property name="sequencePreallocationSize" value="50"/>
    <config-property name="tableQualifier" value=""/>
    <config-property name="defaultNChar" value="false"/>
    <config-property name="usesBatchWriting" value="true"/>
</connector-factory>

Voila! Its done!

Once the above is done you should now be using the data source in data-sources.xml rather than the connection embedded in the WSDL.  To confirm that this is the case modify the username in the WSDL and verify that your proces still works - mine did!  now I need to get back to figuring out how to fix that darn slow running database query.