Replicating datatype varchar (8000) in Batch Optimized Apply mode

[sergsyb]

There is the table with column which has datatype varchar (8000) on source DB (MS SQL Server). When this table replicates to SAP Sybase IQ in Batch Optimized Apply mode (without transformation) a temporary table attrep_changesXXXXXXXX creates for further applying changes to the target (SAP Sybase IQ).

А table attrep_changesXXXXXXXX has columns with max length of varchar equals 2000 (varchar(2000) ) which is less than lenght of column on Source DB– varchar (8000).

Please explain

- Why there is not a column with the same length of varchar as it exists on Source in a table attrep_changesXXXXXXXX?

- In which way we can replicate column with varchar (8000) in Batch Optimized Apply mode without data losses?

Unfortunately I couldn’t find answer on this question in documentation

Thanks

[Steve_Nguyen]

fyi

https://help.qlik.com/en-US/replicate/May2022/Content/Replicate/Main/SAP%20Sybase%20IQ/limitations_s...

• Full LOB mode is not supported.
• Replication of LOBs during Change Processing is not supported in Bulk Apply mode (LOB values are replicated to NULL).

===

what is your task LOBS limit set for 8000 ?

also, if you look at this column in the table transformation , what Replicate show for this column ?

[sergsyb]

Hi Steave

In MS SQL Server datatype VARCHAR(8000)  is not a LOB. It can store up to 8000 bytes on pages in the same way as other datatypes. Please don't confuse it with VARCHAR (max) datatype which can store up to 2Gb. VARCHAR (max) is a LOB. It is stored in different way than varchar (8000).

In the table transformation VARCHAR(8000)  is represented as STRING(8000). Please see attached screenshot.

Limit LOB size  set  to 8KB

[john_wang · Accepted solution]

Hello @sergsyb ,

attrep_changesXXXXXXXX table structure was determined by all the columns of all the tables which were included in the task. if the maximum length of these columns is 2000, then attrep_changesXXXXXXXX will contains such a 2000 column(s). It's reused to cover all the interested tables in the task. Certainly the max length will be 8000 if any one source table column is varchar (8000).

A task log file with SOURCE_CAPTURE/SOURCE_UNLOAD set to Verbose will help us to fully understand how the global temp table attrep_changesXXXXXXXX  was created. Please set the Verbose and re-run the task, then provide the Diag Packages.

And please decrypt log files  on you server then attach the decrypted log files as well (compress them before transfer). Please note to open a support ticket then attach the files (for security reason). Support team will help you on that.

Regards,

John.

[sergsyb]

Thanks John, we will do it as you suggested, but please answer on the last question.

Maybe it is important the problem table where there is an column varchar(8000) is practically static, there are very few changes on it. And all these changes do not affect the problem column varchar(8000) , where all data is empty string '' right now.

So my questinion following - will REPLICATE include definition of column varchar(8000) in temp table attrep_changesXXXXXXXX if there were not changes was done in this column?

[Steve_Nguyen]

@sergsyb

the attrep_Changesxxxx get drop and recreate when task stop and run/resume

so when task run/resume the attrep_changexxx get new metadata. 

[lyka · Accepted solution]

Hello @sergsyb 

To answer your question, " will REPLICATE include definition of column varchar(8000) in temp table attrep_changesXXXXXXXX if there were not changes was done in this column?"

YES - replicate will include all columns when creating the temp table even if there are no changes done on the column.

Thanks

Lyka