DB2 Reorg information

Chandra Sekhar Runku

DB2 Reorg information


Can any one help to find out when the last REORG run against a tablespace.

I tried to find  SYSIBM.SYSCOPY i am having a limited period information during that period no REORG runs.

Chandra

DB2 System Programmer

Wolfgang Zeus

AW: DB2 Reorg information
(in response to Chandra Sekhar Runku)
Hello Chandra,

table for realtime statistics: SYSIBM.SYSTABLESPACESTATS, column REORGLASTTIME

Wolfgang


Wolfgang Zeus
Abteilung Informatik – Betrieb

HUK-COBURG
Bahnhofsplatz
96444 Coburg
Telefon: 09561 96-44193
Telefax: 09561 96-44104
E-Mail: [login to unmask email]
Internet: www.huk.de
________________________________
HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter Deutschlands a. G. in Coburg
Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
Vorstand: Dr. Wolfgang Weiler (Sprecher), Stefan Gronbach, Klaus-Jürgen Heitmann, Dr. Hans Olav Herøy, Sarah Rössler, Daniel Thomas (stv.).
________________________________
Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte Informationen.
Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich erhalten haben,
informieren Sie bitte sofort den Absender und vernichten Sie diese Nachricht.
Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht ist nicht gestattet.

This information may contain confidential and/or privileged information.
If you are not the intended recipient (or have received this information in error) please notify the
sender immediately and destroy this information.
Any unauthorized copying, disclosure or distribution of the material in this information is strictly forbidden.
________________________________
Von: Chandra Sekhar Runku [mailto:[login to unmask email]
Gesendet: Donnerstag, 8. September 2016 15:04
An: [login to unmask email]
Betreff: [DB2-L] - DB2 Reorg information


Can any one help to find out when the last REORG run against a tablespace.

I tried to find SYSIBM.SYSCOPY i am having a limited period information during that period no REORG runs.

Chandra

DB2 System Programmer

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

Tushar Jha

DB2 Reorg information
(in response to Chandra Sekhar Runku)
Chandra,

You may look for ICTYPE W or X :

W : REORG LOG(NO)
X : REORG LOG(YES)


Select * from sysibm.syscopy
Where tsname=’YOURTS’ and DBNAME=’YOURDB’
and ICTYPE IN (‘W’,’X’)
order by ICDATE DESC,ICTIME DESC


If you do not have record in SYSCOPY, then you may look in RTS :

select REORGLASTTIME
from sysibm.systablespacestats
where dbname='YOURDB' and name='YOURTS'



Thanks,
Tushar Jha


From: Chandra Sekhar Runku [mailto:[login to unmask email]
Sent: Thursday, September 08, 2016 6:34 PM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Reorg information


Can any one help to find out when the last REORG run against a tablespace.

I tried to find SYSIBM.SYSCOPY i am having a limited period information during that period no REORG runs.

Chandra

DB2 System Programmer

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

----------------------------------------------------------------------
This message and any attachments are intended only for the use of the addressee and may contain information that is privileged and confidential. If the reader of the message is not the intended recipient or an authorized representative of the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, notify the sender immediately by return email and delete the message and any attachments from your system.

John Bucaria

DB2 Reorg information
(in response to Chandra Sekhar Runku)
Chandra,
Systablepart has a column called REORG_LR_TS which contains the timestamp when the last reorg or load replace occurred.

From: Chandra Sekhar Runku [mailto:[login to unmask email]
Sent: Thursday, September 08, 2016 9:04 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 Reorg information


Can any one help to find out when the last REORG run against a tablespace.

I tried to find SYSIBM.SYSCOPY i am having a limited period information during that period no REORG runs.

Chandra

DB2 System Programmer

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

Chandra Sekhar Runku

AW: DB2 Reorg information
(in response to Wolfgang Zeus)
Thank you so much Wolfgang.

On Thu, Sep 8, 2016 at 6:40 PM, Zeus, Wolfgang <[login to unmask email]> wrote:

> Hello Chandra,
>
>
>
> table for realtime statistics: SYSIBM.SYSTABLESPACESTATS, column
> REORGLASTTIME
>
>
>
> Wolfgang
>
>
>
> Wolfgang Zeus
> Abteilung Informatik – Betrieb
>
> HUK-COBURG
> Bahnhofsplatz
> 96444 Coburg
> Telefon: 09561 96-44193
> Telefax: 09561 96-44104
> E-Mail: [login to unmask email]
> Internet: www.huk.de
>
> ------------------------------
> HUK-COBURG Haftpflicht-Unterstützungs-Kasse kraftfahrender Beamter
> Deutschlands a. G. in Coburg
> Reg.-Gericht Coburg HRB 100; St.-Nr. 9212/101/00021
> Sitz der Gesellschaft: Bahnhofsplatz, 96444 Coburg
> Vorsitzender des Aufsichtsrats: Prof. Dr. Heinrich R. Schradin.
> Vorstand: Dr. Wolfgang Weiler (Sprecher), Stefan Gronbach, Klaus-Jürgen
> Heitmann, Dr. Hans Olav Herøy, Sarah Rössler, Daniel Thomas (stv.).
> ------------------------------
> Diese Nachricht enthält vertrauliche und/oder rechtlich geschützte
> Informationen.
> Wenn Sie nicht der richtige Adressat sind oder diese Nachricht irrtümlich
> erhalten haben,
> informieren Sie bitte sofort den Absender und vernichten Sie diese
> Nachricht.
> Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Nachricht
> ist nicht gestattet.
>
> This information may contain confidential and/or privileged information.
> If you are not the intended recipient (or have received this information
> in error) please notify the
> sender immediately and destroy this information.
> Any unauthorized copying, disclosure or distribution of the material in
> this information is strictly forbidden.
> ------------------------------
>
> *Von:* Chandra Sekhar Runku [mailto:[login to unmask email]
> *Gesendet:* Donnerstag, 8. September 2016 15:04
> *An:* [login to unmask email]
> *Betreff:* [DB2-L] - DB2 Reorg information
>
>
>
>
> Can any one help to find out when the last REORG run against a tablespace.
>
> I tried to find SYSIBM.SYSCOPY i am having a limited period information
> during that period no REORG runs.
>
> Chandra
>
> DB2 System Programmer
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>

Chandra Sekhar Runku

DB2 Reorg information
(in response to John Bucaria)
Thank you so much John.

On Thu, Sep 8, 2016 at 7:59 PM, Bucaria, John <[login to unmask email]> wrote:

> Chandra,
>
> Systablepart has a column called REORG_LR_TS which contains the timestamp
> when the last reorg or load replace occurred.
>
>
>
> *From:* Chandra Sekhar Runku [mailto:[login to unmask email]
> *Sent:* Thursday, September 08, 2016 9:04 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - DB2 Reorg information
>
>
>
>
> Can any one help to find out when the last REORG run against a tablespace.
>
> I tried to find SYSIBM.SYSCOPY i am having a limited period information
> during that period no REORG runs.
>
> Chandra
>
> DB2 System Programmer
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Chandra Sekhar Runku

DB2 Reorg information
(in response to Tushar Jha)
Thanks a lot Tushar.

On Thu, Sep 8, 2016 at 6:45 PM, Jha, Tushar (CORP) <[login to unmask email]>
wrote:

> Chandra,
>
>
>
> You may look for ICTYPE W or X :
>
>
>
> W : REORG LOG(NO)
>
> X : REORG LOG(YES)
>
>
>
>
>
> Select * from sysibm.syscopy
>
> Where tsname=’YOURTS’ and DBNAME=’YOURDB’
>
> and ICTYPE IN (‘W’,’X’)
>
> order by ICDATE DESC,ICTIME DESC
>
>
>
>
>
> If you do not have record in SYSCOPY, then you may look in RTS :
>
>
>
> select REORGLASTTIME
>
> from sysibm.systablespacestats
>
> where dbname='YOURDB' and name='YOURTS'
>
>
>
>
>
>
>
> Thanks,
>
> Tushar Jha
>
>
>
>
>
> *From:* Chandra Sekhar Runku [mailto:[login to unmask email]
> *Sent:* Thursday, September 08, 2016 6:34 PM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - DB2 Reorg information
>
>
>
>
> Can any one help to find out when the last REORG run against a tablespace.
>
> I tried to find SYSIBM.SYSCOPY i am having a limited period information
> during that period no REORG runs.
>
> Chandra
>
> DB2 System Programmer
>
>
> -----End Original Message-----
> ------------------------------
> This message and any attachments are intended only for the use of the
> addressee and may contain information that is privileged and confidential.
> If the reader of the message is not the intended recipient or an authorized
> representative of the intended recipient, you are hereby notified that any
> dissemination of this communication is strictly prohibited. If you have
> received this communication in error, notify the sender immediately by
> return email and delete the message and any attachments from your system.
>
> -----End Original Message-----
>

Ram Ramaiyan

RE: DB2 Reorg information
(in response to Chandra Sekhar Runku)

Hi Experts, Any leads on the Reorg recommendations would really be appreciated. We are currently optimizing DB2 systems where one of the area we are focusing is to eliminate the candidates which doesn't need a Reorg..We have RTS enabled in our systems but we use PDA(CA) stats for our batch.Please share any query which makes use of the RTS information to pull out the candidates which needs a Reorg.

Thanks,

Ram.

Bill Gallagher

DB2 Reorg information
(in response to Ram Ramaiyan)
Hi Ram,

I did something similar at my previous job. I wrote a stored procedure which ran weekly against the RTS tables, and populated a “UTILITY_RECOMMENDATIONS” table with candidates for both REORG and RUNSTATS.

The queries themselves were rather complicated, as I put predicates in the queries to only include certain databases (primarily, application databases), but also excluded specific tablespaces or indexespaces that were identified in a REORG_EXCEPTION table. This was done because of the nature of the way some of our tables behaved within their applications, and they were falsely being flagged as reorg candidates by the queries below every week, when they really didn’t need to be reorged that frequently.

These were the conditions that I looked for (you will want to determine whether these conditions and thresholds are appropriate for your shop, and modify appropriately). These queries are cut and pasted from my SP code (with all the database and tablespace/indexspace inclusion/exclusion predicates removed).

Hope this helps!

For tablespaces:


1) Relocated rows > 5% of total rows

SELECT 'REORG', 'TS', A.DBNAME, A.NAME, A.PARTITION,
'INDREF > 5%'
FROM SYSIBM.SYSTABLESPACESTATS A
WHERE A.TOTALROWS >= 0
AND (BIGINT(A.REORGNEARINDREF + A.REORGFARINDREF) * 100 /
(A.TOTALROWS + 1)) > 5
AND (A.REORGNEARINDREF + A.REORGFARINDREF) > 100


2) Unclustered inserts > 10% of total rows

SELECT 'REORG', 'TS', A.DBNAME, A.NAME, A.PARTITION,
'UNCLUSTINS > 10%'
FROM SYSIBM.SYSTABLESPACESTATS A
WHERE A.TOTALROWS >= 0
AND (BIGINT(A.REORGUNCLUSTINS) * 100 /
(A.TOTALROWS + 1)) > 10
AND A.REORGCLUSTERSENS > 0
AND A.REORGUNCLUSTINS > 100


3) Total inserts > 20% of total rows

SELECT 'REORG', 'TS', A.DBNAME, A.NAME, A.PARTITION,
'INSERTS > 20%'
FROM SYSIBM.SYSTABLESPACESTATS A
WHERE A.TOTALROWS >= 0
AND (BIGINT(A.REORGINSERTS) * 100 /
(A.TOTALROWS + 1)) > 20
AND A.REORGINSERTS > 100


4) Total deletes > 20% of total rows

SELECT 'REORG', 'TS', A.DBNAME, A.NAME, A.PARTITION,
'DELETES > 20%'
FROM SYSIBM.SYSTABLESPACESTATS A
WHERE A.TOTALROWS >= 0
AND (BIGINT(A.REORGDELETES) * 100 /
(A.TOTALROWS + 1)) > 20
AND A.REORGDELETES > 100
AND A.DBNAME LIKE 'DB%'


5) Tablespace is over 25 extents

SELECT 'REORG', 'TS', A.DBNAME, A.NAME, A.PARTITION,
'EXTENTS > 25'
FROM SYSIBM.SYSTABLESPACESTATS A
WHERE EXTENTS > 25


6) Disorganized LOB rows > 10% of total rows

SELECT 'REORG', 'TS', A.DBNAME, A.NAME, A.PARTITION,
'DISORGLOB > 10%'
FROM SYSIBM.SYSTABLESPACESTATS A
WHERE A.TOTALROWS >= 0
AND (BIGINT(A.REORGDISORGLOB) * 100 /
(A.TOTALROWS + 1)) > 10


7) One or more mass deletes have been executed

SELECT 'REORG', 'TS', A.DBNAME, A.NAME, A.PARTITION,
'MASSDELETE > 0'
FROM SYSIBM.SYSTABLESPACESTATS A
WHERE A.REORGMASSDELETE > 0

For indexspaces:


1) Pseudo-deleted entries > 5% of total rows

SELECT 'REORG', 'IX', A.CREATOR, A.NAME, A.PARTITION,
'PSEUDO DELETES > 5%'
FROM SYSIBM.SYSINDEXSPACESTATS A
WHERE A.TOTALENTRIES >= 0
AND (BIGINT(A.REORGPSEUDODELETES) * 100 /
(A.TOTALENTRIES + 1)) > 5


2) Far leaf pages > 10% of active pages

SELECT 'REORG', 'IX', A.CREATOR, A.NAME, A.PARTITION,
'LEAF FAR > 10%'
FROM SYSIBM.SYSINDEXSPACESTATS A
WHERE A.NACTIVE >= 0
AND (BIGINT(A.REORGLEAFFAR) * 100 /
(A.NACTIVE + 1)) > 10


3) Total inserts > 20% of total rows

SELECT 'REORG', 'IX', A.CREATOR, A.NAME, A.PARTITION,
'INSERTS > 20%'
FROM SYSIBM.SYSINDEXSPACESTATS A
WHERE A.TOTALENTRIES >= 0
AND (BIGINT(A.REORGINSERTS) * 100 /
(A.TOTALENTRIES + 1)) > 20


4) Total deletes > 20% of total rows

SELECT 'REORG', 'IX', A.CREATOR, A.NAME, A.PARTITION,
'DELETES > 20%'
FROM SYSIBM.SYSINDEXSPACESTATS A
WHERE A.TOTALENTRIES >= 0
AND (BIGINT(A.REORGDELETES) * 100 /
(A.TOTALENTRIES + 1)) > 20


5) Total append inserts > 10% of total rows

SELECT 'REORG', 'IX', A.CREATOR, A.NAME, A.PARTITION,
'APPEND INSERTS > 10%'
FROM SYSIBM.SYSINDEXSPACESTATS A
WHERE A.TOTALENTRIES >= 0
AND (BIGINT(A.REORGAPPENDINSERT) * 100 /
(A.TOTALENTRIES + 1)) > 10


6) Index is over 25 extents

SELECT 'REORG', 'IX', A.CREATOR, A.NAME, A.PARTITION,
'EXTENTS > 25'
FROM SYSIBM.SYSINDEXSPACESTATS A
WHERE A.EXTENTS > 25


7) The number of levels in the index has changed

SELECT 'REORG', 'IX', A.CREATOR, A.NAME, A.PARTITION,
'NUMLEVELS > 0'
FROM SYSIBM.SYSINDEXSPACESTATS A
WHERE A.REORGNUMLEVELS > 0

Bill Gallagher
DB2 Database Administrator
State of Connecticut
Department of Children and Families
Office: 860-263-1389
[login to unmask email]<mailto:[login to unmask email]>


From: Ram Ramaiyan [mailto:[login to unmask email]
Sent: Tuesday, January 02, 2018 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Reorg information


Hi Experts, Any leads on the Reorg recommendations would really be appreciated. We are currently optimizing DB2 systems where one of the area we are focusing is to eliminate the candidates which doesn't need a Reorg..We have RTS enabled in our systems but we use PDA(CA) stats for our batch.Please share any query which makes use of the RTS information to pull out the candidates which needs a Reorg.

Thanks,

Ram.

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

Steen Rasmussen

DB2 Reorg information
(in response to Ram Ramaiyan)
Hello Ram,

You should definitely use PDA’s capabilities to generate REORG’s based on RTS conditions/thresholds.
There are a couple of ways to get this done – either via RTOS (objects extracted based on RTS thresholds) or ACTION BASED (objects defined in the Extract will be reorg’ed based on RTS conditions).

I suggest we hook up outside this forum via a webex so I can demo live the available options, so please contact me off list at [login to unmask email]<mailto:[login to unmask email]> and let me know the company as well please.
Steen

From: Ram Ramaiyan [mailto:[login to unmask email]
Sent: Tuesday, January 02, 2018 2:28 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 Reorg information

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.

Hi Experts, Any leads on the Reorg recommendations would really be appreciated. We are currently optimizing DB2 systems where one of the area we are focusing is to eliminate the candidates which doesn't need a Reorg..We have RTS enabled in our systems but we use PDA(CA) stats for our batch.Please share any query which makes use of the RTS information to pull out the candidates which needs a Reorg.

Thanks,

Ram.

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

Ram Ramaiyan

RE: DB2 Reorg information
(in response to Bill Gallagher)

Dear Bill,

Thank you very much indeed. I shall compare the conditions which we have with yours. Thanks for sharing the queries. I appreciate your time and valuable inputs. 

Regards,

Ram R.

Ram Ramaiyan

RE: DB2 Reorg information
(in response to Steen Rasmussen)

Thank you, Steen. I shall surely connect with you.

Regards,

Ram R.