MVSFORUMS.com

jctgf · Beginner Joined: 05 Nov 2006 Posts: 89 Topics: 36

hi,

pls suppose i am gonna execute a select cursor statement many times and that my table doesn't suffer any update/delete/insert.

are the rows retrieved ALWAYS in the same sequence?

there is no command that orders the rows, such as ORDER BY/DISTINCT/UNION/etc!

is it possible to retrieve the rows in a different sequence from one select to the other or can i be 100% sure the rows will always be read in the very same sequence?

thanks!
jc

kolusu · Posted: Thu Dec 13, 2007 7:06 pm Post subject:

jctgf,

NO you cannot sure of the same sequence of retrieval. Apart from the insert/update/delete there are other things to be considered.

1. Is it Dynamic Sql or Static Sql
2. Load utitlies like LOAD,REORG, RUNSTATS... can also affect the retrieval sequence.

Hope this helps...

Cheers
_________________
Kolusu
www.linkedin.com/in/kolusu

jctgf · Beginner Joined: 05 Nov 2006 Posts: 89 Topics: 36

hi,

this is a static sql.

pls, suppose there is no change in the table between one select and the next one.

no load/reorg/runstats.

assuming that, is it possible to have the rows ALWAYS retrieved in the same sequence?

thanks

CZerfas · Intermediate Joined: 31 Jan 2003 Posts: 211 Topics: 8

Since the ordering of the rows of an answer set of a read-only table depends solely on the access path of the program to the data and, since this is static sql, this access path does not change, I would assume, that the ordering of the rows is stabil, i.e. always the same.

There are a lot of assumptions in this statement, therefore I would simply code an ORDER BY clause and forget the bothering.

regards
Christian

dbzTHEdinosauer · Posted: Fri Dec 14, 2007 5:03 am Post subject:

db2 simply does not guarantee same order without an order by clause. It is poor programming practice to assume that one knows enough about db2 to predict results if you do not use the appropriate syntax. If you want a predictable order, use the order by clause.

edited to correct first sentence. changed with to without. dbz
_________________
Dick Brenholtz
American living in Varel, Germany