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
 

 

SELECT result ordered differently in embedded SQL?

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

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Wed Oct 03, 2012 2:28 pm    Post subject: SELECT result ordered differently in embedded SQL?
Reply with quote

Hi, not sure if I should post this under "DB2" or "COBOL", I am posting it here first anyway...

I need to fetch records from DB2 table without any sorting, say I have SQL like below -

Code:
SELECT ACCT_NUMB, RESP_CENTER, SOD_RESP_CENTER, BATCHNUMB FROM MDTACBAS_MO                                       
WHERE BATCHNUMB  IN ('03', '04', '05', '06')
AND (RESP_CENTER ¬= SOD_RESP_CENTER)                     
FOR FETCH ONLY;                   


and I got below results when I ran this SQL in SPUFI -

Code:
ACCT_NUMB    RESP_CENTER  SOD_RESP_CENTER  BATCHNUMB
6001008011     100280002       100280004               06       
6000054578     100280002       100280004               06       


but when I define a CURSOR in COBOL by using the same SQL and fetch the CURSOR, records are not fetched in the same order as what I get from SPUFI, means the record with ACCT_NUMB = '6000054578' is fetched first before the record with ACCT_NUMB = '6001008011'...

anyone has any idea of the reason of the difference?

let me know if I need to provide more details.

many thanks!

Code'd
Back to top
View user's profile Send private message

Bill Woodger

DFSORT Moderator


Joined: 09 Mar 2011
Posts: 7236

PostPosted: Wed Oct 03, 2012 2:41 pm    Post subject: Reply to: SELECT result ordered differently in embedded SQL?
Reply with quote

Your post has been Code'd to preserve spacing to make what you have bothered to format retain that format. You've been here long enough to know you should do this yourself.

It is easier for a person to quickly understand an SQL statement if just a little time is spent formatting it nicely. You only even need to do this once, then copy and "past" (of some variety) and all your SQL looks nice and is consistently formatted. The computer doesn't mind what valid SQL "looks like", so do it for people.
Back to top
View user's profile Send private message
Nic Clouston

Global Moderator


Joined: 10 May 2007
Posts: 1715
Location: UK

PostPosted: Wed Oct 03, 2012 2:49 pm    Post subject:
Reply with quote

To answer your question, from posts I have read over the years, and you should have as well, the sequence of presentation is only guaranteed if you use ORDER BY.
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10203
Location: italy

PostPosted: Wed Oct 03, 2012 2:49 pm    Post subject: Reply to: SELECT result ordered differently in embedded SQL?
Reply with quote

the DB2 manuals are clear about the fact that when no ORDER BY clause is specified
the <sequence> of the rows returned is unpredictable
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Wed Oct 03, 2012 3:07 pm    Post subject:
Reply with quote

Hi SuperTramppp,

Interesting. Check the Explain Information in the plan_table, for the query in the package as well as query you ran in SPUFI, basically SPUFI executes query dynamically & Cobol is for static SQL.

What is the DEGREE option in the bind have you specified ?

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

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Wed Oct 03, 2012 3:11 pm    Post subject: Re: Reply to: SELECT result ordered differently in embedded
Reply with quote

Bill Woodger wrote:
Your post has been Code'd to preserve spacing to make what you have bothered to format retain that format. You've been here long enough to know you should do this yourself.

It is easier for a person to quickly understand an SQL statement if just a little time is spent formatting it nicely. You only even need to do this once, then copy and "past" (of some variety) and all your SQL looks nice and is consistently formatted. The computer doesn't mind what valid SQL "looks like", so do it for people.


sorry for the formatting...

have been here for long time but never posted before, will keep in note.

Thanks.
Back to top
View user's profile Send private message
SuperTramppp

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Wed Oct 03, 2012 3:18 pm    Post subject: Re: Reply to: SELECT result ordered differently in embedded
Reply with quote

enrico-sorichetti wrote:
the DB2 manuals are clear about the fact that when no ORDER BY clause is specified
the <sequence> of the rows returned is unpredictable


yes, unpredicatable was my first guess too, but as per my actual experiences on this case, (I tried SPUFI and CURSOR for more than 10 times), all SPUFI results are in same sequence and all CURSOR results are in same sequence, but SPUFI results and CURSOR results are different from each other, really weird...
Back to top
View user's profile Send private message
SuperTramppp

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Wed Oct 03, 2012 3:25 pm    Post subject:
Reply with quote

sushanth bobby wrote:
Hi SuperTramppp,

Interesting. Check the Explain Information in the plan_table, for the query in the package as well as query you ran in SPUFI, basically SPUFI executes query dynamically & Cobol is for static SQL.

What is the DEGREE option in the bind have you specified ?

Thanks,
Sushanth


Hi Sushanth,

I used DEGREE (ANY).

will try to check the explain information also.

thanks.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7913
Location: Bellevue, IA

PostPosted: Wed Oct 03, 2012 4:18 pm    Post subject:
Reply with quote

Quote:
all SPUFI results are in same sequence and all CURSOR results are in same sequence, but SPUFI results and CURSOR results are different from each other, really weird...
No, not weird -- expected. You are confusing REPEATABILITY with PREDICTABILITY. You change the way you get the data, you change the order the data is presented (and you won't know until you run the query the order of presentation) -- but unless something changes about the table, most likely the same query done in the same way will return the same results each time you run it.
Back to top
View user's profile Send private message
SuperTramppp

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Wed Oct 03, 2012 4:33 pm    Post subject:
Reply with quote

Robert Sample wrote:
Quote:
all SPUFI results are in same sequence and all CURSOR results are in same sequence, but SPUFI results and CURSOR results are different from each other, really weird...
No, not weird -- expected. You are confusing REPEATABILITY with PREDICTABILITY. You change the way you get the data, you change the order the data is presented (and you won't know until you run the query the order of presentation) -- but unless something changes about the table, most likely the same query done in the same way will return the same results each time you run it.


that makes sense! what kind of changes I can do against the table to make it present the sequence differently?

will reorg or runstats do?

thanks!
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: Wed Oct 03, 2012 9:04 pm    Post subject:
Reply with quote

Hello,

To guarantee the sequence of the results (SPUFI or within a program) specify an ORDER BY to force the same sequence. There is no good reason to fiddle with the table to try to trick the system into presenting data in a different sequence. . .

If you just let things go as is, "things" may happen that cause the optimizer to process differently. I have never had a user who would accept the order of the data they see magically changing sequence from one run to another. . .
Back to top
View user's profile Send private message
SuperTramppp

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Thu Oct 04, 2012 7:44 am    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

To guarantee the sequence of the results (SPUFI or within a program) specify an ORDER BY to force the same sequence. There is no good reason to fiddle with the table to try to trick the system into presenting data in a different sequence. . .

If you just let things go as is, "things" may happen that cause the optimizer to process differently. I have never had a user who would accept the order of the data they see magically changing sequence from one run to another. . .


totally agree with you! but I need the query result to be in a different sequence for testing purpose...
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Oct 04, 2012 7:49 am    Post subject:
Reply with quote

Quote:
but I need the query result to be in a different sequence for testing purpose


could you explain that, please?
Back to top
View user's profile Send private message
SuperTramppp

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Thu Oct 04, 2012 8:52 am    Post subject:
Reply with quote

dbzTHEdinosauer wrote:
Quote:
but I need the query result to be in a different sequence for testing purpose


could you explain that, please?


I am doing fine-tuning of this SQL, I want to add ORDER BY into this SQL so I need to prove the result sequence is unpredictable without ORDER BY, but now the query result of the SQL without ORDER BY is the same as the one with ORDER BY... so I want to know if I can do something against the table to make the query result presents in different sequence...
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Oct 04, 2012 11:39 am    Post subject:
Reply with quote

Hi SuperTramppp,

Check how the clustering indexes are defined in the table, they determine how the rows are physically ordered in the table.

Quote:
I want to add ORDER BY into this SQL so I need to prove the result sequence is unpredictable without ORDER BY
You need to get lots of rows inserted/load resumed randomly and get a bad clustering raito and then do a select and test.

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

New User


Joined: 29 Apr 2009
Posts: 8
Location: China

PostPosted: Thu Oct 04, 2012 2:18 pm    Post subject:
Reply with quote

sushanth bobby wrote:
Hi SuperTramppp,

Check how the clustering indexes are defined in the table, they determine how the rows are physically ordered in the table.

Quote:
I want to add ORDER BY into this SQL so I need to prove the result sequence is unpredictable without ORDER BY
You need to get lots of rows inserted/load resumed randomly and get a bad clustering raito and then do a select and test.

Thanks,
Sushanth


tried to reorg the table and index, got what I wanted now, thanks to all lovely guys!
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Thu Oct 04, 2012 3:13 pm    Post subject:
Reply with quote

FYI: (unpredicted) sequence is dependant on the accesspath. DB2 will give the rows in the sequence it encounters them:
if it is using an index, it will show them in the order of the index.
if it uses a tablescan, it will show them in the physical sequence of the rows/pages. This location is determined by the placing-algorithm of DB2 which basically is : as closely possible according to the clustering index. a Reorg (of the TS) will reorder/replace the rows physically according to the clustering index.

So a different sequence can be caused by :
- a different accespath (Spufi vs embedded)
- a reorg (if the accesspath is TS-scan)
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: Thu Oct 04, 2012 6:51 pm    Post subject:
Reply with quote

Hello,

Quote:
but I need the query result to be in a different sequence for testing purpose...
Someone in your organization may believe this, but there is NO business reason for this. . . It is neither a test nor a verification of the process.

One of the first rules of getting data via sql is in order to gurantee some particular sequence is an ORDER BY must be specified.

A given query may run the same way 100,000 times and then one day return the data in a different sequence - causing no end of havok (abends, data corruption, confision, etc). Completely unacceptable to then say "We told you this could happen".
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Thu Oct 04, 2012 7:09 pm    Post subject:
Reply with quote

probably some petty little party aparatchik's kid wrote the sql
and this kind of test is required, in this case, to get it fixed.
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 Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
This topic is locked: you cannot edit posts or make replies. Rexx to create GDG by scanning JJ result krish.deepu CLIST & REXX 5 Tue Oct 25, 2016 5:32 pm
No new posts cobol embedded sql MS_Developer COBOL Programming 7 Fri Oct 14, 2016 2:39 pm
This topic is locked: you cannot edit posts or make replies. How to run and see the result of DFH$... lind sh CICS 2 Wed Jul 27, 2016 7:04 pm
No new posts Select first 2 group data vice_versa DFSORT/ICETOOL 10 Mon Jun 13, 2016 2:21 pm


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