Skip to Content
0

SAP IQ 16 ROWID Allocation

Apr 05 at 09:18 AM

58

avatar image
Former Member

Hi,

I am performing a data migration from IQ 15.4 to IQ 16.1 sp02 pl04.

In my IQ 15 table, the ROWID is perfectly sequential i.e. the rowcount matches with max(rowid) consistently.

When data is migrated to my IQ 16.1 table, the ROWID is not sequential and the rowcount does not match with the max(rowid) even though the rowcounts are matching on both systems. Furthermore, there are always huge gaps in the rowid allocation in IQ 16.1 when the rowcount exceeds a certain amount - why is this happening?

Why does this behavioral difference between the mentioned IQ engines matter to us? When performing data validation comparison using the hash md5 function (between IQ 15 table and IQ 16 table), I get a mismatching checksum value. This is because the ROWID on my IQ 16 table contains huge gaps and due to a different rowid being allocated to rows, the order of those row values is changed and therefore the checksum on the list of (ordered vs non-ordered) values is obviously mismatching.

I can understand if the internal rowid is different but this is affecting the order of the rows in my IQ 16 table where there is no primary key or defined order and so therefore I cannot rely on the hash md5 function to validate data for comparison purposes.

Any help, or thoughts on this will be most appreciated.

Regards,

Asif.

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

avatar image
Former Member Apr 18 at 11:15 AM
1

Hi Kirby,

I understand your explanation on why the ROWID is not sequential in IQ 16, but this behavior is not found in IQ 15.x.

Furthermore, ROWID determines the internal order of the data and I need this order to be the same between source (IQ 15) and target (IQ 16). The LIMIT clause actually forces the loading engine to maintain the same order by utilizing a single thread to perform the load.

I have done some performance tests, and it doesn't impact too much on performance, so i'll be adding the LIMIT clause to my load scripts.

Thanks for your help.

Asif.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

The behaviour of ROWID has not changed between SAP Sybase IQ 15 and SAP IQ 16, but parallelism has increased in many ways.

You are likely now seeing the results of the speed improvements that were implemented by using parallel inserts in most instances. SAP Sybase IQ 15 used parallelism much less, and therefore ROWIDs were more likely to be sequential.

The LIMIT clause is a good way to force single-threaded loads at the expense of a lot of performance possibilities, especially for larger loads.

I would strongly encourage you not to rely on row order. No DBMS will guarantee row order outside of a sorted query. SAP Sybase 15's sequential ROWIDs were a coincidental side-effect, not a feature.

0
Kirby Gehman
Apr 06 at 04:33 PM
0

Hi Asif,

If there is no ORDER BY clause, SAP IQ can't guarantee the order of rows in a query output or a table.

There are a few reasons why the ROWID won't be sequential and there are gaps, but the most likely is parallel data inserts. SAP IQ will assign a block of ROWIDs to each thread so there are no collisions. This makes inserts into the table much faster.

ROWID is not intended to be used as a primary key or specific data identifier, and it is not guaranteed to be sequential or gap-free, and matching a specific ROWID to a specific row of data shouldn't be part of data integrity checks.

--Kirby

Share
10 |10000 characters needed characters left characters exceeded
avatar image
Former Member Apr 18 at 11:10 AM
0

OK - so using the LIMIT clause in the LOAD TABLE statement in IQ 16 causes the engine to load the rows in the same order and prevent gaps in the ROWID from occurring.

Regards,

Asif.

Share
10 |10000 characters needed characters left characters exceeded