IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Arrangement of UNLOADED DATA


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sprikitik

New User


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

PostPosted: Tue Apr 30, 2013 1:45 am
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

Superior Member


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

PostPosted: Tue Apr 30, 2013 9:45 am
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

Superior Member


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

PostPosted: Tue Apr 30, 2013 9:46 am
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: 61
Location: Makati City, Philippines

PostPosted: Tue Apr 30, 2013 11:46 am
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

Global Moderator


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

PostPosted: Tue Apr 30, 2013 11:53 am
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: 61
Location: Makati City, Philippines

PostPosted: Tue Apr 30, 2013 11:59 am
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

Global Moderator


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

PostPosted: Tue Apr 30, 2013 12:02 pm
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

Superior Member


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

PostPosted: Tue Apr 30, 2013 4:29 pm
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

Moderator Emeritus


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

PostPosted: Tue Apr 30, 2013 7:29 pm
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
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

Moderator Emeritus


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

PostPosted: Tue May 07, 2013 7:12 pm
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
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 View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Data set Rec-Cnt and Byte-Cnt Testing & Performance 2
No new posts SCOPE PENDING option -check data DB2 2
No new posts Check data with Exception Table DB2 0
No new posts JCL EXEC PARM data in C Java & MQSeries 2
This topic is locked: you cannot edit posts or make replies. Automation need help in sorting the data DFSORT/ICETOOL 38
Search our Forums:

Back to Top