How to connect to DB2 using JDBC with securityMechanism=13

[fsilveir (Felipe Silveira)]

Hi there,

I'm trying to extract some data from a DB2 database with securityMechanism 13. I've tried passing this parameter as part of the connection string as shown below:

input {
  jdbc {
    jdbc_connection_string => "jdbc:db2://${DB2_HOST}:${DB2_PORT}/${DB2_DB}:securityMechanism=13"
    jdbc_user => "${DB2_USER}"
    jdbc_password => "${DB2_PASSWORD}"
    jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/db2jcc4.jar"
    jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
    schedule => "${LOGSTASH_SCHEDULE}"

Which returned the following error:

[2020-09-21T12:16:01,092][ERROR][logstash.inputs.jdbc     ] Unable to connect to database. Tried 1 times {:error_message=>"Java::ComIbmD
b2JccAm::SqlSyntaxErrorException: [jcc][10214][10050][4.21.29] java.util.Properties object passed to java.sql.Driver.connect() cannot be
 overridden by URL: jdbc:db2://MYSERVER:50000/MYDATABASE:securityMechanism=13;. ERRORCODE=-4461, SQLSTATE=42815"}
{ 2054 rufus-scheduler intercepted an error:
  2054   job:
  2054     Rufus::Scheduler::CronJob "* * * * *" {}
  2054   error:
  2054     2054
  2054     Sequel::DatabaseConnectionError
  2054     Java::ComIbmDb2JccAm::SqlSyntaxErrorException: [jcc][10214][10050][4.21.29] java.util.Properties object passed to java.sql.Dr
iver.connect() cannot be overridden by URL: jdbc:db2://MYSERVER:50000/MYDATABASE:securityMechanism=13;. ERRORCODE=-4461, SQLSTATE=42815

I've also tried passing securityMechanism as part of sequel_opts, as indicated by some other folks on other posts, as shown below:

  jdbc {
    jdbc_connection_string => "jdbc:db2://${DB2_HOST}:${DB2_PORT}/${DB2_DB}"
    jdbc_user => "${DB2_USER}"
    jdbc_password => "${DB2_PASSWORD}"
    jdbc_driver_library => "/usr/share/logstash/logstash-core/lib/jars/db2jcc4.jar"
    jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
    sequel_opts => {
              jdbc_properties => {
                "securityMechanism" => "13"
              }
            }

However, I had the almost the same error as shown below:

[2020-09-21T18:35:01,516][ERROR][logstash.inputs.jdbc     ] Unable to connect to database. Tried 1 times {:error_message=>"Java::ComIbmDb2JccAm::SqlException: [jcc][1071][10615][4.21.29] Caught java.security.InvalidAlgorithmParameterException while initializing Encryption
Manager.  See attached Throwable for details. ERRORCODE=-4223, SQLSTATE=null"}
{ 2054 rufus-scheduler intercepted an error:
  2054   job:
  2054     Rufus::Scheduler::CronJob "* * * * *" {}
  2054   error:
  2054     2054
  2054     Sequel::DatabaseConnectionError
  2054     Java::ComIbmDb2JccAm::SqlException: [jcc][1071][10615][4.21.29] Caught java.security.InvalidAlgorithmParameterException while
 initializing EncryptionManager.  See attached Throwable for details. ERRORCODE=-4223, SQLSTATE=null

For other applications (such as DBVis) the only way I can connect to DB2 with securityMechanism 13 is using IBM SDK -- however as pointed out in the documentation, IBM's SDK is not currently supported by Logstash.

Any help would be very much appreciated!

Thanks in advance,
Felipe Silveira