Task full load, records loaded in change sequence?

[serge-bouschet]

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?

[Steve_Nguyen]

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

[serge-bouschet]

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

[Steve_Nguyen]

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

[Steve_Nguyen]

you can see this article as well:

https://community.qlik.com/t5/Qlik-Replicate/Duplicate-records-with-Full-Load-and-Store-Changes-on-a...

[PGN]

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

[serge-bouschet]

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 ?

[Steve_Nguyen]

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

[Heinvandenheuvel · Accepted solution]

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

[serge-bouschet · Accepted solution]

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.