[Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

Philip Sevetson

[Db2 v11 z/OS] REORGing after Pending DDL, or maybe not
**please note my email address change**
In cleaning up my tables which need REORGs, after the ALTERs to their partition limit keys, I used a REORG, with a LISTDEF, and SHRLEVEL CHANGE SCOPE PENDING. Seems reasonable.

Apparently not. Four tablespaces, which had entries in SYSPENDINGDDL, did not have AREO* flagged, and SCOPE PENDING did not identify them as needing REORG. Don't know why. I'm explicitly running REORG for the four.

Lesson: until I understand *why* a tablespace would have pending changes but not an AREO* set, I'm going to explicitly REORG all tablespaces found in SYSPENDINGDDL, and/or any tablespace where I make limit key changes.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Phil Grainger

[Db2 v11 z/OS] REORGing after Pending DDL, or maybe not
(in response to Philip Sevetson)
I may be wrong, but ....

Advisory Reorg Pending, to me, means "you made some changes and in implementing them we have perhaps compromised performance or something and I STRONGLY advise reorging the data to resolve"

Anything in SYSPENDINGDDL has not even been attempted yet, so although you need a reorg to instantiate the changed, it doesn't really (in my mind) match the definition of "Advising a Reorg"

It's up to YOU Mr/Ms DBA to decide when to reorg to instantiate your change

________________________________

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: Sevetson, Phil [mailto:[login to unmask email]
Sent: 27 October 2017 13:22
To: [login to unmask email]
Subject: [DB2-L] - [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

**please note my email address change**
In cleaning up my tables which need REORGs, after the ALTERs to their partition limit keys, I used a REORG, with a LISTDEF, and SHRLEVEL CHANGE SCOPE PENDING. Seems reasonable.

Apparently not. Four tablespaces, which had entries in SYSPENDINGDDL, did not have AREO* flagged, and SCOPE PENDING did not identify them as needing REORG. Don't know why. I'm explicitly running REORG for the four.

Lesson: until I understand *why* a tablespace would have pending changes but not an AREO* set, I'm going to explicitly REORG all tablespaces found in SYSPENDINGDDL, and/or any tablespace where I make limit key changes.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D8872&d=DwMCAg&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=Ab7sSiPDrn5n8wwo00JHTieWKgOMPU40ANAic9Q5iwM&s=_s8W8xuU-J0Qgyui3JpUlAcD9MghlPPXAqYF_09c0jg&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_st_-3Fpost-3D183505-26anc-3Dp183505-23p183505&d=DwMCAg&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=Ab7sSiPDrn5n8wwo00JHTieWKgOMPU40ANAic9Q5iwM&s=dN-PSNOqBH6Cyuz65SvuFPFwXRxIv2UuMH9JEQEfra0&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMCAg&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=Ab7sSiPDrn5n8wwo00JHTieWKgOMPU40ANAic9Q5iwM&s=KiBx1LC23B_wRLqGayl_HDgFNI3C6_7iZMAAkWymrGA&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_us_to_&d=DwMCAg&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=Ab7sSiPDrn5n8wwo00JHTieWKgOMPU40ANAic9Q5iwM&s=t2oY9sinpazeOBU4FBp1QUR6Yt7CSkMfNHE5KbD-S-I&e=

This email has been sent to: [login to unmask email]<mailto:[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 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMCAg&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=Ab7sSiPDrn5n8wwo00JHTieWKgOMPU40ANAic9Q5iwM&s=4mEfoLJMoPXJgi219CF1tWOsOYkLL9Q6aVeAHlP__6o&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMCAg&c=UrUhmHsiTVT5qkaA4d_oSzcamb9hmamiCDMzBAEwC7E&r=EAGrd_qzLADPfI8dgytr8sbCG7_U9QfXwQMLgK1Zo30&m=Ab7sSiPDrn5n8wwo00JHTieWKgOMPU40ANAic9Q5iwM&s=FyIeWDHbyhIffNSMT3GwIcKsMF2EAwjTmZ0yrm2Fe1c&e=

________________________________
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)

Philip Sevetson

[Db2 v11 z/OS] REORGing after Pending DDL, or maybe not
(in response to Phil Grainger)
**please note my email address change**
Phil,

I don't have a problem with that, philosophically. However, it appears to contradict the product documentation, at
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_pendingchanges.html

Money quote (italics added):
"ALTER statements with certain options can cause pending changes to the definition of database objects. When an ALTER statement is issued that causes pending changes to the definition of an object, semantic validation and authorization checking are performed. However, changes to the table definition and data are not applied and the object is placed in advisory REORG-pending state (AREOR), until the REORG utility is run to resolve the pending changes."

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, October 27, 2017 8:59 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

I may be wrong, but ....

Advisory Reorg Pending, to me, means "you made some changes and in implementing them we have perhaps compromised performance or something and I STRONGLY advise reorging the data to resolve"

Anything in SYSPENDINGDDL has not even been attempted yet, so although you need a reorg to instantiate the changed, it doesn't really (in my mind) match the definition of "Advising a Reorg"

It's up to YOU Mr/Ms DBA to decide when to reorg to instantiate your change

-----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.
-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

Walter Jani&#223;en

AW: [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not
(in response to Philip Sevetson)
Phil

You are right, there is no distinction between AREOR and AREO*, both are called Advisory reorg pending, see also: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_pendingchanges.html
Did you consider to open a PMR?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Freitag, 27. Oktober 2017 15:05
An: '[login to unmask email]'
Betreff: [DB2-L] - RE: [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

**please note my email address change**
Phil,

I don't have a problem with that, philosophically. However, it appears to contradict the product documentation, at
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_pendingchanges.html

Money quote (italics added):
"ALTER statements with certain options can cause pending changes to the definition of database objects. When an ALTER statement is issued that causes pending changes to the definition of an object, semantic validation and authorization checking are performed. However, changes to the table definition and data are not applied and the object is placed in advisory REORG-pending state (AREOR), until the REORG utility is run to resolve the pending changes."

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, October 27, 2017 8:59 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

I may be wrong, but ....

Advisory Reorg Pending, to me, means "you made some changes and in implementing them we have perhaps compromised performance or something and I STRONGLY advise reorging the data to resolve"

Anything in SYSPENDINGDDL has not even been attempted yet, so although you need a reorg to instantiate the changed, it doesn't really (in my mind) match the definition of "Advising a Reorg"

It's up to YOU Mr/Ms DBA to decide when to reorg to instantiate your change

-----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.
-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
Attachments

  • image001.png (2.6k)

Philip Sevetson

[Db2 v11 z/OS] REORGing after Pending DDL, or maybe not
(in response to Walter Janißen)
**please note my email address change**
Walter,

Haven't opened a PMR because I haven't sufficient documentation yet. Right now, it's a weird thing that happened once. If I can recreate the situation, then I'll have something which the support people can track.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Walter Janißen [mailto:[login to unmask email]
Sent: Friday, October 27, 2017 9:32 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

Phil

You are right, there is no distinction between AREOR and AREO*, both are called Advisory reorg pending, see also: https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_pendingchanges.html
Did you consider to open a PMR?

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Sevetson, Phil [mailto:[login to unmask email]
Gesendet: Freitag, 27. Oktober 2017 15:05
An: '[login to unmask email]'
Betreff: [DB2-L] - RE: [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

**please note my email address change**
Phil,

I don't have a problem with that, philosophically. However, it appears to contradict the product documentation, at
https://www.ibm.com/support/knowledgecenter/en/SSEPEK_11.0.0/admin/src/tpc/db2z_pendingchanges.html

Money quote (italics added):
"ALTER statements with certain options can cause pending changes to the definition of database objects. When an ALTER statement is issued that causes pending changes to the definition of an object, semantic validation and authorization checking are performed. However, changes to the table definition and data are not applied and the object is placed in advisory REORG-pending state (AREOR), until the REORG utility is run to resolve the pending changes."

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001-2632
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Grainger, Phil [mailto:[login to unmask email]
Sent: Friday, October 27, 2017 8:59 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: [Db2 v11 z/OS] REORGing after Pending DDL, or maybe not

I may be wrong, but ....

Advisory Reorg Pending, to me, means "you made some changes and in implementing them we have perhaps compromised performance or something and I STRONGLY advise reorging the data to resolve"

Anything in SYSPENDINGDDL has not even been attempted yet, so although you need a reorg to instantiate the changed, it doesn't really (in my mind) match the definition of "Advising a Reorg"

It's up to YOU Mr/Ms DBA to decide when to reorg to instantiate your change

-----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.
-----End Original Message-----


**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)