Skip to main content
Announcements
UPGRADE ADVISORY for Qlik Replicate 2024.5: Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
AKV0524
Contributor III
Contributor III

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

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.

Labels (2)
1 Solution

Accepted Solutions
john_wang
Support
Support

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

SELECT id,name,hex(notes) FROM testforbitdata;
ID|NAME                |00003               |
--+--------------------+--------------------+
 2|test                |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:

john_wang_0-1686318953246.png

In Oracle target, the HEX values are:

john_wang_1-1686319071641.png

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

Regards,

John.

 

 

 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!

View solution in original post

11 Replies
Dana_Baldwin
Support
Support

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

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

john_wang
Support
Support

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:

john_wang_0-1686101975195.png

 

Hope this helps.

Regards,

John.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
AKV0524
Contributor III
Contributor III
Author

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

john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
AKV0524
Contributor III
Contributor III
Author

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

john_wang
Support
Support

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

SELECT id,name,hex(notes) FROM testforbitdata;
ID|NAME                |00003               |
--+--------------------+--------------------+
 2|test                |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:

john_wang_0-1686318953246.png

In Oracle target, the HEX values are:

john_wang_1-1686319071641.png

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

Regards,

John.

 

 

 

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
AKV0524
Contributor III
Contributor III
Author

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

john_wang
Support
Support

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.

Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
AKV0524
Contributor III
Contributor III
Author

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