[DB2 v11 zOS] REORG after ROTATE

Philip Sevetson

[DB2 v11 zOS] REORG after ROTATE
I'm looking over old code, and I've discovered that some of our monthly purge-and-rotate jobs for date partitioned tables are using REORG with SHRLEVEL NONE to materialize the ALTER / ROTATE statement.

ALTER / ROTATE doesn't set a RECP or REORP condition, but does cause a SYSPENDINGDDL entry. Meanwhile, changing the previous high LIMITKEY from MAXVALUE to a fixed date _does_ cause a REORG PENDING (AREOR) condition.

I need a REORG to do the minimum disturbance while fixing this. Can I use REORG with SHRLEVEL REFERENCE to materialize pending changes and fix an AREOR?

Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]
212-857-1688 w
917-991-7052 m
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)

Steven Lamb

RE: [DB2 v11 zOS] REORG after ROTATE
(in response to Philip Sevetson)

We do a dummy load into the "oldest" partition to clear it out and then use something like the following and as far as I'm aware, this doesn't set any pending chages or advisory states. We run these Alters every day and RTS says SYSPENDINGDDL was last changed back in February. We don't do any Reorgs or Repairs after the Alter.

ALTER TABLE owner.table ROTATE PARTITION FIRST TO LAST ENDING AT('2019-06-19-00.00.00.000000') RESET

steen rasmussen

[DB2 v11 zOS] REORG after ROTATE
(in response to Steven Lamb)
Correct - ROTATE partition is an immediate change but will issue a drain

Steen Rasmussen

Sent from my iPhone

> On Jun 19, 2019, at 9:26 AM, Steven Lamb <[login to unmask email]> wrote:
>
> We do a dummy load into the "oldest" partition to clear it out and then use something like the following and as far as I'm aware, this doesn't set any pending chages or advisory states. We run these Alters every day and RTS says SYSPENDINGDDL was last changed back in February. We don't do any Reorgs or Repairs after the Alter.
>
> ALTER TABLE owner.table ROTATE PARTITION FIRST TO LAST ENDING AT('2019-06-19-00.00.00.000000') RESET
>
>
> 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]
> Discover the best cloning tool on the market. Try BCV5 & the new BCV5 Masking Tool.
> ESAi also has powerful solutions for Buffer Pool Tuning, Log Analysis, TDM, & more.
> 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
>

Jack Campbell

RE: [DB2 v11 zOS] REORG after ROTATE
(in response to Philip Sevetson)

Phil,

I do ROTATE's a lot , all automated. We use SHREVEL CHANGE REORG (with DISCARD) to dispose of the data rows and reclaim space, but you might also REORG the PART before rotate to just reclaim the space if you purge the data via application delete.

ROTATE does not add any advisory/restrictive state, but it will invalidate all static packages. You will need to rebind with you favorite PLANMGMT setting.

HTH

Jack

Philip Sevetson

[DB2 v11 zOS] REORG after ROTATE
(in response to Jack Campbell)
Jack,

Thanks. I didn’t mention the REORG with DISCARD, because it didn’t seem relevant to the ROTATE. We do that.

I’m actually not worrying about reclaiming the space – I’m going to test changing the REORG to REUSE if I don’t run with SHRLEVEL REFERENCE. It looks, from the advice, like my only reason to have a pending state is the limit key change, which is on the MAXVALUES partition and also creates an AREOR (because there’s data in the MAXVALUES part). And… I might be able to fix that by adding an extra partition, and changing out MAXVALUES before it contains data. We’ll have to see, I guess.

-phil

From: Jack Campbell [mailto:[login to unmask email]
Sent: Wednesday, June 19, 2019 1:13 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: [DB2 v11 zOS] REORG after ROTATE


Phil,

I do ROTATE's a lot , all automated. We use SHREVEL CHANGE REORG (with DISCARD) to dispose of the data rows and reclaim space, but you might also REORG the PART before rotate to just reclaim the space if you purge the data via application delete.

ROTATE does not add any advisory/restrictive state, but it will invalidate all static packages. You will need to rebind with you favorite PLANMGMT setting.

HTH

Jack

-----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.**