Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11

Jose Ramon Vazquez

Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11

Anyone has noticed a change of order in rows of a cursor without ORDER BY after migration to DB2 z/OS V11. Without REBIND (manual nor automatic) of the package. The access path is a matching index.

Thanks.

 

Roy Boxwell

AW: Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11
(in response to Jose Ramon Vazquez)
Without REBIND would be a disaster… are you 101% sure about that??

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

Von: Jose Ramon Vazquez [mailto:[login to unmask email]
Gesendet: Freitag, 22. September 2017 12:04
An: [login to unmask email]
Betreff: [DB2-L] - Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11


Anyone has noticed a change of order in rows of a cursor without ORDER BY after migration to DB2 z/OS V11. Without REBIND (manual nor automatic) of the package. The access path is a matching index.

Thanks.



-----End Original Message-----

Jose Ramon Vazquez

RE: AW: Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11
(in response to Roy Boxwell)

At this moment is only a guess that explain a problem after migration to V11. I hope to confirm or no this next week.

Dave Nance

AW: Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11
(in response to Jose Ramon Vazquez)
 Why is it a problem? Ordering was never guaranteed without an ORDER BY clause. A reorg, disorganization, an access path change, a ton of other reasons could change the ordering without notice and has always been the described behavior. If you need/want data in a particular order then ORDER BY must be present in query.

Dave Nance 

From: Jose Ramon Vazquez <[login to unmask email]>
To: [login to unmask email]
Sent: Friday, September 22, 2017 5:42 AM
Subject: [DB2-L] - RE: AW: Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11

At this moment is only a guess that explain a problem after migration to V11. I hope to confirm or no this next week.
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email] a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Peter Backlund

AW: Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11
(in response to Dave Nance)
Hear, hear!

Peter

On 2017-09-22 15:44, Dave Nance wrote:
 Why is it a problem? Ordering was never guaranteed without an ORDER BY clause. A reorg, disorganization, an access path change, a ton of other reasons could change the ordering without notice and has always been the described behavior. If you need/want data in a particular order then ORDER BY must be present in query.

Dave Nance 

From: Jose Ramon Vazquez <[login to unmask email]>
To: [login to unmask email]
Sent: Friday, September 22, 2017 5:42 AM
Subject: [DB2-L] - RE: AW: Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11

At this moment is only a guess that explain a problem after migration to V11. I hope to confirm or no this next week.
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email] a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2


Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2
--

+--------------------------------+---------------------------------+
| Peter G Backlund | E-mail: [login to unmask email] |
+--------------------------------+---------------------------------+
| Phone: 0046(0)70 - 764 7554 | Skype: BacklundDB2 |
+--------------------------------+---------------------------------+
| May - September | October - April |
+------------------------------------------------------------------+
| Råsundavägen 94 | Calle Alella 55, Apt 2B |
| S-169 57 SOLNA | 03185 Torrevieja |
| Sweden | Spain |
+--------------------------------+---------------------------------+
| Playing with DB2 since 1981 ... and forever! |
+------------------------------------------------------------------+

Phil Grainger

Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11
(in response to Jose Ramon Vazquez)
I’m paraphrasing, but somewhere in the Db2 manuals it does say that if the order of rows in a result set is important then you MUST use ORDER BY (that’s what it’s for)

Otherwise the order of rows is not defined and subject to change – any time

You could rebind and get a tablespace scan instead of an index – that would change the row order

And, as you may have found, if you apply maintenance or move to a new version of Db2, the row order could change there too

Even at runtime the row order could change – maybe Db2 was using a RID list to materialize the data and ran out of storage. Falling back to an alternate access path could conceivably change the order of rows

Sorry – definitely “working as designed”

________________________________

Phil Grainger

Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[cid:[login to unmask email]






From: Jose Ramon Vazquez [mailto:[login to unmask email]
Sent: 22 September 2017 11:04
To: [login to unmask email]
Subject: [DB2-L] - Change of ORDER in rows of a cursor without ORDER BY after migration to DB2 z/OS V11


Anyone has noticed a change of order in rows of a cursor without ORDER BY after migration to DB2 z/OS V11. Without REBIND (manual nor automatic) of the package. The access path is a matching index.

Thanks.



-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image002.png (5.9k)