CHAR in the source but VARCHAR in the target (DB2-z/OS -> DB2-LUW)

[Laurin37]

Hello,

I want to replicate a table from a DB2-z/OS database to a DB2-LUW database (ODBC endpoint).

I noticed that Qlik Replicate creates the columns that have the data type CHAR in the source not as CHAR but as VARCHAR in the target. This causes problems in my following processing route.

How can I ensure that Qlik Replicate also creates those columns with the data type CHAR in the target? I do not want to create the table manually and instead have it created by Qlik Replicate.

Best regards,
Laurin

[aarun_arasu]

Hello @Laurin37 ,

Thank you for reaching out to the Qlik Community.

Yes, you're correct. In Qlik Replicate, when data is sourced from DB2 z/OS, a `CHAR` data type is converted to a `String` on the Replicate server, and then it is further converted to a `VARCHAR` when the table is created on the target database using ODBC.

As a workaround, you can refer to the user guide linked below, which provides step-by-step instructions for converting a column's data type in Qlik Replicate using transformation rules:

https://help.qlik.com/en-US/replicate/May2024/Content/Global_Common/Content/SharedEMReplicate/Custom...

I hope this helps meet your requirements!

Best regards,
Arun

[Laurin37]

Hey @aarun_arasu ,

Thanks for the advice. Could you provide me an example for such an transformation rule to change from VARCHAR to CHAR. 
That would help me a lot!

Best regards,
Laurin

[aarun_arasu]

Hi @Laurin37 ,

If you are planning for specific table , please follow the below instruction

Steps:

Navigate to the Designer tab.
Select the table you want to configure.
Click on Table Settings.
You will now see the following screen:

Select the data type you wanted to map.
Once done save and reload the table and make sure we se the full load setting to " Drop and create"  so the the target table gets dropped and recreated with the changes you made.

Regards

Arun

[Laurin37]

Hey @aarun_arasu,

I think I have to give you an example for my problem:

On the left you see me z/OS source-table and on the right my LUW target-table. In the source-table "Name" is a CHAR but in my target-table Qlik Replicate create "Name" as an VARCHAR and not as an CHAR.

Best regards,
Laurin

[aarun_arasu]

Hello @Laurin37 ,

It is working as designed.

As explained earlier, in Qlik Replicate, when data is sourced from DB2 z/OS, a CHAR data type is converted to a String (as seen on the left side) on the Replicate server. Then, it is further converted to a VARCHAR when the table is created on the target database using ODBC.

Here is the data mapping that occurs when you use ODBC as the target:
ODBC Data Types Mapping in Qlik Replicate: https://help.qlik.com/en-US/replicate/May2024/Content/Replicate/Main/ODBC/odbc_data_types_target.htm...

Apologies for missing the data mapping for ODBC earlier. After reviewing it again, I am unsure if the ODBC endpoint can map the data to the CHAR data type on the target, as mentioned in the user guide. Therefore, we may need to manually create the table.

However, let's pause and await more suggestions from the experts in the community.

Regards

Arun

[Laurin37]

Are there any other suggestions? 🙂 

Best regards,
Laurin

[john_wang]

Hello @Laurin37 ,

This is by-design in Qlik Replicate, both CHAR and VARCHAR in source side map to STRING internally, and then convert it back from STRING to VARCHAR in target side (no CHAR type). This is why you can only get VARCHAR in target side.

If you want to have both CHAR and VARCHAR in target tables, you need to define the table manually (out of Qlik Replicate). Unless you want to map both CHAR and VARCHAR to a single type, CHAR, or VARCHAR.

Hope this helps.

John.

[micpage · Accepted solution]

Hello Laurin,

like the Db2 LUW Source Datatype (Supported data types | Qlik Cloud Help) you could Setup 

CHAR (n)  STRING n<=255

VARCHAR (n) STRING n<=32k

with a modified provider Syntax for Db2 with a length_condition:

{
        "name":"DB2_LUW_CHAR",
        "repository.provider_syntax":   {...},
        "data_type_mapping":    [..{								 
          "rep_type":	"kAR_DATA_TYPE_STR",								 
          "provider_data_type":	"CHAR(${LENGTH})",								 
          "has_length_condition":	true,								 
          "from_length":	1,								 
          "to_length":	255						 
        },{								 
          "rep_type":	"kAR_DATA_TYPE_STR",								 
          "provider_data_type":	"VARCHAR(${LENGTH})",								 
          "has_length_condition":	true,								 
          "from_length":	256,								 
          "to_length":	32704						 
       }],"odbc_col_attr": [{...}]
     }
}

Best Regards,

Michael