JDBC Plugin - Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc when parsing xml datatype in DB2

khannaja · February 3, 2023, 9:23am

New to Logstash(ver 8.6.0), need to parse xml data type from db2 database. Keep getting "Missing Converter handling for full class" error. Read through most discussion post on this subject and tried a few things without any luck . Here are the details.
####Issue : Unable to parse xml data using jdbc/db2. New to Logstash please help.

####Scenario 1: Works without xml data (only id field)

[Config :]
</>


input {
jdbc {
jdbc_driver_library => "./drivers/db2jcc4.jar"
jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
jdbc_connection_string => "jdbc:db2://XX.XX.XX.XXX:50001/MAILHST"
jdbc_user => "mailuser"
jdbc_password => "XXXXXXX"
schedule => "* * * * *"
#statement_filepath => "C:\logstash\logstash-8.6.1\preparedstmt\getMailRequest.sql"
statement => "select id from mail.jktest where id=1000"
target => "top"
}
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "logstash_db2_test1"
    user => "elastic"
    password => "XXXXXXXXX"
  }
}

</>
[Elasticsearch Output : Success]

DELETE /logstash_db2_test1
PUT /logstash_db2_test1

GET /logstash_db2_test1/_search
{
  // The query parameter indicates query context.
  "query": {
    "match_all": {} // Matches all documents.
  }
}

{
  "took": 0,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": "logstash_db2_test1",
        "_id": "gbV6FoYB-Tefeb34nylY",
        "_score": 1,
        "_source": {
          "top": {
            "id": 1000
          },
          "@version": "1",
          "@timestamp": "2023-02-03T08:53:00.252634300Z"
        }
      }
    ]
  }
}

####Scenario 2: Does not work with xml field (xml_data)

[Config :]

</>

input {
jdbc {
jdbc_driver_library => "./drivers/db2jcc4.jar"
jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
jdbc_connection_string => "jdbc:db2://XX.XX.XX.XX:50001/MAILHST"
jdbc_user => "mailuser"
jdbc_password => "XXXX"
schedule => "* * * * *"
statement => "select id,xml_data from MAIL.JKTEST where id=1000"
target => "top"
}
}

filter
{
 xml {
        remove_namespaces => "true"
        source => "xml_data"
        store_xml => "false"
		force_array => "false"
        target => "doc"
        xpath =>  [
                   "/catalog/book/author/text()","author"
        ]
    }
}
output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "logstash_db2_test1"
    user => "elastic"
    password => "XXXXXXXXXXX"
  }
}

</>
[Error]: Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc

</>

[2023-02-03T19:30:37,650][INFO ][logstash.javapipeline    ][main] Pipeline started {"pipeline.id"=>"main"}
[2023-02-03T19:30:37,664][INFO ][logstash.agent           ] Pipelines running {:count=>1, :running_pipelines=>[:main], :non_running_pipelines=>[]}
[2023-02-03T19:31:00,763][INFO ][logstash.inputs.jdbc     ][main][ec48cc52b74bb8b4dbb6b3c835a9eb2fe345e15e79f933cf75915bf55d87a356] (0.014016s) select id,xml_data from MAIL.JKTEST where id=1000
[2023-02-03T19:31:00,775][WARN ][logstash.inputs.jdbc     ][main][ec48cc52b74bb8b4dbb6b3c835a9eb2fe345e15e79f933cf75915bf55d87a356] Exception when executing JDBC query {:exception=>Sequel::DatabaseError, :message=>"Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc, simple name=dc", :cause=>"#<Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc, simple name=dc>"}
[2023-02-03T19:32:00,951][INFO ][logstash.inputs.jdbc     ][main][ec48cc52b74bb8b4dbb6b3c835a9eb2fe345e15e79f933cf75915bf55d87a356] (0.002985s) select id,xml_data from MAIL.JKTEST where id=1000
[2023-02-03T19:32:00,956][WARN ][logstash.inputs.jdbc     ][main][ec48cc52b74bb8b4dbb6b3c835a9eb2fe345e15e79f933cf75915bf55d87a356] Exception when executing JDBC query {:exception=>Sequel::DatabaseError, :message=>"Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc, simple name=dc", :cause=>"#<Java::OrgLogstash::MissingConverterException: Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc, simple name=dc>"}
[2023-02-03T19:32:11,949][WARN ][logstash.runner          ] SIGINT received. Shutting down.
[2023-02-03T19:32:12,621][INFO ][logstash.javapipeline    ][main] Pipeline terminated {"pipeline.id"=>"main"}
[2023-02-03T19:32:12,984][INFO ][logstash.pipelinesregistry] Removed pipeline from registry successfully {:pipeline_id=>:main}
[2023-02-03T19:32:12,989][INFO ][logstash.runner          ] Logstash shut down.

</>
[REFERENCES]

</>
Query : select id,xml_data from MAIL.JKTEST where id=1000
o/p :  
ID   XML_DATA
 ---- -----------------------------------------------------------------------------------------------------------------------------------------------------------------
 1000 <catalog><book><author>Gambardella Matthew</author><title>XML Developers Guide</title><genre>Computer</genre><price>44.95</price><description>An in-depth look at creating application with XML</description></book></catalog>
  ---- -----------------------------------------------------------------------------------------------------------------------------------------------------------------
DDL : CREATE TABLE "MAIL"."JKTEST" (
		"ID" INTEGER, 
		"XML_DATA" XML
	)
	DATA CAPTURE NONE 
	COMPRESS NO;
</>

Badger · February 3, 2023, 5:29pm

DB2 supports XML as a column type. The message is telling you that logstash has no class to support conversion of a row of such a column to something logstash understands. You will have to do the conversion yourself. Use a CAST in the SQL to convert the column to a string before it reaches logstash.

khannaja · February 4, 2023, 6:09am

Thank you Sir! You were right. I should better comprehend the error. After Casting xml element, the data was stored as expected. These are the changes I made.

[db2-xml-test.sql]
      select id as key,
      (xmlcast(XMLQUERY('$m/catalog/book/author' PASSING xml_data as "m") as varchar(256)) )
    as author    from MAIL.JKTEST where id=1000

[Config File]

input {
jdbc {
jdbc_driver_library => "./drivers/db2jcc4.jar"
jdbc_driver_class => "com.ibm.db2.jcc.DB2Driver"
jdbc_connection_string => "jdbc:db2://xx.xx.xx.xx:50001/MAILHST"
jdbc_user => "mailuser"
jdbc_password => "xxxxx"
schedule => "* * * * *"
statement_filepath => "C:\logstash\logstash-8.6.1\preparedstmt\db2-xml-test.sql"
target => "top"
}
}

output {
  elasticsearch {
    hosts => ["http://localhost:9200"]
    index => "logstash_db2_test1"
    user => "elastic"
    password => "xxxxx"
  }
}

[Elastic Query o/p]

{
  "took": 925,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 1,
      "relation": "eq"
    },
    "max_score": 1,
    "hits": [
      {
        "_index": "logstash_db2_test1",
        "_id": "hrUBG4YB-Tefeb34sCn9",
        "_score": 1,
        "_source": {
          "@version": "1",
          "@timestamp": "2023-02-04T05:59:00.997477100Z",
          "top": {
            "author": "Gambardella Matthew",
            "key": 1000
          }
        }
      }
    ]
  }
}

system · March 4, 2023, 6:09am

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.

JDBC Plugin - Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc when parsing xml datatype in DB2

####Scenario 1: Works without xml data (only id field)

####Scenario 2: Does not work with xml field (xml_data)

[Config :]

</> [Error]: Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc

</>
[Error]: Missing Converter handling for full class name=com.ibm.db2.jcc.am.dc