Table Space that Need Reorganize

Antonis Galaios

Table Space that Need Reorganize
Dear listers hi,
We running DB2 V7 on OS/390.We reorganize most of our tablespaces once a
week . We decided to use in our Reorganize jobs the parameters OFFPOSLIMIT
and INDREFLIMIT with the default values.
Notice that we don't have LOB table spaces Every Monday after the Reorganize
I run some queries to see if still we have table spaces that need Reorganize
using values a lot less than the default (10%) I usually used 4% or 2% and
everything seems ok. After 20 days or so all our jobs increased elapsed time
and I/O wait a lot .For example from 20 minutes elapsed time and 7 minutes
I/O time went to one hour and a half elapsed and about one hour I/O time.
Last weekend we run Reorganize for all of our table spaces without the
parameters and the batch jobs decrease their time back to normal.
Does anyone has any hints about that

Antonis Galaios
EFG Eurobank- Ergasias
Central System Support Division
Database Administrator
IBM Certified Solutions Expert DB2 UDB V7.1
1-3 Lyssicratous str.
17674 Kallithea, Athens, Greece
tel.: +302109474161




Disclaimer
This e-mail is confidential. If you are not the intended recipient, you should not copy it, re-transmit it, use it or disclose its contents, but should return it to the sender immediately and delete the copy from your system.
EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any opinion, recommendation, conclusion, solicitation, offer or agreement or any information contained in this communication.
EFG Eurobank Ergasias S.A. cannot accept any responsibility for the accuracy or completeness of this message as it has been transmitted over a public network. If you suspect that the message may have been intercepted or amended, please call the sender.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Michael Ebert

Re: Table Space that Need Reorganize
(in response to Antonis Galaios)
The REORG does not determine these parameters - it just reads the
appropriate statistics columns in the Catalog - e.g. SYSTABLEPART,
SYSINDEXPART. If you don't run RUNSTATS regularly, these values won't
change, and the limits will never be met.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Dear listers hi,
We running DB2 V7 on OS/390.We reorganize most of our tablespaces once a
week . We decided to use in our Reorganize jobs the parameters OFFPOSLIMIT
and INDREFLIMIT with the default values.
Notice that we don't have LOB table spaces Every Monday after the
Reorganize
I run some queries to see if still we have table spaces that need
Reorganize
using values a lot less than the default (10%) I usually used 4% or 2% and
everything seems ok. After 20 days or so all our jobs increased elapsed
time
and I/O wait a lot .For example from 20 minutes elapsed time and 7 minutes
I/O time went to one hour and a half elapsed and about one hour I/O time.
Last weekend we run Reorganize for all of our table spaces without the
parameters and the batch jobs decrease their time back to normal.
Does anyone has any hints about that

Antonis Galaios
EFG Eurobank- Ergasias
Central System Support Division
Database Administrator
IBM Certified Solutions Expert DB2 UDB V7.1
1-3 Lyssicratous str.
17674 Kallithea, Athens, Greece
tel.: +302109474161




Disclaimer
This e-mail is confidential. If you are not the intended recipient, you
should not copy it, re-transmit it, use it or disclose its contents, but
should return it to the sender immediately and delete the copy from your
system.
EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any
opinion, recommendation, conclusion, solicitation, offer or agreement or
any information contained in this communication.
EFG Eurobank Ergasias S.A. cannot accept any responsibility for the
accuracy or completeness of this message as it has been transmitted over a
public network. If you suspect that the message may have been intercepted
or amended, please call the sender.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email]
Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Ulf Heinrich

Re: Table Space that Need Reorganize
(in response to Michael Ebert)
If you are running DB2 V7 and you just want to get your reorg candidates,
you should consider making use of the real-time statistics. This gives you
the advantage that you don't have to run a utility, just to find out if
you have to run another utility!

But keep in mind, that the DB2 optimizer needs actual information about
your objects to find the best access path for your queries. That
information is only gathered by the runstats utility. Think about running
the reorg utility with the statistics parameter, then the runstats
information are gathered while reorganizing.

Furthermore you should also consider changed pages, unclustered inserts,
mass deletes and active pages in conjunction with extents for your reorg
decision, especially to adjust the allocations of your DB2 spaces.



Ulf Heinrich
SOFTWARE ENGINEERING
-Technical Consultant-
Robert-Stolz-Strasse 5
40470 Duesseldorf/Germany
Tel. +49 (0)211 96149-0
Fax +49 (0)211 96149-32
E-mail [login to unmask email]
Homepage www.seg.de


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email] On Behalf Of
[login to unmask email]
Sent: Tuesday, January 20, 2004 10:42 AM
To: [login to unmask email]
Subject: Table Space that Need Reorganize

Dear listers hi,
We running DB2 V7 on OS/390.We reorganize most of our tablespaces once a
week . We decided to use in our Reorganize jobs the parameters OFFPOSLIMIT
and INDREFLIMIT with the default values.
Notice that we don't have LOB table spaces Every Monday after the
Reorganize
I run some queries to see if still we have table spaces that need
Reorganize
using values a lot less than the default (10%) I usually used 4% or 2% and
everything seems ok. After 20 days or so all our jobs increased elapsed
time
and I/O wait a lot .For example from 20 minutes elapsed time and 7 minutes
I/O time went to one hour and a half elapsed and about one hour I/O time.
Last weekend we run Reorganize for all of our table spaces without the
parameters and the batch jobs decrease their time back to normal.
Does anyone has any hints about that

Antonis Galaios
EFG Eurobank- Ergasias
Central System Support Division
Database Administrator
IBM Certified Solutions Expert DB2 UDB V7.1
1-3 Lyssicratous str.
17674 Kallithea, Athens, Greece
tel.: +302109474161




Disclaimer
This e-mail is confidential. If you are not the intended recipient, you
should not copy it, re-transmit it, use it or disclose its contents, but
should return it to the sender immediately and delete the copy from your
system.
EFG Eurobank Ergasias S.A. is not responsible for, nor endorses, any
opinion, recommendation, conclusion, solicitation, offer or agreement or
any information contained in this communication.
EFG Eurobank Ergasias S.A. cannot accept any responsibility for the
accuracy or completeness of this message as it has been transmitted over a
public network. If you suspect that the message may have been intercepted
or amended, please call the sender.

--------------------------------------------------------------------------
-------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and
home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page
select "Join or Leave the list". The IDUG DB2-L FAQ is at
http://www.idugdb2-l.org. The IDUG List Admins can be reached at
[login to unmask email] Find out the latest on IDUG conferences
at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm