topic Re: Can we replicate view from IBM DB2 as 400 iSeries source Database. in Qlik Replicate

Can we replicate view from IBM DB2 as 400 iSeries source Database.

AKV0524 — Tue, 06 Jun 2023 18:05:45 GMT

Can we replicate view from IBM DB2 as 400 iSeries source Database. we are able to find base table from the tool but we are not able to find view via Qlik tool. Is there any possibility we can replicate view via Qlik replicate tool.

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

Dana_Baldwin — Tue, 06 Jun 2023 18:20:14 GMT

Hi @AKV0524

I checked our documentation and unfortunately only DB2 LUW and z/OS support replicating a view from the source:

Selecting tables and/or views for replication #Selecting tables and/or views for replication | Qlik Replicate Help

Please submit a feature request to our Product Management team here to see if we can add this support:

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

Thanks,

Dana

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

john_wang — Wed, 07 Jun 2023 04:58:26 GMT

Hello @AKV0524 ,

Agree with @Dana_Baldwin . The good news is we have a workaround to replicate DB2i VIEW in Full Load ONLY task:

1- create a 64-bit ODBC DSN which connect to IBM DB2 400 iSeries Database

2- users the generic ODBC DSN in source Endpoint. now you can see both tables/views are visible in task design, as below:

Hope this helps.

Regards,

John.

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

AKV0524 — Thu, 08 Jun 2023 12:45:50 GMT

Hi John,

As always thanks for the solution. After creating ODBC DSN i am able to replicate the views. But i observed data between source to snowflake target is not matching for some column.

For example in source column datatype is CHAR () FOR BIT DATA length 10 but in snowflake after loading the table column datatype is BINARY(20). I have tried to convert column to string in Attunity tool but still data mismatch issue is there.

Do you have any idea how to fix this issue .

Attaching screenshot for the reference.

Thanks

Amit

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

john_wang — Thu, 08 Jun 2023 15:35:19 GMT

Hello @AKV0524 ,

Good news and thanks for the update!

The data type mapping is correct. In most relational databases, "FOR BIT DATA" is used to store hex values rather than regular string type characters. It's same in DB2 for iSeries (base64Binary encoding),the data type "CHAR(10) FOR BIT DATA" in DB400 should map to BINARY(20) in Snowflake.

You may compare the source and target data after replication,

In DB400: use hex() function

In Snowflake: use HEX_DECODE_BINARY() function

Looks to me the values in the two sides should be equal because the values of the column are not associated with a coded character set and are never converted.

Let me know if you need any additional information.

Regards,

John.

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

AKV0524 — Thu, 08 Jun 2023 16:09:34 GMT

Hi John,

i have tried replicate full load without any datatype conversion but still values are not matching between source and target.

views are present in db2 i series may be are using ODBC DSN as a source end point and thus it is creating issues.

Thanks

Amit

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

john_wang — Fri, 09 Jun 2023 14:01:19 GMT

Hello Amit @AKV0524 ,

Thanks for your patience.

Replicate task replicate from DB400 table to Snowflake and Oracle works well for me. "FOR BIT DATA" is used to store hex values, so in DB2i side, the sample table and data is:

CREATE TABLE TESTFORBITDATA (ID INTEGER NOT NULL PRIMARY KEY, NAME CHAR(20), NOTES CHAR(10) FOR BIT DATA);
INSERT INTO TESTFORBITDATA values (2,'test',x'f0f1f2f3f4f5f6f7f8f9');

Please take note the values for column "CHAR(10) FOR BIT DATA" is HEXADECIMAL value, prefix by 'x'.

In Replicate task, the column maps to BYTES(10), after the Full Load done the result in Snowflake is "F0F1F2F3F4F5F6F7F8F9", see:

In Oracle target, the HEX values are:

BTW, I tested both table and view, both of them works fine. let me know if you need any additional assistance.

Regards,

John.

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

AKV0524 — Fri, 09 Jun 2023 19:29:01 GMT

Hi John,

can we connect for quick call for 10-15 minute on monday est morning if possible. please let me know your availability on monday est morning.

Thanks

Amit

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

john_wang — Mon, 12 Jun 2023 08:39:22 GMT

Hello Amit @AKV0524 ,

Thanks for the update. We'd like to suggest you open a support ticket (reference to this article) ,support team will setup call with you, certainly I'd love to continue to work on this ticket with global support team together until all questions/doubts are solved.

Best Regards,

John.

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

AKV0524 — Tue, 13 Jun 2023 10:04:41 GMT

Hi John,

As you have suggested i have created a support case 00089196. can you please check and let me know we can connect for a quick call

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

john_wang — Tue, 13 Jun 2023 14:54:01 GMT

Hi @AKV0524 ,

Thank you. Please update the support case with agenda/questions ,let's continue with the support case.

Regards,

John.

Re: Can we replicate view from IBM DB2 as 400 iSeries source Database.

AKV0524 — Tue, 13 Jun 2023 16:16:17 GMT

Hi John,

I have updated the case with same issue what are discussing in this community portal.

Thanks

Amit