Database Connector | How to configure pooling Datasource using Bitronix Transaction Manager

Pre-requisites:

1. Refer to this MuleSoft documentation for a brief understanding of the Bitronix Transaction Manager. 
2. Before configuring the pooling datasource, you must Add the Bitronix Transaction manager from your Anypoint Studio and configure Bitronix properties as necessary.
3. The steps below are based on the MuleSoft Documentation for Configuring a Pooling Data Source for Databases and Configure a Data Source Reference Connection When in XA Transaction Mode but with more detailed configuration steps and an example Mule app.

Steps:

1. Create a spring bean config file similar to below. Note that the class must be provided based on the Database used. 

<bean id="mssqlXADataSource" name="mssqlXADataSource" class="com.microsoft.sqlserver.jdbc.SQLServerXADataSource">
<property name="serverName" value="?" />
<property name="user" value="?" />
<property name="password" value="?" />
<property name="databaseName" value="?" />
</bean>

2. In the Global configuration file, add Bitronix transaction manager pooling similar to the one below. Use the necessary pooling properties as shown in this documentation: Configuring a Pooling Data Source for Databases. Please note that the below is only an example and the properties must be adjusted 

<bti:xa-data-source-pool name="mssqlBitronixDataSourcePool" acquireIncrement="1" acquireTimeoutSeconds="60" dataSource-ref="mssqlXADataSource" maxIdleTime="60" maxPoolSize="16" minPoolSize="0" preparedStatementCacheSize="5"/>

3. Update DB config to point to the above Bitronix transaction pool.

<db:config doc:name="mssql XA Database Config" name="DB_Config_mssql">
<db:data-source-connection dataSourceRef="mssqlBitronixDataSourcePool">
</db:data-source-connection>
</db:config>

4. You can download the Mule app with these configurations in the "Attachments" section below.

** Generic XAPool class "org.enhydra.jdbc.standard.StandardXADataSource" must be used with compatible Databases only and this might not work for all Databases. Please verify your official Database documentation before using this. This is tested and works with Snowflake by using the dependency com.experlog.xapool and the "driverName" property set to net.snowflake.client.jdbc.SnowflakeDriver in the Spring bean.

Disclaimer: The example is provided as a reference for your own usage and it's not part of the official Mule product so its use will be considered as a custom implementation made by the customer.