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

[AKV0524]

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.

[Dana_Baldwin]

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]

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.

[AKV0524]

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]

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.

[AKV0524]

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 · Accepted solution]

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:

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.

[AKV0524]

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]

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.

[AKV0524]

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