|
View previous topic :: View next topic
|
| Author |
Message |
sprikitik
New User
Joined: 29 Jan 2007
Posts: 61
Location: Makati City, Philippines
|
|
|
|
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 |
|
 |
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| 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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| A second thought - why do you ask this? |
|
| Back to top |
|
|
sprikitik
New User
Joined: 29 Jan 2007
Posts: 61
Location: Makati City, Philippines
|
|
|
|
| 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 |
|
|
Pandora-Box
Global Moderator
.jpg)
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
| Since you are going for DSNTIAUL you can adjust the query with order by PK1 |
|
| Back to top |
|
|
sprikitik
New User
Joined: 29 Jan 2007
Posts: 61
Location: Makati City, Philippines
|
|
|
|
| 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 |
|
|
Pandora-Box
Global Moderator
Joined: 07 Sep 2006
Posts: 1592
Location: Andromeda Galaxy
|
|
|
|
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 |
|
|
Anuj Dhawan
Superior Member
Joined: 22 Apr 2006
Posts: 6248
Location: Mumbai, India
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
lagneshp
New User
Joined: 15 Aug 2009
Posts: 29
Location: Chennai
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
And if Clustering was Not used . . . ? |
|
| Back to top |
|
|
lagneshp
New User
Joined: 15 Aug 2009
Posts: 29
Location: Chennai
|
|
|
|
| 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 |
|
|
|
|
