topic Re: setting up JDBC connection pool in Talend ESB container in Talend Studio

setting up JDBC connection pool in Talend ESB container

Anonymous — Sat, 16 Nov 2024 09:49:40 GMT

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:

place the SQL Server JDBC driver in ./runtime/deploy/sqljdbc42.jar
feature:install tesb-datasource-sqlserver
feature:list | grep sqlserver
tesb-datasource-sqlserver | 6.2.1 | x | Started | tesb-6.2.1 |
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
Actually I'm not sure where the JDBC Driver class name is set above! It should be "com.microsoft.sqlserver.jdbc.SQLServerDriver". Anyway!
bundle:list | grep MSSQL
355 | Active | 80 | 0.8.0 | OPS4J Pax JDBC MSSQL Driver Adapter
bundle:refresh 355
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!!!

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Thu, 04 May 2017 21:15:24 GMT

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

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Thu, 04 May 2017 21:16:12 GMT

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

Re: setting up JDBC connection pool in Talend ESB container

vharcq — Tue, 16 May 2017 10:51:43 GMT

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

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Tue, 16 May 2017 18:09:54 GMT

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>

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Thu, 10 Jan 2019 16:41:19 GMT

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

create a datapool configuration file under runtimePath/container/etc/org.ops4j.datasource-db_your_data_source_name.cfg

Update the file accordingly:

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

note in this approach you can name your data source anything and not for example "ds-mysql"

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

go into the talend client i.e ./client in the container/bin folder
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

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:

You will need to download the ojdbc oracle Jar and copy it to you deployment environment

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'

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.
Once this is complete you can then install the pax driver
```
feature:install pax-jdbc-oracle
```

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Mon, 28 Oct 2019 22:56:10 GMT

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

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Tue, 29 Oct 2019 12:01:11 GMT

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

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Sat, 02 Nov 2019 02:54:02 GMT

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

Re: setting up JDBC connection pool in Talend ESB container

Anonymous — Sun, 03 Nov 2019 16:53:50 GMT

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.

Re: setting up JDBC connection pool in Talend ESB container

David_Beaty — Mon, 14 Jun 2021 15:12:01 GMT

Hi @Brian Johnson

Used your instructions above to install connection pooling for a SQL Server instance running the Talend Runtime 7.3.1 Open Source.

So helpful

Thanks

David