|
View previous topic :: View next topic
|
| Author |
Message |
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
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 |
|
 |
Bill Woodger
Moderator Emeritus
Joined: 09 Mar 2011
Posts: 7309
Location: Inside the Matrix
|
|
|
|
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 |
|
|
Nic Clouston
Global Moderator
Joined: 10 May 2007
Posts: 2454
Location: Hampshire, UK
|
|
|
|
| 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 |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10888
Location: italy
|
|
|
|
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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
| 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 |
|
|
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
| 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 |
|
|
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
| 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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8700
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
| 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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
| 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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
| Quote: |
| but I need the query result to be in a different sequence for testing purpose |
could you explain that, please? |
|
| Back to top |
|
|
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
| 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 |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008
Posts: 1020
Location: India
|
|
|
|
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 |
|
|
SuperTramppp
New User
Joined: 29 Apr 2009
Posts: 8
Location: China
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
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 |
|
|
dbzTHEdinosauer
Global Moderator
Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne
|
|
|
|
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 |
|
|
|
|
