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
 

 

How to physically sort & save records in a DB2 table ?

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

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Sat Jan 31, 2009 5:33 pm    Post subject: How to physically sort & save records in a DB2 table ?
Reply with quote

Hi,

I'm using CA-Platinum RC/update to insert new records(using repeat command and changing key values) and I could sucessfully commit my changes. Now, the inserted records are physically stored randomly and not in a sorted order.

I want the below EMP_ID columns to be sorted and saved in descending order i.e. Change the fetch order and save in descending order in table for selected records (note - I tried SORT EMP_ID D command but it is not getting saved in the sorted manner)


RUEDITC R11.5 ----- RC/Edit: Searched Column Mode ---- 09/01/31 05:34:54
COMMAND ===> SCROLL ===> CSR
For Table => Row number=> 1 OF 11947
Edit Mode => C Max Char => 070
SSID: DSNT ---------------------------------------------------------- USERID
OPT S EMP_ID PARTITION_NBR TEST_ID REC_ID
___ 190365 59373 0 0
___ 190375 59373 0 0
___ 190366 59373 0 0
___ 190368 59373 0 0
___ 190369 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
___ 190376 59373 0 0
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


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

PostPosted: Sat Jan 31, 2009 8:11 pm    Post subject:
Reply with quote

Hello,
Quote:
stored randomly and not in a sorted order.
You want to say that data in DB2 table should be in some sort-order, 'am not sure, how does it benefit?
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Feb 02, 2009 8:59 am    Post subject:
Reply with quote

Quote:
Now, the inserted records are physically stored randomly and not in a sorted order.


I don't think you have control over how the rows are stored in DB2.
We can retrieve the columns in sorted order but not save in sorted order. icon_rolleyes.gif
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: Mon Feb 02, 2009 9:19 am    Post subject:
Reply with quote

Hello,

Quote:
I don't think you have control over how the rows are stored in DB2.
Yup.

Even if you could store the rows in some order, why do so? Think of the problems this would cause for insert and update activities.

One of the rules of sql is that unless an ORDER BY is used in a SELECT, the sequence of retrieved rows will be unpredictable. If you need descending sequence for some process, specify this in the query.
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


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

PostPosted: Mon Feb 02, 2009 12:54 pm    Post subject:
Reply with quote

or may be your requirement is totally different & what you posted, you thnk, is a way to achieve it. If you post the actual requirement I believe some one will be around and there can be alternative solution..
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Mon Feb 02, 2009 9:18 pm    Post subject:
Reply with quote

Thank you all for your response!

I just wanted to test ORDER BY DESC clause(Records satisfying my test condition criteria are in ASC order in production, I tried loading from production but still it retains the same order,so I need to change the order for my testing).

Do we have control to physically position the records in a table using move,insert or other update actions thru RC/Update or file-aid for DB2?

Or by sorting the unloaded file and loading, will it load in the same sequence?

Jag.
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Mon Feb 02, 2009 9:33 pm    Post subject:
Reply with quote

Sorry a small correction, I wanted to test ORDER BY ASC not descending.
Back to top
View user's profile Send private message
Bharath Bhat

Active User


Joined: 20 Mar 2008
Posts: 283
Location: chennai

PostPosted: Mon Feb 02, 2009 10:26 pm    Post subject:
Reply with quote

Quote:
Records satisfying my test condition criteria are in ASC order in production, I tried loading from production but still it retains the same order,so I need to change the order for my testing


How can it retain the same order? As the folks have mentioned over and over again, There is NO defined order in which data is or can be stored in DB2 (relational database).

If you wanted to test order by clause, I'd suggest you go ahead without bothering about how the rows are stored. May be a better thing to bother about is the manner in which they are returned. icon_cool.gif
Back to top
View user's profile Send private message
itjagadesh

New User


Joined: 05 Dec 2006
Posts: 89
Location: chennai

PostPosted: Tue Feb 03, 2009 11:04 am    Post subject:
Reply with quote

Thanks
Bharath
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 Innovations in Your Mainframe Sort Arun Raj PC Guides & IT News 4 Wed Mar 15, 2017 10:38 pm
No new posts SORT VSAM file with each field one by... maxsubrat DFSORT/ICETOOL 6 Tue Mar 14, 2017 1:07 pm
No new posts SORT requirement sjiraga COBOL Programming 6 Thu Mar 09, 2017 9:11 pm
No new posts Join giving more records than expected Danielle.Filteau SYNCSORT 2 Thu Mar 09, 2017 3:28 am
No new posts TSO or command line utility to genera... kishpra JCL & VSAM 3 Thu Mar 09, 2017 1:11 am


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