MVSFORUMS.com

Martin · Beginner Joined: 20 Mar 2006 Posts: 133 Topics: 58

Sometime back I happen to read an article on the net which talked about the advantages of migrating to DB2V8. ( I dont have the link right now.. Sad

)

Could any one provide some info about migrating to DB2V8. Also I read that the ORDER BY clause a must in a cursor.

Could any one let me why this is a must ? what about the program which do not have order by clause ?

kolusu · Posted: Tue Apr 10, 2007 2:05 pm Post subject:

Here is an excellent article about DB2 V8

http://www.dbazine.com/db2/db2-disarticles/zikopoulos11

Also read this book

http://publibz.boulder.ibm.com/cgi-bin/bookmgr_OS390/BOOKS/DSNWNJ11/CCONTENTS?DT=20061212110132

Order by clause is a MUST? I haven't heard any thing about it

Kolusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu

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

From a technical point of view, ORDER BY is a must in a restartable Batch program, to be able to reposition a cursor after an interruption.
Talking about business, work is normally done in a time, partner or account orientation, and there an ORDER BY is very helpful indeed.

Just my two cents.
Christian

kolusu · Posted: Wed Apr 11, 2007 8:39 am Post subject:

CZerfas,

Do you still need an ORDER By in the following scenario?

A BMP program is to update a flag in a table from 'n' to 'y'
It opens the cursor and updates each row 1 by 1 flipping the flag (single update will do but it is a huge table which might cause deadlocks)

Now do you still need a order by ?

The same case also applies when you are deleting rows of the table

KOlusu
_________________
Kolusu - DFSORT Development Team (IBM)
DFSORT is on the Web at:
www.ibm.com/storage/dfsort

www.linkedin.com/in/kolusu

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

Hello Kolosu,

well, shurely not. But speaking of a general advice, there are not many opportunities where you can omit an ORDER BY.

Since every ORDER BY eats ressources, it is always worth to ask yourself, whether you really need an ORDER BY clause.

regards
Christian

Martin · Beginner Joined: 20 Mar 2006 Posts: 133 Topics: 58

Kolusu,

We were hit by this PROD. There was a SQL which did not have ordaer by clause and the rows fetched from this cursor was used as a driver for orther programs. Now in V8 the order in which the rows will be fetched is diff from what it was in V7.

Any thougths as to why would they change the ordaer in which the data gets retrieved.

-Mt

kolusu · Posted: Fri Apr 20, 2007 1:13 pm Post subject:

dbzTHEdinosauer · Posted: Fri Apr 20, 2007 1:37 pm Post subject:

I don't think that DB2 guarantees the same results table for multiple executions of a CURSOR, when there is no ORDER BY clause in the SQL; though they may be the same, DB2 is not trying to provide the same order each time.
_________________
Dick Brenholtz
American living in Varel, Germany