topic Re: Replicating Boolean failed on invalid SQL data type -7 in Qlik Replicate

Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Wed, 11 May 2022 13:13:25 GMT

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>)

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Wed, 11 May 2022 16:13:17 GMT

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/prereq_db2_replicate_machine.htm

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

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Wed, 11 May 2022 16:23:06 GMT

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_db_data_types_source.htm

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

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Wed, 11 May 2022 17:02:04 GMT

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

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Wed, 11 May 2022 19:08:54 GMT

Hi Dana,

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

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Wed, 11 May 2022 19:32:04 GMT

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,

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Wed, 11 May 2022 19:43:53 GMT

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)

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Wed, 11 May 2022 20:21:30 GMT

Hi @FrankBosh

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

Thanks,

Dana

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Wed, 11 May 2022 20:22:29 GMT

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

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Thu, 12 May 2022 14:24:15 GMT

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

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Mon, 16 May 2022 19:48:20 GMT

The work around is not effective with DB2 driver version 11.1.4.

I'm waiting on another department to install DB2 driver 11.5.6. I will re-test the Boolean data type and the work around once it's installed and post the results.

Best regards,

Ken

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Fri, 27 May 2022 15:32:25 GMT

Hi @FrankBosh , Ken,

How is the testing going with driver 11.5.6?

Thanks,

Dana

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Fri, 27 May 2022 19:30:13 GMT

Hi Dana,

Driver 11.5.6 was installed on our non-prod server last week. In Table Settings, Transform, Output, set column to Type Boolean it did not work. Now, Type is set to string(1) and waiting to get data from the application. If this doesn't work I will try setting Type to Int1, then I will try the work around with source_lookup.

source_lookup(3,'<tabschema>','<tabname>','<columnname>','<uniqueidentier=?>','<$uniqeridentier>)

I will post the test results.

Best regards,

Ken

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Wed, 01 Jun 2022 14:37:52 GMT

Hi Dana,

Testing with DB2 driver V11.5.6 did not work. In Table Settings, Transform, Output, Column type was set to string(1), int(1), unit1, bytes(1) and source _lookup(3,’<tabschema>’,’<tabname>’,’<colname>’,’uniqueidentifer=?>’,’<$uniqueidentifer>) these data types failed on invalid SQL data type -7. Any thing else I can try will be appreciated.

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Thu, 02 Jun 2022 16:33:37 GMT

Hi @FrankBosh

I'm checking with my colleagues on next steps.

Thanks,

Dana

Re: Replicating Boolean failed on invalid SQL data type -7

Dana_Baldwin — Fri, 03 Jun 2022 15:51:53 GMT

Hi @FrankBosh , Ken,

Please open a support case to see if we can help you further.

Thanks,

Dana

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Mon, 06 Jun 2022 14:16:55 GMT

Hi Dana,

I'll open a support case.

Thanks,

Ken

Re: Replicating Boolean failed on invalid SQL data type -7

john_wang — Wed, 01 Mar 2023 13:56:43 GMT

Hello @FrankBosh ,

Got information from PM team that BOOLEAN data type for IBM DB2 LUW is in R&D consideration, hopefully we get it verified in Q2/Q3 this year (this is not commitment yet). We will keep you posted of any new progress.

Hops this helps.

Regards,

John.

Re: Replicating Boolean failed on invalid SQL data type -7

john_wang — Wed, 01 Mar 2023 13:56:56 GMT

Hello @FrankBosh ,

Hops this helps.

Regards,

John.

Re: Replicating Boolean failed on invalid SQL data type -7

FrankBosh — Wed, 01 Mar 2023 14:35:20 GMT

Thanks, John. We'll await further updates.