new CCSID decodes backwards from ascii to ebdic

[msillence]

The following exception on adding the ccsid:

org.apache.kafka.connect.errors.ConnectException: An exception occurred in the change event producer. This connector will be stopped.
at io.debezium.pipeline.ErrorHandler.setProducerThrowable(ErrorHandler.java:53) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:116) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) [?:?]
at java.util.concurrent.FutureTask.run(FutureTask.java:264) [?:?]
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) [?:?]
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) [?:?]
at java.lang.Thread.run(Thread.java:833) [?:?]
Caused by: java.lang.RuntimeException: Failed to initialize snapshot context.
at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:65) ~[classes/:?]
at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:30) ~[classes/:?]
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
... 5 more
Caused by: java.lang.IllegalArgumentException: The column "ëáà|<" is referenced as PRIMARY KEY, but a matching column is not defined in table "DATABASE.SCHEMA.TABLE"!
at io.debezium.relational.TableEditorImpl.lambda$updatePrimaryKeys$0(TableEditorImpl.java:107) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at java.util.ArrayList.removeIf(ArrayList.java:1672) ~[?:?]
at java.util.ArrayList.removeIf(ArrayList.java:1660) ~[?:?]
at io.debezium.relational.TableEditorImpl.updatePrimaryKeys(TableEditorImpl.java:103) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.relational.TableEditorImpl.create(TableEditorImpl.java:307) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.relational.Tables.lambda$overwriteTable$2(Tables.java:194) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.util.FunctionalReadWriteLock.write(FunctionalReadWriteLock.java:84) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.relational.Tables.overwriteTable(Tables.java:187) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.jdbc.JdbcConnection.readSchema(JdbcConnection.java:1203) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.readTableStructure(As400SnapshotChangeEventSource.java:140) ~[classes/:?]
at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:62) ~[classes/:?]
at io.debezium.connector.db2as400.As400SnapshotChangeEventSource.execute(As400SnapshotChangeEventSource.java:30) ~[classes/:?]
at io.debezium.pipeline.ChangeEventSourceCoordinator.doSnapshot(ChangeEventSourceCoordinator.java:155) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.executeChangeEventSources(ChangeEventSourceCoordinator.java:137) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
at io.debezium.pipeline.ChangeEventSourceCoordinator.lambda$start$0(ChangeEventSourceCoordinator.java:109) ~[debezium-core-2.1.2.Final.jar:2.1.2.Final]
... 5 more

Primary key in question was SEDOL:

ëáà|< = 0x53, 0x45, 0x44, 0x4F, 0x4C - EBDIC CP285
SEDOL = 0x53, 0x45, 0x44, 0x4F, 0x4C - ASCII

[andymalo43]

Hi
First over, I want to thank you about the connector.
I got the same issue :( do you know if it will be resolved soon ?
Thank you again for your answer 😃

[msillence]

I was hoping the latest release had fixed it- what version are you running @andymalo43 ?

[andymalo43]

Hi @msillence ! I used the docker-compose.yml
Do I have to build it from your source ?

THank you for your answer :)

[msillence]

OK I've pushed a new image and updated the tag latest
please can you do a pull:
docker pull silllencem/debezium-connector-ibmi:latest
before restarting:
docker compose up -d
and see if that fixes it

[andymalo43]

Hi back @msillence ,

I tried and i still have the same message :/

[msillence]

I tried and i still have the same message :/

:(

just checking - do you actually need the ccid conversion - i.e. is the data in the tables different to the character set on the table?
the override is only for https://github.com/jhc-systems/debezium-connector-ibmi/blob/main/jt400-override-ccsid/readme.md

Annoyingly it's working for us even without the latest fix, I'll have to check with our iseries expert as we do need to fix this - not having a reproducible issue does make it harder.

[andymalo43]

Hi !
Yep I have to convert our ccsid (some tables are on 65xxx) but I just find that I didn't do anything on ccsid configuration when I was uploading a connector on your IBMi debezium.
I m going to check that today and tell you back after
Thank you again !

[andymalo43]

Hi back @msillence
I got the same behavior after pull the last docker image :(

I delete the volume "mskafka" before up the docker-compose and I still got the same problem :/

[msillence]

to help diagnose this please can you send me the results from running this:

docker run -it --rm -e "FORCED_CCSID=<ccsid to force>" -e "ISERIES_HOST=<iseries name/ip>" -e "ISERIES_USER=<iseries user>"  -e "ISERIES_PASSWORD=<password>" -e "ISERIES_SCHEMA=<schema>" -e "TABLE=<table>" silllencem/jt400-test:latest-SNAPSHOT

replacing the stuff between <> with your settings - note that you need the long name for the table if is is different from the short name

[andymalo43]

hi @msillence ,
here is my console execution*

[msillence]

hi @msillence , here is my console execution*

Thanks, sadly that doesn't appear to have worked I was hoping to see a lines such as:

12:55:38.244 [main] [INFO] class com.ibm.as400.access.AS400JDBCConnectionForcedCcsid - requested ccsid 37 using forced ccsid 285

my guess is it's not connected to the database yet and is going to timeout

maybe a problem with the hostname resolution in docker?

you could try adding:
--add-host "<myiseries hostname>:<iseries ip address>"
to the docker command or use the ip address in -e "ISERIES_HOST=<iseries name/ip>"

testing locally I get more output for everything other than the ip address being wrong

[msillence]

Our current theory is:
The driver request for the table metadata is running CALL SYSIBM.SQLCOLUMNS and we suspect for some systems this is being returned in utf16/utf8/ascii and we then convert it to the forced CCSID.
I've a new release building which will only convert between two specific character sets - assuming that the system default that comes back for CALL SYSIBM.SQLCOLUMNS is not the same as the you wish to convert from then we should be OK
It does mean we need an extra parameter from_ccsid and I've renamed the forced_ccsid to to_ccsid

#46

new image: silllencem/debezium-connector-ibmi:2.2.1.Final_1.9.1

[andymalo43]

Hi @msillence it seems to work with the last docker image :D Sorry for the late answering, I didn't have so much time to work on it. Thank you a lot
So now it's all good. and the keys for table are recognized. But there are little problems :
When i imported the connector, debezium load all my rows in redpandas with their initial state and their value -> It's allright
I tried to update/delete some rows, but nothing happened to topics in redpanda.
I'm not a big debezium user so maybe it's the standard comportment.
I hope use your connector for our need to sync data from ibmi to clickhouse/postgres/or what else sql/nosql database :p
Thank you again for your time and your knowledge's sharing :)

[martintosney]

Hi @andymalo43, that's great news that you've got the initial snapshot working.

It sounds like, for whatever reason, the connector is having some trouble reading the journal for the objects. The README is not currently very clear on the requirements on the IBM i side, but the main things you need are:

• A journal linked to the library the tables reside in (set with STRJRNLIB)
• Journaling active for all the tables in your connector configuration, being recorded to the same journal as that of the library

Do you see anything interesting in the Debezium container logs that might indicate what happened?

(It may also be worth opening a new issue, since it sounds like the initial problem is now resolved)

[msillence]

please can you check the logs for errors:

docker logs ibmi-connector |   jq -rcR 'fromjson? | [.timestamp, .severity, .message]' | grep ERROR

also review the readme here for setting up journaling: https://github.com/jhc-systems/debezium-connector-ibmi/blob/main/journal-parsing/Readme.md

[msillence]

Also are you seeing the correct decoding of CCSID in the latest image - did you change the FORCED_CCSID to the TO_CCSID and add in the FROM_CCSID?

[andymalo43]

Hi both of you :D

@msillence , there was no error message in my log :)
@martintosney for the CCSID, the options from_ccsid et to_ccsid are usefull :)

Thank you a lot for your answers ! Everything 's going fine :

• It was my bad, I journaled my files with a journal that was not in the *LIB
  => I Just updated my file and now it detects everythings 😮

--> for this constraint, do you think that you'll be able to update it ? I'm not a big java developper but I scrolled through the code and saw this on this page

I think that maybe it's where the problem is. I use a query to get journal info https://gist.github.com/andymalo43/16231be5805866b523abe3271409a0e5

I hope this can help you :)

have a great day

And again : Thank you very much for your work :)

[msillence]

This has been working for a while now closing