Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
Insert order is missing in the table

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
Lingam D

New User


Joined: 07 Sep 2011
Posts: 8
Location: india

PostPosted: Tue Dec 04, 2012 3:44 pm    Post subject: Insert order is missing in the table
Reply with quote

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.
Back to top
View user's profile Send private message

Akatsukami

Global Moderator


Joined: 03 Oct 2009
Posts: 1777
Location: Bloomington, IL

PostPosted: Tue Dec 04, 2012 3:55 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


Joined: 07 Sep 2006
Posts: 1542
Location: Andromeda Galaxy

PostPosted: Tue Dec 04, 2012 3:57 pm    Post subject:
Reply with quote

Is this the first time insert happening to the table??
Back to top
View user's profile Send private message
Lingam D

New User


Joined: 07 Sep 2011
Posts: 8
Location: india

PostPosted: Tue Dec 04, 2012 4:22 pm    Post subject:
Reply with quote

Yes. this is the first time insert is happening to the table.
Back to top
View user's profile Send private message
don.leahy

Active Member


Joined: 06 Jul 2010
Posts: 658
Location: Whitby, ON, Canada

PostPosted: Tue Dec 04, 2012 6:37 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Dec 04, 2012 9:30 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm
No new posts Table(Unicode(Graphic) table) loading... muralikrishnan_new DB2 0 Thu Oct 05, 2017 5:10 pm
No new posts How to insert a value on specific pos... Poha Eater DFSORT/ICETOOL 9 Sun Oct 01, 2017 3:04 am
No new posts Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us