setting up JDBC connection pool in Talend ESB container

[Anonymous]

Hi,

I'm trying to setup my REST services jobs to use JDBC connection pooling when deployed on Talend ESB runtime (Karaf).

I've followed the instructions in the documentation guide here

From the docs I've run the following:

1. place the SQL Server JDBC driver in ./runtime/deploy/sqljdbc42.jar
2. feature:install tesb-datasource-sqlserver
3. feature:list | grep sqlserver  
   tesb-datasource-sqlserver | 6.2.1 | x | Started | tesb-6.2.1 |
4. edited ./runtime/etc/org.talend.esb.datasource.sqlserver.cfg 
   dataSource.url=jdbc:sqlserver://MYHOST:1433;databaseName=MYDB
   dataSource.user=MYUSER
   dataSource.password=MYPASS
   datasource.pool.maxActive=20
   datasource.pool.maxIdle=5
   datasource.pool.maxWait=30000
5. Actually I'm not sure where the JDBC Driver class name is set above! It should be "com.microsoft.sqlserver.jdbc.SQLServerDriver". Anyway!
6. bundle:list | grep MSSQL
   355 | Active | 80 | 0.8.0 | OPS4J Pax JDBC MSSQL Driver Adapter
7. bundle:refresh 355
8. bundle:restart 355

Now, I have a job that sets the data source alias on my MSSQLServerConnection:

But when I deploy my job, the bundle stays in "GracePeriod" state. 

When I look at the logs I see that the job is waiting on a dependency with the following error message:

2017-05-04 18:25:59,919 | INFO | ool-491-thread-1 | BlueprintContainerImpl | 16 - org.apache.aries.blueprint.core - 1.6.1 | Bundle demo.rest1/0.5.0.SNAPSHOT is waiting for dependencies [(&(osgi.jndi.service.name=ds-sqlserver)(objectClass=javax.sql.DataSource))] 

What's the dependency feature that needs to be started??

I'm feeling Karaf has not started the ds-sqlserver connection pool!!!

[Anonymous]

Follow up question... how do you set up multiple connection pools? for example how do you create a new or change "ds-sqlserver" pool?

[Anonymous]

Follow up question... how do you set up multiple connection pools? for example how do you create a new or change "ds-sqlserver" pool?

[vharcq · Accepted solution]

I do that not using tesb but pure Camel commands :

Install the Camel JDBC feature

feature:install jdbc

Create a Datasource

 jdbc:ds-create -url jdbc:oracle:thin:@//hostname:1521/dbname -u login -p password -dc oracle.jdbc.OracleDriver jdbc/sample

Check you can list it

jdbc:ds-list

Even test some SQL

jdbc:query jdbc/sample select * from tablename

[Anonymous]

Thanks so much for your reply @vharcq. I actually got another solution as well but forgot to post it here.

Here's the other solution I found:

I had to drop a Spring Bean XML config file into the Karaf deploy directory. Here's a sample Spring Bean configuration for MySQL but it can easily be modified for SQL Server.

Edit this file based on your JDBC connection URL and drop it into the deploy directory of Karaf; afterwards you can use the connection name as the "alias" in your t****Connection component.

<?xml version="1.0" encoding="UTF-8"?>

<beans xmlns="http://www.springframework.org/schema/beans"
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
  xmlns:osgi="http://www.springframework.org/schema/osgi"
  xmlns:context="http://www.springframework.org/schema/context"
   xsi:schemaLocation="http://www.springframework.org/schema/beans
   http://www.springframework.org/schema/beans/spring-beans-3.0.xsd
   http://www.springframework.org/schema/osgi http://www.springframework.org/schema/osgi/spring-osgi.xsd
   http://www.springframework.org/schema/context http://www.springframework.org/schema/context/spring-context.xsd">
  
	<!-- The Connection Pool -->
	<bean id="mysql-ds" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
		<property name="driverClassName" value="com.mysql.jdbc.Driver"/>
		<property name="url" value="jdbc:mysql://localhost/demo"/>
		<property name="username" value="talend"/>
		<property name="password" value="talend"/>
		<property name="initialSize" value="4"/>
		<property name="maxActive"  value="30"/>
		<property name="maxIdle" value="10"/>
		<property name="maxWait" value="3000"/>
		<property name="validationQuery" value="SELECT 1"/>
	</bean>

	<!-- Publish the pool as an OSGi and JNDI Service -->
	<osgi:service interface="javax.sql.DataSource" ref="mysql-ds">
		<osgi:service-properties>
			<entry key="osgi.jndi.service.name" value="mysql-ds"/>
		</osgi:service-properties>
	</osgi:service>
</beans>

[Anonymous]

The best way to create multiple connection pools is to use pax-jdbc-pool-dbcp2, do the following, ensure talend is running:

1. create a datapool configuration file under runtimePath/container/etc/org.ops4j.datasource-db_your_data_source_name.cfg
2. Update the file accordingly: 

   1. osgi.jdbc.driver.name=mysql
      pool=dbcp2
      xa=true
      databaseName=your_database_name
      user=your_database_userName
      password=your_database_username_password
      dataSourceName=your_data_source_name
      jdbc.pool.maxTotal=8

   2. note in this approach you can name your data source anything and not for example "ds-mysql"
3. Note in the tesb.log that the file has been loaded 

   2019-01-10T18:16:00,636 | INFO  | fileinstall-XXXXX/TOS_ESB-20180116_1512-V6.5.1/Runtime_ESBSE/container/etc | install.internal.Util$OsgiLogger  205 | 8 - org.apache.felix.fileinstall - 3.5.8 | Creating configuration from org.ops4j.datasource-db_your_data_source_name.cfg

4. go into the talend client i.e ./client in the container/bin folder
5. execute the following commands:

   feature:repo-add mvn:org.ops4j.pax.jdbc/pax-jdbc-features/1.0.0/xml/features
   feature:install transaction jndi pax-jdbc-mysql pax-jdbc-config pax-jdbc-pool-dbcp2

   1. in the above I am using mysql "pax-jdbc-mysql" should you require another database the following options can be used:
      1. pax-jdbc-oracle
         pax-jdbc-db2
         pax-jdbc-derby
         pax-jdbc-derbyclient
         pax-jdbc-h2
         pax-jdbc-hsqldb
         pax-jdbc-jtds
         pax-jdbc-mariadb
         pax-jdbc-mssql
         pax-jdbc-mysql
         pax-jdbc-postgresql
         pax-jdbc-sqlite
         pax-jdbc-teradata
6. Confirm that the datasource has been created by executing  service:list javax.sql.DataSource

   [javax.sql.DataSource]
   ----------------------
    service.factoryPid = org.ops4j.datasource
    felix.fileinstall.filename = file:/XXX/Runtime_ESBSE/container/etc/org.ops4j.datasource-db_your_data_source_name.cfg
    service.pid = org.ops4j.datasource.9d02b595-6315-49f3-9ed4-17537e99b126
    Jdbc.pool.maxTotal = 8
    password = password
    databaseName = databaseName
    user = user
    osgi.jndi.service.name = your_data_source_name
    dataSourceName = your_data_source_name
    osgi.jdbc.driver.name = mysql
    service.id = 491
    service.bundleid = 271
    service.scope = singleton
   Provided by : 
    OPS4J Pax JDBC Config (271)

Should you wish to create a second connection pool merely create a new configuration file to each database you are trying to connect to, ensure that the osgi.jdbc.driver.name is set to the correct database. Be sure to set your alias name in your db connector in the studio to what the dataSourceName name is in your configuration file.

NOTE

The instructions for creating an oracle connection are somewhat different, Since the oracle driver is neither in maven central or an osgi bundle. Before executing pax-jdbc-oracle in step 5 you will need to perform the following:

1. You will need to download the ojdbc oracle Jar and copy it to you deployment environment
2. execute the following command:
   

   bundle:install wrap:file:/path_to_download/ojdbc-7.jar\$Bundle-SymbolicName=oracle.jdbc&Bundle-Version=1.0&Bundle-Name='JDBC Driver for Oracle'

3. The above command is tricky to get right since if you have made an error you will not get anything in the logs. Execute the list command in the talend client in order to determine if the driver has been installed.
   
   
4. Once this is complete you can then install the pax driver

   feature:install pax-jdbc-oracle

[Anonymous]

Thanks @Brian_Johnson . Your instruction helped me a lot.

I'm using Oracle 12c and Talend 7.0.1.

I followed your instruction and had some problems with retrieving the connection from the pool.

When I used default configuration the pool was returning dbcp2.ManagedConnection and app was failing because it couldn't be cast to OracleConnection.

Started working for me when I configured pool to use dbcp2.BasicDataSource class without XA and specified jdbc driver class:

runtime/etc/org.ops4j.datasource-ds-oracle.cfg

pool=dbcp2
dataSource.class=org.apache.commons.dbcp2.BasicDataSource
dataSource.accessToUnderlyingConnectionAllowed=true
osgi.jdbc.driver.class=oracle.jdbc.OracleDriver
url=jdbc:oracle:thin:@HOST:PORT:SID
user=USER
password=PASSWORD
dataSourceName=ds-oracle
initialSize=2
jdbc.pool.maxTotal=20
jdbc.pool.maxIdle=10
jdbc.pool.minIdle=1
jdbc.pool.maxWaitMillis=10000

I hope it helps someone. Cheers

[Anonymous]

Good Afternoon Vald

My apologies I should have put an example of an oracle connection. Below is the one I used. You need to set the correct driver with xa = true in this case it is osgi.jdbc.driver.name=oracle

osgi.jdbc.driver.name=oracle
pool=dbcp2
xa=true
url=jdbc:oracle:thin:@HOST:PORT:SID&allowMultiQueries=true
databaseName=database_name
user=database_user
password=database_password
dataSourceName=db_C_P_Oracle_PAS
Jdbc.pool.removeAbandonedOnBorrow=true
Jdbc.pool.removeAbandonedTimeout=60
Jdbc.pool.defaultAutoCommit=true
Jdbc.pool.logAbandoned=true
pool.maxTotal=150
pool.minIdle=20
pool.maxIdle=50
pool.maxWaitMillis=10000
pool.minEvictableIdleTimeMillis=5000
pool.testOnBorrow=true
pool.testOnReturn=false
pool.timeBetweenEvictionRunsMillis=5000
Jdbc.factory.validationQuery=select 1
Jdbc.factory.defaultTransactionIsolation=2

Also here is a useful query in the talend client that you might be interested in after creating your connection pool

 jdbc:ds-list 

[Anonymous]

Hi @Brian_Johnson 

Thank you for sharing that config file. I will configure abandoned connections handling with my config same way you did.

I performed few more tests to turn on XA. Seems like XA works only with osgi.jdbc.driver.name=oracle .

I'm able to create the datasource with the config you provided and it works in karaf, but I can't get the connection in talend job.

if I use osgi.jdbc.driver.name=oracle and xa=true, the pool creates but the job errors out when trying to get the connection with message: 

java.lang.ClassCastException: org.apache.commons.dbcp2.managed.ManagedConnection cannot be cast to oracle.jdbc.OracleConnection
at pool_test_0_20.POOL_TEST.tDBInput_1Process(...)

if I use osgi.jdbc.driver.name=oracle without specifying xa  the job errors out with message: 

java.lang.ClassCastException: org.apache.commons.dbcp2.PoolingDataSource$PoolGuardConnectionWrapper cannot be cast to oracle.jdbc.OracleConnection

if I use osgi.jdbc.driver.class=oracle.jdbc.OracleDriver and xa=true  karaf fails to create datasource:

org.ops4j.pax.jdbc.config - 1.1.0 | not supported - use a driver adapter org.ops4j.pax.jdbc.<subprotocol>
java.sql.SQLException: not supported - use a driver adapter org.ops4j.pax.jdbc.<subprotocol>

when I use osgi.jdbc.driver.class=oracle.jdbc.OracleDriver without specifying xa  it works fine. 

Regarding checking the datasource, I can see my pool in jdbc:ds-list and jdbc:ds-info but it does not give much details. 

Mbeans in runtime JVM give much more details and you can see real time what is happening to the datasource (i.e see number of active and idle connections and some stats). I'm using ViasualVM + mbeans browser plugin for that. 

Did you have a chance to perform some load tests and see that you can run 150 active connections? Any issues observed?

Kind Regards,

Vald

[Anonymous]

Good Afternoon Vald

We are running a little older version of Talend than you are. I don't know if you tried newer versions of pax-jdbc-features, we run on version 1.0.0 and I just checked on maven central and I see they are now on version 1.4 so it might be something to look into. We have ran load tests on this and the connection speeds are good as well as the pool maintaining its size as connections are returned. Keep in mind you must close the connections inside your Talend Job or the connection will not be returned to the pool and you will get a pool leak. Additionally when load is dropped the pool size will drop accordingly based on your pool.minEvictableIdleTimeMillis setting. All in all we are very happy with its performance considering that we have 2 connection pools in one of our deployments, one to a MySQL db and another to an oracle db. We have some deployment where we have up to 3 connection pools and we have still not seen any degradation. 

That said there are more efficient connection pools supported in PAX JDBC. We really tried to get hikari working but unfortunately had to abandon this. As I'm sure you probably know documentation is very scarce in this arena so you have my sympathy. (So if you get that one to work would be very keen to see how)

Thank you kindly for sharing ViasualVM will definitely have a look at this.

Wishing you all the best in your endeavour here.