topic Re: Task full load, records loaded in change sequence? in Qlik Replicate

Task full load, records loaded in change sequence?

serge-bouschet — Mon, 04 Apr 2022 17:05:37 GMT

During a full load run, is the query executed by Qlik ordered? For example if a table has a sequence, is it guaranteed to get all records loaded in the order of the sequence. What determine the load sequence in the target table and is there any way to influence this in the configuration?

Re: Task full load, records loaded in change sequence?

Steve_Nguyen — Mon, 04 Apr 2022 18:17:50 GMT

@serge-bouschet ,

this is all depend on your source endpoint, what is the source endpoint in question ?

example, for SQL source , Replicate does a select * from the table.

Re: Task full load, records loaded in change sequence?

serge-bouschet — Mon, 04 Apr 2022 19:07:21 GMT

Hi Steve, yes it's a MS-SQL database. Is there a way to change the behaviour?

Re: Task full load, records loaded in change sequence?

Steve_Nguyen — Mon, 04 Apr 2022 19:57:51 GMT

if it's a clustered primary key, then that's the order you will get the rows if doing a select * from the table

Re: Task full load, records loaded in change sequence?

Steve_Nguyen — Mon, 04 Apr 2022 20:09:36 GMT

you can see this article as well:

https://community.qlik.com/t5/Qlik-Replicate/Duplicate-records-with-Full-Load-and-Store-Changes-on-a-task/m-p/1897456#M1929

Re: Task full load, records loaded in change sequence?

PGN — Tue, 05 Apr 2022 11:45:34 GMT

That's an incorrect assumption. There's no guarantee that select * will order any rows without an order by.

Re: Task full load, records loaded in change sequence?

serge-bouschet — Wed, 06 Apr 2022 14:16:44 GMT

Thanks for your input everyone.

There seems to be disagreement on the expected behaviour, should I log a support question to get this resolved @Steve_Nguyen ?

Re: Task full load, records loaded in change sequence?

Steve_Nguyen — Wed, 06 Apr 2022 14:36:55 GMT

@serge-bouschet best to open support ticket, for confirmation, in term of source and target for full load.

Re: Task full load, records loaded in change sequence?

Heinvandenheuvel — Wed, 06 Apr 2022 21:23:38 GMT

@serge-bouschet create a ticket as you see fit, but it may be easier to just look for yourself.

Set the task logging to SOURCE_UNLOAD TRACE, run a load, and look:

You'll see the default command used is a simple "SELECT <columnlist> FROM <table>"

As per @PGN , this does NOT garantuee PK sort order allthough MS SQL Server appears to always do so.

You _could_ force a sort order by adding a PASSTHRU filter.

The magic to activate this is to hold down <CONTROL> while selecting a table and after that hit filter.

Once you have done one passthru filter for one table, just save and export the tasks and look for the filter to see how to do the same thing for several tables should you decide to do so.

Again, trust but verify. Do check that reptask log with at least SOURCE_UNLOAD TRACE.

I recommend a test task with just one or two table with 10 rows or so. That way you can even go to VERBOSE logging to see maximum details.

Hein.

Re: Task full load, records loaded in change sequence?

serge-bouschet — Fri, 08 Apr 2022 16:39:44 GMT

Thank you!

Support gave me a similar response, however they recommended adding this to filter section:

- in the fullload passthru filter section, please set according to the following syntax:
1=1 ) ORDER BY <pkColumn>,<pkColumn>,<pkColumn> FOR READ ONLY --

the set SOURCE_UNLOAD to Verbose to check query executed.