Insert order is missing in the table

Lingam D · New User Joined: 07 Sep 2011 Posts: 8 Location: india

Hi

Using a Cobol program I am inserting records into a DB2 table. The records in the input file are in sorted order(on key). the primary key of the table is the combination of five columns, and it has the clustering index on these columns. After inserting the records i found that the table rows are not sorted based on the key(checked with PRINCETON tool). I traced the program using XPEDITOR, its inseting in the correct order.
What would be the reason, please help. Thanks.

Akatsukami · Posted: Tue Dec 04, 2012 3:55 pm

The reason is that DB2 does not guarantee retrieval of the rows in any particular order unless a ORDER BY clause is specified in the query.

Pandora-Box · Posted: Tue Dec 04, 2012 3:57 pm

Is this the first time insert happening to the table??

Lingam D · New User Joined: 07 Sep 2011 Posts: 8 Location: india

Yes. this is the first time insert is happening to the table.

don.leahy · Posted: Tue Dec 04, 2012 6:37 pm

DB2 does not guarantee that rows will be inserted in the "proper" sequence. It may work out that way sometimes, but that is just a coincidence. You cannot rely on this behaviour.

As Akatsukami said, the only time that DB2 guarantees the order is when the rows are retrieved via a query that uses ORDER BY.

dick scherrer · Posted: Tue Dec 04, 2012 9:30 pm

Hello,

As others have mentioned, the only to be sure of the sequence the rows are returned is to use an ORDER BY.

If you run the same query with no order by over a few weeks, the sequence of the data returned may change from run to run.