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

Replicating datatype varchar (8000) in Batch Optimized Apply mode

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

Labels (3)
2 Solutions

Accepted Solutions
john_wang
Support
Support

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.

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

lyka
Support
Support

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

View solution in original post

6 Replies
Steve_Nguyen
Support
Support

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 ?

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
sergsyb
Contributor III
Contributor III
Author

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

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.

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

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

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

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!
lyka
Support
Support

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