Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups 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: 1746
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: 1530
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: 642
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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts How to insert a lengthy string havin... vidyaa DB2 7 Thu Aug 25, 2016 5:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us