Replicating Boolean failed on invalid SQL data type -7

[FrankBosh]

Replicating columns defined as Boolean failed after upgrading source endpoint from DB2 for LUW V11.1.4.6 to V11.5.7.0.  The error message is "<column> has unknown SQL data type -7" and forces the Task to terminate.  I tried a workaround with source_lookup function to transform the Boolean column and get the same error message.

I thought upgrading DB2 Client to V11.5.7.0 may help, but I read on this forum that I should not upgrade DB2 Client to V11.5.7.0 as it is not certified for use.

Does anyone have a solution or workaround?

Our environment:

Qlik Replicate 2021.11.0.165

Source end point is DB2 for LUW V11.5.7.0

Target end point is Azure SQL Server

DB2 Client is V11.1

Source_lookup function:   Source_lookup(3, <tabschema>, <tabname>, <Boolean colname>, <primary key colname> =?, $< primary key colname>)

[Dana_Baldwin · Accepted solution]

Hi @FrankBosh 

I checked the User Guide and it shows that the required client version is 11.5 for use with both 11.1 and 11.5 source database version.

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/IBM%20DB2%20for%20LUW/prer...

I'm checking with my colleagues to see if there is an issue specific to the 11.5.7 client and will follow up.

Thanks,

Dana

[Dana_Baldwin · Accepted solution]

Hi @FrankBosh 

Upon further investigation, we don't support the boolean data type for DB2 LUW source.

https://help.qlik.com/en-US/replicate/November2021/Content/Replicate/Main/IBM%20DB2%20for%20LUW/db2_...

Does it fail reading from the source, or writing to the target? If to the target, as a work around, can you change the data type to VARCHAR(1) in the task for the target table? If from the source, it is feasible to change the source column data type to VARCHAR(1)?

Thanks,

Dana

[Dana_Baldwin · Accepted solution]

Hi @FrankBosh 

It is true that we don't support version 11.5.7 driver yet, but since the User Guide states that version 11.5 is required, we advise to use driver 11.5.6.

Thanks,

Dana

[FrankBosh]

Hi Dana, 

I'll download DB2 Client V11.5.6 for now and wait for your response on which version to use.

[FrankBosh]

Hi Dana,

It failed reading from the source.  I see the link you provided indicates Boolean data type is not supported.

DB2 for LUW did not support Boolean data type, however I raised case number 01938201 in March 2020 for a work around using source_lookup function.  "Source_lookup function:   Source_lookup(3, <tabschema>, <tabname>, <Boolean colname>, <primary key colname> =?, $< primary key colname>)".  This workaround worked, at the same time I raised case number 01950288 to request New Functionality to replicate Boolean data type.

Thanks,

[FrankBosh]

I should add the error message from the log.

00004588: 2022-05-09T09:48:31 [SOURCE_CAPTURE ]E: Column 'AUDIOVISUAL_TIMING_STRICT_IND' has unknown SQL data type -7 [1020109] (db2luw_endpoint_capture.c:1797)

[Dana_Baldwin]

Hi @FrankBosh 

Please try with client 11.5.6 and let us know if the work around is still effective.

Thanks,

Dana

[Dana_Baldwin]

Hi @FrankBosh 

All our enhancement request have been moved here, you may need to resubmit the request:

https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana

[FrankBosh]

Enhancement request to add Boolean data type to DB2 for LUW V11.5.7 was submitted.