topic Re: Mapping SQL Server View as source for replicate server in Qlik Replicate

Mapping SQL Server View as source for replicate server

JAfonso — Mon, 06 Feb 2023 15:25:38 GMT

Hello,

when I try to select Tables/views in a task from an Sql server source I only see tables and can´t see a view that was created.

Can anyone advise?

Re: Mapping SQL Server View as source for replicate server

Dineshan — Mon, 06 Feb 2023 15:29:16 GMT

Hello @JAfonso,

You will see views only on full load task. You will see the option to select views when you choose table selection.

Thank you,

Dinesh

Re: Mapping SQL Server View as source for replicate server

SwathiPulagam — Mon, 06 Feb 2023 15:30:00 GMT

Hi @JAfonso ,

Turn on just the Full Load option, then click on Table selection, and you should be able to see the views.

Thanks,

Swathi

Re: Mapping SQL Server View as source for replicate server

JAfonso — Mon, 06 Feb 2023 15:42:35 GMT

Hi guys. thank you for the fast answers.

My issue is the following we have a huge table with Change Processing working on it.

We had the need to do a full load but it is taking forever since it dies frequently dur to timeouts.

My idea was to create a view only with the records of the source table from 2023 (the older ones are not subject to change) and do a full load. After that I would reset the source back to the big table and Change processing would take over

For the older records I would simply do insert to target select from source.

Can this work?

Thanks.

Re: Mapping SQL Server View as source for replicate server

SwathiPulagam — Mon, 06 Feb 2023 17:26:11 GMT

Hi @JAfonso ,

Using Passthru filter option you can load 2023 data and this option is available during Full Load. Once the Full Load is completed you can enable CDC to process.

Below is the link for your reference:

https://community.qlik.com/t5/Official-Support-Articles/Filter-for-last-90-days-of-data-in-Qlik-Replicate/ta-p/1880761

Thanks,

Swathi

Re: Mapping SQL Server View as source for replicate server

JAfonso — Mon, 06 Feb 2023 17:30:40 GMT

Hi @SwathiPulagam , thank you for the advise. I am giving it another try as is and if it fails again I will do as advised. Thanks and regards

Re: Mapping SQL Server View as source for replicate server

SwathiPulagam — Mon, 06 Feb 2023 17:32:41 GMT

Sure @JAfonso .

Please keep us posted with your results.

Thanks,

Swathi

Re: Mapping SQL Server View as source for replicate server

JAfonso — Mon, 06 Feb 2023 17:53:34 GMT

I dont have passthrough enabled. If I enable it all other tasks and tables will work fine as before?

Re: Mapping SQL Server View as source for replicate server

Dana_Baldwin — Mon, 06 Feb 2023 18:10:25 GMT

Hi @JAfonso

Yes, that is correct - it only affects tasks that have a pass through filter defined.

Thanks,

Dana

Re: Mapping SQL Server View as source for replicate server

SwathiPulagam — Mon, 06 Feb 2023 18:11:04 GMT

@JAfonso Passthrough filter will be enabled on the server level and if you want to use that specific filter then you can use it in a specific task\table. Not all other tasks\tables that are not going to use this filter not affected.

Thanks,

Swathi

Re: Mapping SQL Server View as source for replicate server

Michael_Litz — Mon, 06 Feb 2023 18:42:26 GMT

Hi @JAfonso ,

I am posting a link to an article about a Full Load / Split CDC task pair. It discusses some potential issues with this type of approach and how to mitigate them.

https://community.qlik.com/t5/Official-Support-Articles/Qlik-Replicate-Full-Load-and-CDC-Split-Task-Considerations/ta-p/1871152

I hope this helps out.

Thanks,
Michael

Re: Mapping SQL Server View as source for replicate server

JAfonso — Tue, 07 Feb 2023 12:55:34 GMT

Hi everyone,

the reason for trying to use the view is to workaround to a contnued timeout isssue in a full load.
Here is my plan, I would appreciate your comments and advise if you think this can work.

Scenario:
- we have multiple source systems (more than 10), some are Oracle some are SQL Server all using Replicate for Change Processing
- the table that is causing the issue is the biggest of 40 tables from an SQL source
- target system is for all tasks is an Oraclle Database

Issue:
due to the size of the table full load never ends (timeout errors occur and the load process restarts from the begining)
The error we get gives 2 messages:
Error1
Stream component 'st_2_S_SOARIAN_HBA_PRD_SQLS' terminated
Stream component failed at subtask 2, component st_2_S_SOARIAN_HBA_PRD_SQLS
Error executing source loop
Endpoint is disconnected

Error2
RetCode: SQL_ERROR SqlState: HYT00 NativeError: 0 Message: [Microsoft][ODBC Driver 17 for SQL Server]Query timeout expired

Assumption:
- the critical info we need is for data no more than 1-2 months old but the source table has more than 1.100.000.000 records (data since 2012)
- Old data is unchanged so change processing is not needed on that data

Solution A:
1- create a view in the source only with data from this year
2- run full load from this view into the current target table
3- keep change processing working based on the view (does this work?)
4- feed the oldest data by chunks using insert select with database llink from target to source

Solution B:
1- Perform the full load filtering the data from the source data
2- keep change processing working based on the filtered table
3- feed the oldest data by chunks using insert select with database llink from target to source

Thanks in advance if you have read this far!!!

Re: Mapping SQL Server View as source for replicate server

JAfonso — Wed, 08 Feb 2023 08:38:14 GMT

Thank you @Michael_Litz , the issue we are facing is not actually splitting Full Load and CDC but doing full load by chunks: the table is huge and each times there is a failure it truncates target and restarts.

Any ideas on how to address this?

Thanks

Re: Mapping SQL Server View as source for replicate server

john_wang — Sat, 11 Feb 2023 04:36:57 GMT

Hello @JAfonso ,

Sounds like huge rows number leads troubles. Error1 caused by TCP/IP long time connection; Error2 caused by some specific query timeout. To shorten the full load time and reduce the cached change records:

1. As you mentioned, use filter;

2. Run Full Load ONLY task to pass the unchanged old data to target by dedicated task(s); And use Parallel Load to speed up the transfer if network bandwidth is sufficient.

3. We may transfer unchanged data to different temporary table(s) in target, then merge the temporary table(s) into target table in off-peak time, before the CDC task startup.

4. We'd like to suggest PS team engaged as this not an easy performance tuning job, and need to solve the various errors during the troubleshooting.

BTW, regarding the question of "keep change processing working based on the view (does this work?)", the answer is NO, there is an explanation in my comment SQL Server does not record VIEW's data into TLOG.

Hope this helps.

Regards,

John.

Re: Mapping SQL Server View as source for replicate server

JAfonso — Mon, 13 Feb 2023 09:22:34 GMT

Hi @john_wang , Thanks a lot for your input.

Regarding the filter part:

Can I do the filtering in the source with views? Likethis:

Step 1-Imagine I create 4 views SQL2023, SQL 2022, SQL2021, SQL2020 and I full load 4 equivalent target tables in Oracle: ORA23,ORA22,ORA21, ORA20

Step 2- I Truncate the current Target Table and merge the data from the 4 ORA tables.

Step3- I turn on CDC on the Target table and it starts sync

In case the above is possible Any issue if later on I merge even more ancient data to the target table? (e.g. SQL2019,...)

Thanks again.

Regards,

Joao

Re: Mapping SQL Server View as source for replicate server

john_wang — Wed, 15 Feb 2023 14:37:33 GMT

Hello @JAfonso ,

That should be fine. BTW, I think it's better to use partition table (eg 1 partition for 1 year data) in Oracle DB for easy management and better performance; transfer the 'history' data prior to 'change' data to avoid the UPDATE/DELETE cannot find the target rows.

Good Luck,

John.

Re: Mapping SQL Server View as source for replicate server

JAfonso — Wed, 15 Feb 2023 14:56:59 GMT

Hi John, we are thinking about partitioning, yes.

The idea is to start with most recent data (the one that is bond to be changed) and start cdc asap (500.000 records inserted7updated per day) than we had the hystory dat that we know is not updated while users are using the current up to date data.

Thank you John and all for your very useful advise.