Replicate a column in the BSON format

[deaconescu]

Hello team ,

I would like to have your expertise on the below topic :

We have a table with a column that in the source (DB2) the value of this column is encoded as a BSON .

On the Qlik replicate , in the output columns list this column is by default set to BLOB .

As a target it is used MySQL , but this column contains strange characters, which cannot be handled. I understood that in order to see the value we must decode it ,however, I also understood that MySQL it is not supporting BSON type of data.

Do you think that it is possible for a datatype conversion when replicating in this case ?  If yes, what datatype you suggest using from the output columns drop list menu?

Thank you

Ionut Deaconescu

[Dana_Baldwin]

Hi @deaconescu 

I looked into this but so far have not found a readily available solution. I could not find any SQLite function that would convert BSON to another format. SQLlite is the internal database Qlik Replicate uses.

I found this link at MySQL's website, but I am not sure how relevant or useful it is, as I am not aware of how we could utilize this from within Replicate:

MySQL :: MySQL Shell 8.0 :: 11.2.4 Conversions for Representations of BSON Data Types

As I have not found a similar request / solution in our support database, you may need to engage with our Professional Services team to arrive at a solution.

Sorry I could not be of more help.

Dana

[Heinvandenheuvel]

One potential solution folks might consider would be to write a user defined function - documented by example in : 

C:\Program Files\Attunity\Replicate\addons notably file README

However...  LOBS are NOT supported as arguments, so that dog won't hunt. At least not in version 7 and before. Maybe a feature request can be made, but I believe there to be too many technical challenges to implement this.

Hein.

[deaconescu]

Thank you both for looking into this.

Regards

Ionut Deaconescu

[Dana_Baldwin]

Hi @deaconescu 

If you would like to pursue a feature request related to this, you can submit it directly to our Product Management team here: https://community.qlik.com/t5/Ideas/idb-p/qlik-ideas

Thanks,

Dana

[john_wang · Accepted solution]

Hello @deaconescu , copy @Dana_Baldwin , @Heinvandenheuvel ,

BSON column in DB2 is defined as BLOB data type too, however it stores BSON values. I'm lucky to find a way to support the BSON replication from DB2 LUW to MySQL. Essentially, I'm using source_lookup() function to convert the BLOB JSON values to TEXT JSON and replicate it to MySQL, both Full Load and CDC works fine. Below are the steps demo how it works:

1. In DB2 LUW 11.5 defined source table and put one demo row

CREATE TABLE johnw.testbson(id integer NOT NULL PRIMARY KEY,name char(20), notes BLOB);

INSERT INTO johnw.testbson VALUES (3,'test',systools.JSON2BSON('{"team-id":2,"team-name": "support"}'));

SELECT id,name, systools.BSON2JSON(notes) FROM johnw.TESTBSON;

ID|NAME                |3 |
--+--------------------+----------------------------------------+
 3|test                |{"team-id":2,"team-name":"support"}     |

2. New a Full Load + CDC enabled task, DB2 LUW 11.5.6 is source endpoint, MySQL 8.0 is target endpoint, in table transformation, add an additional column "NOTESJSON" with expression:

source_lookup('NO_CACHING','JOHNW','TESTBSON','systools.BSON2JSON(notes)','id=?',$id)

3. Results for both Full Load row and CDC row in SQL Server workbench:

Hope this helps.

Regards,

John.

[deaconescu]

Thank you John for your suggestion ,

I've asked the team to have a look.

Thank you

Ionut Deaconescu

[deaconescu]

Hello John ,

Team tried to implement your suggestion but run into this error :

Error executing data handler
Cannot read from stream
SQLite general error. Code <250>, Message <unknown error>.
Failed to fetch data for statement 'SELECT systools.BSON2JSON(MESSAGEBODY) FROM ""CONCORDIA"".""OUTBOUNDMESSAGES"" WHERE OUTBOUNDMESSAGESID=?'
RetCode: SQL_ERROR  SqlState: 22546 NativeError: -443 Message: [IBM][CLI Driver][DB2/LINUXX8664] SQL0443N  Routine ""SYSTOOLS.BSON2JSON"" (specific name ""BSON2JSON"") has returned an error SQLSTATE with diagnostic text ""Invalid binary format."".  SQLSTATE=22546

Any suggestion how we can move forward ?

Thank you

Ionut Deaconescu

[john_wang · Accepted solution]

Hello @deaconescu ,

Thanks for the update.

What's the data type of column MESSAGEBODY? it should be BLOB type and while you write data to it, use the systools.JSON2BSON function. then use reverse function systools.BSON2JSON to decode the BSON while retrieving data from the table. You can troubleshoot the problem within DB2 by query SQLs.

Regards,

John.

[deaconescu]

Hello John ,

Apologize for late replying ( for the last 2 weeks I was in vacation ).

In my absence , team , using you suggestion , manged to solve this issue.

Thanks a lot .

Regards

Ionut Deaconescu