AREO* or AREOR advisory status

Anil Kale

AREO* or AREOR advisory status

Hi All,

I am seeking some clarity to further my understanding of the AREO* or AREOR advisory status after the a reorg.

After performing an ALTER TABLE to increase a column size (CHAR data type), the corresponding TS goes to a AREO*  advisory status.

Now, as per the manual, it mentions the difference between the AREO* or AREOR advisory status.

REORG-pending (AREO*) advisory status:
Indicates that the object needs to be reorganized for optimal performance. AREO* status can be reset on a single partition, regardless of whether adjacent partitions are also in AREO* status. The affected objects are not restricted and can be accessed by both readers and writers.

REORG-pending (AREOR) advisory status:
Indicates that the object should be reorganized to apply pending definition changes. AREOR status must be reset for all adjacent logical partitions that are in AREOR status. The affected objects are not restricted and can be accessed by both readers and writers. The affected objects are not modified until the REORG job that materializes the definition changes is run.

My understanding of the status description is that if the status is AREO*, it suggests the COLUMN size has been altered and in effect allow an INSERT of data of the increased column size. And that the reorg is desirable for performance reasons.
Since I am working on an ALTER a fairly large table, there is a small chance that the REORG may not complete in the available maintenance window. So, I am looking for some comfort that even if the reorg does not go through, the application can still use the increased column size (of the Altered table). Is my understanding of the AREO* advisory status correct? or is there something more I need to know before I am comfortable with the fact that the reorg is a nice to have as opposed to must have.

Thanks.
Anil

 

 

Phil Grainger

AREO* or AREOR advisory status
(in response to Anil Kale)
Yes Anil

AREOR* means you have to reorg for PERFORMANCE reasons after an IMMEDIATE alter has changed SOME aspects of your stored data (such as making a column larger)

AREOR means that you have to reorg to IMPLEMENT the change, which is still pending after you issued a deferred alter

In the former case, your application can process the data “as if the change were complete”. So, if you increase a CHAR(8) to CHAR(12), you can immediately store 12 characters in the column. In the latter case you cannot UNTIL you have run the reorg

Phil Grainger
Principal Enablement Manager

[BMC Exchange 2019 - Global Event Series - REGISTER] https://www.bmc.com/ami

Direct

+44 1189 218 000

Mobile

+44 7808 643 479

Email

[login to unmask email]

E2, Eskdale Road
Winnersh
Berkshire
United Kingdom
RG41 5TS
[image001 (002)] [cid:[login to unmask email] [https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]



From: Anil Kale <[login to unmask email]>
Sent: 14 August 2019 15:33
To: [login to unmask email]
Subject: [EXTERNAL] [DB2-L] - AREO* or AREOR advisory status


Hi All,

I am seeking some clarity to further my understanding of the AREO* or AREOR advisory status after the a reorg.

After performing an ALTER TABLE to increase a column size (CHAR data type), the corresponding TS goes to a AREO* advisory status.

Now, as per the manual, it mentions the difference between the AREO* or AREOR advisory status.

REORG-pending (AREO*) advisory status:
Indicates that the object needs to be reorganized for optimal performance. AREO* status can be reset on a single partition, regardless of whether adjacent partitions are also in AREO* status. The affected objects are not restricted and can be accessed by both readers and writers.

REORG-pending (AREOR) advisory status:
Indicates that the object should be reorganized to apply pending definition changes. AREOR status must be reset for all adjacent logical partitions that are in AREOR status. The affected objects are not restricted and can be accessed by both readers and writers. The affected objects are not modified until the REORG job that materializes the definition changes is run.

My understanding of the status description is that if the status is AREO*, it suggests the COLUMN size has been altered and in effect allow an INSERT of data of the increased column size. And that the reorg is desirable for performance reasons.
Since I am working on an ALTER a fairly large table, there is a small chance that the REORG may not complete in the available maintenance window. So, I am looking for some comfort that even if the reorg does not go through, the application can still use the increased column size (of the Altered table). Is my understanding of the AREO* advisory status correct? or is there something more I need to know before I am comfortable with the fact that the reorg is a nice to have as opposed to must have.

Thanks.
Anil





-----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 (49.7k)
  • image002.png (6.7k)
  • image003.jpg (1.6k)
  • image004.png (<1k)

Anil Kale

RE: AREO* or AREOR advisory status
(in response to Phil Grainger)

Thanks Phil for confirming what I thought would happen. I feel much better now.

have a nice day ahead!

 

Anil