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: 1755
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: 1534
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: 650
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 Cobol upgrade - source code missing f... gthmrj IBM Tools 1 Wed Apr 26, 2017 6:04 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm
No new posts Join of more than one table dibyendumandal11111 DB2 6 Mon Feb 13, 2017 11:36 pm


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