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 SQL query to run through list of valu... Ni3-db2 DB2 14 Wed Dec 14, 2016 9:52 am
No new posts Removing Duplicates based on certain ... chandracdac DFSORT/ICETOOL 8 Fri Dec 09, 2016 4:40 am
No new posts Limit duplicate records in the SORT pshongal SYNCSORT 6 Mon Nov 21, 2016 12:54 pm
No new posts How to convert the VBM file to VB or... Sulabh Agrawal JCL & VSAM 4 Fri Nov 18, 2016 1:04 pm
No new posts How to split the records using the am... vnktrrd DFSORT/ICETOOL 24 Fri Oct 28, 2016 7:33 pm


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