Bugs in DB2 Connector

Hi,

1) Wrong PreparedStatement parameters

In https://github.com/debezium/debezium-connector-db2/blob/main/src/main/java/io/debezium/connector/db2/Db2Connection.java

The GET_LIST_OF_KEY_COLUMNS sql requires 2 parameters

• The TableSpaceId as the first parameter
• The TableId as the second parameter

When we read the getTableSchemaFromChangeTable at lines 370-371 we see:

• It set the parameter 1 twice instead of 1 and 2
• So, it didn't set the parameter 2

2) The script asncdcaddremove.sql have a bug in the ADDTABLE function.

The ADDTABLE function insert a row inside IBMSNAP_PRUNCNTL.

For doing this, it compute the unique index having the column MAP_ID

The MAP_ID is a VARCHAR column but the function try to handle it like an INT
 
Actually, the value is computed like this:
   CAST(CAST(max(MAP_ID) AS INT) + 1 AS VARCHAR(10))

The MAX function occurs directly on the VARCHAR column.

After that, the max is converted to INT and incremented before to be recasted to VARCHAR.

With a series of "1" , "2" , "3" , ... , "9" , "10"

The max(MAP_ID) will return "9" in the VARCHAR ordering (maybe depend of collation).

That means, the function will return "10" and we will have a "duplicate keys" since "10" already exist.

I propose to execute the MAX function on the INT version of the MAP_ID for returning 10 in our example. After that we can increment it and recast it to VARCHAR for resulting "11"

   CAST( max( CAST( MAP_ID AS INT ) ) + 1 AS VARCHAR(10) ) 

You can reproduce the problem when you try to add more than 9 tables