topic Re: Qlik replicate not applying the changes in realtime in Qlik Replicate

Qlik replicate not applying the changes in realtime

swaroopchippada — Thu, 07 Mar 2024 07:29:01 GMT

Hi,

We are using qlik replicate with

Source - Sybase

Target - Kafka

and we have selected the below settings while creating the task,

Replication profile: unidirectional

Task options - Full load, Apply changes

After starting the task, Full load is successful. But after that, any new transaction in the DB is not replicated.

if I stop and restart the task by selecting reload target, all the new data is replicated.

Kindly help to resolve this.

Also, one more thing to note, I haven't faced this issue in local. only facing this in our dev environment.

Re: Qlik replicate not applying the changes in realtime

aarun_arasu — Thu, 07 Mar 2024 07:56:28 GMT

Hi @swaroopchippada ,

Thanks for reaching out to Qlik community .

Do you see any errors in the log ?

Is the changes are captured and not applied to target ? or it does not capture the CDC events?

What is the replicate version ?

Regards

Arun

Re: Qlik replicate not applying the changes in realtime

swaroopchippada — Thu, 07 Mar 2024 09:15:03 GMT

Hi Arun,

It does not capture the CDC events.

I don't see any error in the logs. also, I noticed there are no logs on cdc either in my local or dev vm.

but it's working in local.

I'm using QlikReplicate_2023_11_0_Linux_X64

in local i can see, this is getting updated on any new insert. in dev, its always 0 but task is still running.

one more difference is that in local im using windows version.

in dev, we are using qlik replicate linux version

Re: Qlik replicate not applying the changes in realtime

john_wang — Thu, 07 Mar 2024 15:20:34 GMT

Hello @swaroopchippada ,

IN general this is because the table was not CDC enabled, you may check the "Repdef Mode" by:

sp_setreptable kit

where the table name is "kit". Or you can set it by:

sp_setreptable '<tableName>', 'true', owner_on

Hope this helps.

John.

Re: Qlik replicate not applying the changes in realtime

swaroopchippada — Fri, 08 Mar 2024 02:59:04 GMT

Hi John,

we followed below pre-requisite already, but still It does not capture the CDC events.

1) SAP Sybase ASE replication enabled for tables using the sp_setreptable command or privileges to enable it automatically.

Verified - there is an entry for the table.

2) RepAgent must be disabled on the SAP Sybase ASE database.

Verified - RepAgent is disabled.

3) When replicating to SAP Sybase ASE 15.7 installed on a Windows machine configured with a non-Latin language (e.g. Chinese), Qlik Replicate requires Sybase 15.7 SP121 to be installed on the SAP Sybase ASE machine.

This is NA, as Sybase is running on Linux

4) Turn off automatic truncation by running the following command:

sp_dboption mydb, "trunc log on chkpt", false

The above command is already executed on the schema of the rep table.

Re: Qlik replicate not applying the changes in realtime

deepaksahirwar — Fri, 08 Mar 2024 03:30:18 GMT

Dear @swaroopchippada ,

Thank you for reaching out to us in the Qlik Community.

There could be several possible reasons why the changes are not applied in real time to the target. Here are some suggestions that might help you troubleshoot and resolve the problem:

1.Check the Change Processing Mode setting in your task. Make sure it is set to Batch optimized apply, which will commit changes in pre-grouped batches in an efficient way. The alternative mode is Transactional apply, which will commit changes in the order they are committed, i.e sequentially or one-by-one. This mode is not supported for Kafka targets and can cause latency and performance issues.

2.Check the Replicate LOB columns setting in your task. If your source table definition or table definition has LOBs and those LOBs are supported and it is set to unlimited or a large value, this can also affect the change processing speed. You can try to reduce the LOB size limit or exclude the LOB columns from replication.

3.Check the Primary Key of the source table. If there is no primary key on a table you are trying to apply updates to, or if the primary key is changing on the source table, this can also cause the task to switch to one-by-one mode and delay the changes. You can try to add a primary key to the source table or use a surrogate key.

4.Check the Target Apply stage in the Replicate Console. You can see the status and progress of the changes being applied to the target. If you see any errors or warnings, you can click on them to get more details and possible solutions. You can also see if the task is switching to one-by-one mode or if there are any conflicts or connection issues.

I hope this helps you to resolve the issue. Please let me know if you have any other questions or concerns.

If our response has been helpful, please consider clicking "Accept as Solution". This will assist other users in easily finding the answer

Regards,

Deepak

Re: Qlik replicate not applying the changes in realtime

john_wang — Fri, 08 Mar 2024 04:05:06 GMT

Hello @swaroopchippada ,

Thanks for your reminder! I just realized this doc bug: Qlik Replicate requires Sybase 15.7 SP121 to be installed on the SAP Sybase ASE machine. I will report this documentation defect and get it fixed. Thank you!

Qlik Replicate requires SDK 15.7 SP121 or later because of memory leakages bug in lower versions of SAP SDK (I worked with R&D to get this fixed several years ago). The SDK will be installed on Qlik Replicate machine rather than installed on the SAP Sybase ASE machine.

I'm not sure what's the Replicate Version and Sybase ASE version you are running now, please:

1. Use higher privilege account to make sure the problem was not caused by privileges;

2. If the problem persists, please set SOURCE_CAPTURE/SOURCE_UNLOAD to Verbose, recreate the issue and download the Diag Packages. Open a support ticket and attach the article link and Diag Packages to the ticket, we'd like to help you further.

3. Please decrypt the task log files and attach them to support ticket too.

Regards,

John.

Re: Qlik replicate not applying the changes in realtime

swaroopchippada — Fri, 08 Mar 2024 07:15:09 GMT

Hi John and Deepak,

I found a way to make it work. but it seems to be a bug.

I had to restart the application which inserts into Rep table. only after which cdc is working.

Suppose the application is live and running. I create a new task and configure the endpoints and rep tables. Now when I start the task, CDC is not working.

I had to restart the application and then any transaction afterward from the app was replicated.

Also, there is one more issue, Filter is not working,

our requirement is to filter out some transactions based on database user ID.

as per the documentation, for cdc we can access the user id from the header.

We tried to use it, but its not working.

I tried transform feature and then assign the header value to one column and found that its always coming as NULL.

Kindly assist on this

Re: Qlik replicate not applying the changes in realtime

john_wang — Fri, 08 Mar 2024 07:18:41 GMT

Hello @swaroopchippada ,

Personally I do not think 'a bug' but your application does not turn on 'auto-commit' which will hold on the transactions, until you restart it. is my guess reasonable ?

thanks,

John.

Re: Qlik replicate not applying the changes in realtime

swaroopchippada — Fri, 08 Mar 2024 07:32:31 GMT

Hi John,

Sorry, I don't think so. we are using spring boot application. and If it is a holding transaction, then the values should not be reflected in db as well. whereas when I connect to DB (different session) i can see the transaction.

And moreover, CDC works after the application is restarted.

anyway, I have a workaround for this.

Kindly assist on the Filter issue.

Thanks

Re: Qlik replicate not applying the changes in realtime

john_wang — Fri, 08 Mar 2024 15:03:59 GMT

Hello @swaroopchippada ,

Thanks for the feedback.

The header column AR_H_USER works for SAP Sybase ASE for sure, however please take note the Replicate AR_H_USER header column is not supported for database changes performed in auto-commit mode. You need to use the explicit transactions.

I've confirmed that in my labs (Replicate 2023.11 + Sybase ASE 16.0):

1. Turn off auto-commit in "Interactive SQL"

2. Make changes by explicit transactions in "Interactive SQL"

The sample code as below:

begin transaction
insert into moon.dbo.testtime values (16,'abc','23:59:59','23:59:59')
commit

3. Got the transaction owner, "sa" in target DB:

Comparision to the previous line, id=12, which was made by auto-commit, the AR_H_USER is null value. It should work for Kafka target as well.

Hope this helps.

John.

Re: Qlik replicate not applying the changes in realtime

swaroopchippada — Mon, 11 Mar 2024 02:43:28 GMT

@John Thank you so much. its working now

Re: Qlik replicate not applying the changes in realtime

john_wang — Mon, 11 Mar 2024 02:53:59 GMT

Glad to hear that, @swaroopchippada , thank you so much for your great support!