CLOB field is Null in Before-Image in a change table

[MoeE]

Hi,

This is behaviour is present in a direct task and in a logstream task. The source database is Oracle. Targets are SQL Server and Synapse. We've seen the behaviour in both targets.

I am seeing the following behaviour:

1. A record is update in the source.

2. The After-Image has a value for the CLOB field, while the Before-Image is NULL.

I have done some reading and it is vaguely suggested that this may be intentionally how Qlik Replicate functions. However, I have not found any explicit confirmation anywhere that LOB fields in the Before-Image will appear as NULL in the target change table. I've looked through the change table documentation and the Oracle, SQL Server, and Synapse limitations, yet there is no mention of this.

Can someone please confirm whether this behaviour is indeed expected, and whether it can be changed to populate the Before-Image with the data?

Thank you.

Kind Regards,

Mohammed

[DesmondWOO]

Hi @MoeE ,

I believe this is expected behavior. The size of a LOB column can be considerable, and including the before-image would occupy more space. Additionally, since LOB columns are not used for primary keys or unique indexes, having a before-image is not useful. 

Regards,
Desmond

[MoeE]

Hi Desmond,

Thanks for the answer. Is this default behaviour will all source and target endpoints?

Regards,

Mohammed

[MoeE]

Hi Desmond,

I tried switching the endpoints to a SQL Server source and an Oracle target. I was able to see the LOB data in the before image now.

So, is this limitation only for an Oracle source endpoint or SQL Server target? Is there any way to address this behaviour within Qlik Replicate?

Regards,

Mohammed

[DesmondWOO]

Hi @MoeE ,

I tested with SQL Server as both the source and target. The before-image does not contain any values for the TEXT column. Below is my table structure:

CREATE TABLE [dbo].[text01](
[id] [int] NOT NULL PRIMARY KEY,
[c1] [varchar](30) NULL,
[t1] [text] NULL,
[dt2] [datetime2](7) NULL);

CT result:

Could you send me your DDL? I would like to test it in my environment.

Regards,
Desmond

[MoeE]

Hi Desmond,

Sure, my task was from SQL Server to Oracle 19. QR version is November 2024.

CREATE TABLE AdventureWorksLT2019.dbo.CARGOSHIPSTACTEST (
	SHIPID numeric(38,10) NOT NULL,
	SHIPNAME varchar(100) COLLATE Latin1_General_CI_AS NULL,
	CAPACITY numeric(38,10) NULL,
	OWNER varchar(100) COLLATE Latin1_General_CI_AS NULL,
	DESCRIPTION varchar(MAX) COLLATE Latin1_General_CI_AS NULL,
	CONSTRAINT CARGOSHIPSTACTEST_PK PRIMARY KEY (SHIPID)
);

Here were my results. 

Regards,

Mohammed

[MoeE]

Hi Desmond, 

I've tried it using your schema and I did not get the before image of the column. This is interesting 

However with my table I am able to get a before image.

I am not able to get a before-image at all for Oracle as a source. I've even tried John Wang's method of adding an extra column with the $BI__ prefix, however this did not work with Oracle as a source for my CLOB column.

My main objective is to confirm whether this is indeed the intended behaviour for the Oracle source endpoint. Can this be confirmed?

Thanks for the help so far.

Regards,

Mohammed

[DesmondWOO]

Hi @MoeE ,

In my environment, I can retrieve the before-image from varchar(max), but it returns blank for text. To confirm this behavior, please create a support ticket so we can check with R&D.

Regards,
Desmond