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
 

 

Arrangement of UNLOADED DATA

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

New User


Joined: 29 Jan 2007
Posts: 58
Location: Makati City, Philippines

PostPosted: Tue Apr 30, 2013 1:45 am    Post subject: Arrangement of UNLOADED DATA
Reply with quote

Hi,

If for example i have a table - TABLE1 with 5M records and the table has a unique key PK1. If i unloaded data from the table using "SELECT * FROM TABLE1", wil the unloaded data be sorted by PK1?

Thanks,
Chris
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Apr 30, 2013 9:45 am    Post subject:
Reply with quote

The unique constraint helps to ensure data integrity by preventing unintentional duplication; as such, it has got nothing to do with the sort order of a SELECT. So, I don't think that without an ORDER BY PK1 you'll get them in sort order of PK1.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Apr 30, 2013 9:46 am    Post subject:
Reply with quote

A second thought - why do you ask this?
Back to top
View user's profile Send private message
sprikitik

New User


Joined: 29 Jan 2007
Posts: 58
Location: Makati City, Philippines

PostPosted: Tue Apr 30, 2013 11:46 am    Post subject:
Reply with quote

Anuj Dhawan wrote:
A second thought - why do you ask this?


Thank you Anuj. So the PK does not have anything to do with the sorting order. I asked because i'm converting an unload from a non-ibm utility to dsntiaul and wanted to know what will be the sorting order of unloaded data when no 'order by' was used in the unload.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Tue Apr 30, 2013 11:53 am    Post subject:
Reply with quote

Since you are going for DSNTIAUL you can adjust the query with order by PK1
Back to top
View user's profile Send private message
sprikitik

New User


Joined: 29 Jan 2007
Posts: 58
Location: Makati City, Philippines

PostPosted: Tue Apr 30, 2013 11:59 am    Post subject:
Reply with quote

Pandora-Box wrote:
Since you are going for DSNTIAUL you can adjust the query with order by PK1


The current non-ibm utility does not used 'order by'. when converted to dsntiaul, no order by should be used.
Back to top
View user's profile Send private message
Pandora-Box

Moderator


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

PostPosted: Tue Apr 30, 2013 12:02 pm    Post subject:
Reply with quote

But the non-ibm utility always gives the data in sorted by PK1 without using order by?

If you dont mind saying us the non-ibm utility?
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Tue Apr 30, 2013 4:29 pm    Post subject:
Reply with quote

sprikitik wrote:
So the PK does not have anything to do with the sorting order. I asked because i'm converting an unload from a non-ibm utility to dsntiaul and wanted to know what will be the sorting order of unloaded data when no 'order by' was used in the unload.
A little more explnation on the text-in-bold might help me to understand, what are you trying to accomplish. When the 'unload' is 'ready-to-DSNTIAUL' - what would you do with that 'ready thing'?
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 Apr 30, 2013 7:29 pm    Post subject:
Reply with quote

Hello,

Does the non-IBM utility unload the data in the same sequence every time?

If it does and this is important, you need to cause the output of DSNTIAL to be in the same sequence?
Back to top
View user's profile Send private message
lagneshp

New User


Joined: 15 Aug 2009
Posts: 29
Location: Chennai

PostPosted: Tue May 07, 2013 1:12 pm    Post subject: Re: Arrangement of UNLOADED DATA
Reply with quote

Based on my experience, DB2 will have data stored based on cluster index...
So the data present in DB2 is sorted based on Cluster index(one or more columns) that is created during creation of table. So you need to check with ADM tool and find out Cluster index created for this table.
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 May 07, 2013 7:12 pm    Post subject:
Reply with quote

Hello,

And if Clustering was Not used . . . ?
Back to top
View user's profile Send private message
lagneshp

New User


Joined: 15 Aug 2009
Posts: 29
Location: Chennai

PostPosted: Tue May 07, 2013 7:20 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

And if Clustering was Not used . . . ?


If Clustering was not used, then the only way is to look at the load job(if the records are loaded thru JCL) or to look at the COBOL program input file source (if it is getting inserted through program, in this case little research on input file needs to be done to catch the source)
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 Is the 'prompt' data for the DSLIST p... Willy Jensen TSO/ISPF 2 Tue Dec 06, 2016 4:38 am
This topic is locked: you cannot edit posts or make replies. How to move a long alphanumeric data ... lind sh COBOL Programming 8 Mon Dec 05, 2016 7:51 pm
No new posts ODPP(Optim Data privacy Provider) Iss... Rama kishore IBM Tools 1 Mon Nov 07, 2016 5:46 pm
No new posts Can sending 5 MB data between cobol p... Kevin Vaz CICS 12 Tue Oct 18, 2016 4:50 pm
No new posts output data in CSV Format janmejay COBOL Programming 8 Sat Oct 15, 2016 2:20 pm


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