The following source column(s) contain data that exceed(s) the size limit of the corresponding target column

[Leandro_Medeiros]

Hi everyone,

I´m facing a new issue with QLIK Replicate because the Task does not replicate the column size from the source to the target table correctly. 

ERROR in the attrep_apply_exceptions:

The following source column(s) contain data that exceed(s) the size limit of the corresponding target column:
source column "TRMC80" is 32 bytes while target column "TRMC80" is 8 bytes;
source column "MOTY80" is 16 bytes while target column "MOTY80" is 4 bytes;
source column "HDID80" is 48 bytes while target column "HDID80" is 12 bytes;

Endpoints types in use:

Souce: IBM DB2 for LUW

Target: Microsoft SQL Server 2022 (160 Compatibility)  

Any help will be very appreciated.

Thank you !!

Best Rgds,

Leandro Medeiros

[john_wang · Accepted solution]

Hello Leandro Medeiros, @Leandro_Medeiros 

It's difficult to determine the exact cause at this point. We suggest the following steps:

1. Compare the column lengths on both the source and target sides to ensure the target can accommodate the source data.

2. Check whether the target table was created by Qlik Replicate. If not, consider increasing the column sizes where needed.

3. If the issue persists, please open a support ticket — our support team will be happy to assist you further.

Regards,

John.

[Leandro_Medeiros]

Hi @john_wang ,

Sure, the support will be the best way for this matter.

Just to add more info about the issue:
On source table the field is TRMC80[CHAR(8 CODEUNITS32)] 
and QLIK create it as STRING(8) on the target table.

But after the Full Load with DROP / CREATE the QLIK generate the warning and recommend to be 32bytes Unicode Encoding.

Does QLIK have any option to force 32-bits Unicode Enconding to the CREATE on target ?

Thanks again.

Regards,

Leandro Medeiros

[john_wang · Accepted solution]

Hello Leandro Medeiros, @Leandro_Medeiros 

Yes, of course. it's easy to do in Qlik Replicate, please have a look at the article:

How to double the column size in the target database

Hope it helps.

John.

[benwinkeler]

Hi, I have a very similar problem that Qlik Replicate is not recognizing that my source column is defined as VARCHAR(25 CODEUNITS32) and will alter / create my target tables with just a length of 25.

Are there any settings that Qlik recognizes the CODEUNITS32 definition on the source to allow to create the correct size on target?

Unfortunately just doing a hardcoded *4 does not work as some columns don't need it ... (and would become too long if the length would be multiplied).

I also don't want my target column to be defined as length *4... I need the target to be the same as the source with CODEUNITS32.

Endpoints types in use:

Souce: IBM DB2 for LUW

Target: ODBC (IBM DB2 for LUW)

[john_wang]

Hello @benwinkeler ,

Thanks for reaching out to Qlik Community.

In Db2 LUW, string length semantics can be defined in three different ways:

• OCTETS             – length is measured in bytes.
• CODEUNITS16 – length is measured in 16-bit code units (UCS-2 / UTF-16).
• CODEUNITS32 – length is measured in 32-bit code units (UTF-32).

By default, Qlik Replicate uses OCTETS, so VARCHAR(25) is equivalent to VARCHAR(25 OCTETS). This is also the most commonly used definition.

Replicate can map a source VARCHAR(nn CODEUNITS32) to the same type in the target DB2 LUW database. However, due to current product limitations, all string data types in the endpoint will be mapped to VARCHAR(nn CODEUNITS32)—regardless of whether the source was defined as OCTETS, CODEUNITS16, or CODEUNITS32. Fine-grained control over string length semantics is not yet available. 

Workarounds:

• Create the target table manually (or via script) outside of Qlik Replicate, and
• Configure Target Table Preparation as “Truncate before loading”.

If this is a feature you’d like supported in future releases, please submit a feature request, which will be reviewed by our Product Management team.

Regards,

John.

[benwinkeler]

Could you perhaps indicate if any specific settings are needed for Replicate to map a source VARCHAR(nn CODEUNITS32) to target in the same type?

If I follow the manual "Replicating 4-byte UTF8 emojis" and use a global transform to set STRING to WSTRING, I end up with VARGRAPHIC(nn CODEUNITS16).

If I don't apply a transformation, it remains VARCHAR(nn OCTETS).

[Leandro_Medeiros]

Hi @benwinkeler ,

We have fixed the transformations creating two Global Rules.

First we converted DATA_TYPE and multiplying the size by 4: 

$AR_M_SOURCE_DATATYPE_LENGTH * 4

The second replaced the value applying the TRIM:

trim($AR_M_SOURCE_COLUMN_DATA)

If you did not TRIM the target field will be filled with spaces for the rest of string.    

Task Global Rules: 

I hope that this trick can help you.

Best Regards,

Leandro 

[Leandro_Medeiros]

Hi,

When CODEUNITS32 is transformed to VARCHAR the column size should be 4 times bigger.
I´ve tried to change the Collation for the target dabatase but did not result in a good way.

My suggestion is to create two Global Rules:

The first to convert and increase the column target size and the second to apply the TRIM.

You will need to use the trim to clean the "END Spaces".

Best Regards,

Leandro 

[benwinkeler]

Thanks Leandro for the idea, will keep it as a backup solution.

@john_wang , can you still share the method how Qlik could perform what you mentioned ? Is this an internal parameter? 

"However, due to current product limitations, all string data types in the endpoint will be mapped to VARCHAR(nn CODEUNITS32)—regardless of whether the source was defined as OCTETS, CODEUNITS16, or CODEUNITS32. Fine-grained control over string length semantics is not yet available. "