topic unable to add a secondary database node in tdboutput in Talend Studio

unable to add a secondary database node in tdboutput

MKAPOOR1596038160 — Tue, 02 Nov 2021 07:08:55 GMT

I am using vertica 3 node distributed High availability database to connect using jdbc & load data from talend jobs. By default, the vertica driver that talend use supports failover that in case the primary node of vertica is down, it switches to next node given in the connection string. However the connection string that Talend use with tdboutput component with vertica is :

String url_tDBOutput_1 = "jdbc:vertica://" + "primary_db_host" + ":" + "5433" + "/" + "padb";

String dbUser_tDBOutput_1 = "dbuser";

final String decryptedPassword_tDBOutput_1 = context.dbpassword;

String dbPwd_tDBOutput_1 = decryptedPassword_tDBOutput_1;

conn_tDBOutput_1 = String url_tDBOutput_1 = "jdbc:vertica://" + "primary_db_host" + ":" + "5433" + "/" + "dbname";

String dbUser_tDBOutput_1 = "dbuser";

final String decryptedPassword_tDBOutput_1 = context.dbpassword;

String dbPwd_tDBOutput_1 = decryptedPassword_tDBOutput_1;

conn_tDBOutput_1 = java.sql.DriverManager.getConnection(url_tDBOutput_1, dbUser_tDBOutput_1,

dbPwd_tDBOutput_1);

This will build the final DB connection string with Talend as : java.sql.DriverManager.getConnection(url_tDBOutput_1, dbUser_tDBOutput_1,

dbPwd_tDBOutput_1);

There is a feature in vertica jdbc using the following connection string having "db_secondary_host" with given syntax:

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/ClientJDBC/JDBCConnectionFailover.htm?tocpath=Connecting%20to%20Vertica%7CClient%20Libraries%7CProgramming%20JDBC%20Client%20Applications%7CCreating%20and%20Configuring%20a%20Connection%7C_____8

java.sql.DriverManager.getConnection(url_tDBOutput_1, dbUser_tDBOutput_1,

dbPwd_tDBOutput_1, db_secondary_host);

This will avoid the impact in case primary_db_host is down, it automatically switch to db_secondary_host.

How can i enable this functionality in my talend design as by default, talend doesn't support it?

Please guide

Re: unable to add a secondary database node in tdboutput

Anonymous — Fri, 05 Nov 2021 06:39:09 GMT

I think this could be a new feature, can you please open a jira issue on our bugtracker for R&D team to investigate it?

Regards

Shong

Re: unable to add a secondary database node in tdboutput

MKAPOOR1596038160 — Sun, 07 Nov 2021 04:52:54 GMT

Thanks Shong, This is my first time to log a JIRA which to best of my effort tried and logged here: - Talend Open Integration Solution (talendforge.org)

Can you please check if i did the JIRA process correctly?

Regards

Madhusudan

Re: unable to add a secondary database node in tdboutput

Anonymous — Tue, 30 Nov 2021 22:37:54 GMT

Hello Madhusudan,

I'd like to follow up on this request with you.

Based on the JIRA / Official documentation this feature might be supported as of today.

Official documentation suggests:

// Set two backup hosts to be used if connecting to the first host

// fails. All of these hosts will be tried in order until the connection

// succeeds or all of the connections fail.

myProp.put("BackupServerNode", "VerticaHost02,VerticaHost03");

Properties can also be passed to JDBC drivers using Additonal JDBC Parameters.

https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ConnectingToVertica/ClientJDBC/JDBCConnectionProperties.htm?tocpath=Connecting%20to%20Vertica%7CClient%20Libraries%7CProgramming%20JDBC%20Client%20Applications%7CCreating%20and%20Configuring%20a%20Connection%7C_____1

Include the property name and value as part of the connection string you pass to the DriverManager.getConnection() method.

So the additonal JDBC parameters in talend studio should be:

"?BackupServerNode=VerticaHost02,VerticaHost03"

Could you please check and report back if this works?

Regards,

Balázs

Re: unable to add a secondary database node in tdboutput

MKAPOOR1596038160 — Wed, 01 Dec 2021 04:25:29 GMT

Hello Balazs, Following your email, I am able to connect to backup node in case primary node is failing The only thing that I changes is updating below( without ? 😊) & it worked. [cid:image002.png@01D7E699.8839E7C0] Thanks a lot for the explanation & apology for logging JIRA. Madhusudan ST Restricted