0

When I run kafka connect source connector on MySQL everything runs fine, I'm working with the timestamp strategy, the problem comes when I try to do it with a different database, in my case I'm trying to get the information from a db2 table, when I execute my connector I get the following error:

 ERROR Failed to get current time from DB using query select CURRENT_TIMESTAMP; on database DB2 (io.confluent.connect.jdbc.util.JdbcUtils:263)
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=<END-OF-STATEMENT>;( CONCAT || / MICROSECONDS MICROSECOND SECONDS SECO, DRIVER=4.21.29

I think it is not detecting the database and it is trying to execute the query: select CURRENT_TIMESTAMP

And it doesn't work in db2, so we need to specify the database, is there a way to specify the Driver class or the database we are using ?

EDIT

----My connector configuration

{
    "name": "test2-conn",
    "config": 
    {
        "mode":"timestamp",
        "debug":true,
        "poll.interval.ms":10000,
        "timestamp.delay.interval.ms":60000,
        "timestamp.column.name":"LAST_UPDATE_TIME",
        "incrementing.column.name":"id",
        "connector.class":"io.confluent.connect.jdbc.JdbcSourceConnector" ,
        "connection.url":"jdbc:db2://url:port/db:currentSchema=schema",
        "connection.password":"psswd",
        "connection.user":"user",
        "table.types":"VIEW",
        "table.whitelist":"my_view",
        "tasks.max":"1",
        "topic.prefix":"gta-tors-platform",
        "value.converter": "org.apache.kafka.connect.json.JsonConverter",
        "key.converter":"org.apache.kafka.connect.storage.StringConverter",
        "key.converter.schemas.enable":false,
        "value.converter.schemas.enable":false,
        "transforms":"createKey",
        "transforms.createKey.type":"org.apache.kafka.connect.transforms.ValueToKey",
        "transforms.createKey.fields":"EMP_NAME"
        }
}

I'm using I'm using z/OS DB2 V11.1 mode NFM

Thanks for your help

mustaccio
  • 18,234
  • 16
  • 48
  • 57
Alejandro Agapito Bautista
  • 1,344
  • 1
  • 15
  • 35
  • see my edits :) thanks for your help – Alejandro Agapito Bautista Jul 09 '18 at 19:03
  • `select` from nothing is unsupported in Db2, which means the connector does not pick up the correct SQL dialect for the URL. Looks like source dialects were added in [version v5.1.0-beta201806200051](https://github.com/confluentinc/kafka-connect-jdbc/commit/15d16a73c4f91c4a40432ab4b520dd1bc48939a4#diff-bdcd199fdeea928a895142efb3347c4f), check if you're using an earlier version. – mustaccio Jul 09 '18 at 19:33
  • We are using kafka-connect-jdbc-4.0.0.jar, is it possible to do it with this version ? – Alejandro Agapito Bautista Jul 09 '18 at 19:44
  • I'm testing with the version kafka-connect-jdbc-4.1.1.jar and I'm getting ERROR Failed to get current time from DB using query values(CURRENT_TIMESTAMP) on database DB2 (io.confluent.connect.jdbc.util.JdbcUtils:264) com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-142, SQLSTATE=42612, SQLERRMC=null, DRIVER=4.21.29 :( – Alejandro Agapito Bautista Jul 09 '18 at 21:19
  • What's your Db2 version and platform? I believe Db2 for z/OS did not support the `VALUES` statement until recently. – mustaccio Jul 10 '18 at 12:08
  • Looks like https://github.com/confluentinc/kafka-connect-jdbc/pull/319 – mao Jul 10 '18 at 14:08
  • Do you need `dialect.name=Db2DatabaseDialect` in the worker properties file ? – mao Jul 10 '18 at 14:26
  • I saw the pull request, but looks like they are trying to use query = "values(CURRENT_TIMESTAMP)"; and it is not supported by db2, i think I have this version because my error says RROR Failed to get current time from DB using query values(CURRENT_TIMESTAMP) on database DB2 (io.confluent.connect.jdbc.util.JdbcUtils:264), dialect.name=Db2DatabaseDialect didn't work :/ it knows that I'm using db2 but it is not using the proper sql statement, i'm not sure if it should use SELECT CURRENT TIMESTAMP FROM SYSIBM.SYSDUMMY1; – Alejandro Agapito Bautista Jul 10 '18 at 14:42
  • `values(current_timestamp)` is supported by current version of Db2-for-Linux/Unix/Windows. So it depends on your Db2-server operating-system (Z/OS, i-Series, LUW) and Db2-version - that's why you should edit your question to ADD that missing information. – mao Jul 10 '18 at 15:35
  • See my edits, I'm using I'm using z/OS DB2, is there a way to change the query used to get the current timestamp ? – Alejandro Agapito Bautista Jul 10 '18 at 21:56
  • Please also EDIT to add the Db2-version *and* the mode (cm, enfm, nfm). Ask your Z/OS DBA for the details. As it is open source, you can alter Db2DatabaseDialect.java to use a different query the rebuild and test, or you can open a new issue on github BUT *you must specify Db2-version and mode* details. – mao Jul 11 '18 at 09:42
  • I included them , thanks for your help – Alejandro Agapito Bautista Jul 11 '18 at 14:30
  • Which database connector you have used for connecting kafka connect with DB2? – vishal paalakurthi Dec 27 '18 at 06:20
  • we faced same issue recently , we used same version of kafka connect jdbc 4.0.0. which you used , but after upgrading to 5.1.it solved our problem , so please try to upgrade to new jar – Bravo May 23 '19 at 05:48

0 Answers0