reorg index without updating catalog statistics

Luis M Martinez Ch

reorg index without updating catalog statistics
Hi everybody

We are working with DB2 ZOS 7.1 under ZOS 1.5 for the Production
environment.

We did run REORG TABLESPACES over the objects with NEAROFFPOS +
FAROFFPOS / CARD * 100 > X and (NEARINDREF + FARINDREF) * 100 / CARDF > X.
At the same time we ran INLINE STATISTICS with KEYCARD and UPDATE ALL
parameters.

Then, we REORG INDEX for all tablespaces reorganized in the previous point
plus other indexes (and its tablespaces associated) with high LEAFDIST
values. The execution was with the PREFORMAT parameter for improve later,
index access performance. Also we ran INLINE STATISTICS with KEYCARD and
UPDATE ALL parameters.

I was wondered, when I reviewed the statistics in SYSTABLESPACE,
SYSTABLEPART, SYSINDEXES, SYSINDEXPART and SYSCOLUMNS tables, that
indicates of a good DATA/INDEX ORGANIZATION like ....

FARINDREF = 0
NEARINDREF = 0
LEAFFAR = 0



***********************************************
but

NEAROFFPOS <> 0
FAROFFPOS <> 0
LEAFNEAR <> 0
LEADIST <> 0

Why this values doesn't appear in ZEROS after the REORG?
Is lacking of PTF's?


Thanks in advance an good look
Luis M Mtz
Mexico

*************************************************

---------------------------------------------------------------------------------
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: reorg index without updating catalog statistics
(in response to Luis M Martinez Ch)
Hi Luis,

NEAR/FAROFFPOS(F) indicates how far away a data row is from the position
it would have if the data were sorted in index order. This value should be
0 only for the clustering index; for non-clustering indexes it might have
large values, since the data obviously can be sorted in only one order
(that of the clustering index). A FREEPAGE>0 also causes this value to be
non-0. This means you should use this criterion only for clustering
indexes.

LEAFNEAR and LEAFDIST can be >0 due to FREEPAGE>0. After FREEPAGE
non-empty pages, an empty one is inserted. This inserted page will
separate rows that would otherwise be on adjacent leaf pages.

There is no point in running REORG INDEX on indexes belonging to
tablespaces that you've run REORG TABLESPACE on just before.

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








Luis Miguel Martínez Chávez <[login to unmask email]>
Please respond to DB2 Database Discussion list at IDUG
<[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
14-01-06 18:52

To
[login to unmask email]
cc



Subject
[DB2-L] reorg index without updating catalog statistics






Hi everybody

We are working with DB2 ZOS 7.1 under ZOS 1.5 for the Production
environment.

We did run REORG TABLESPACES over the objects with NEAROFFPOS +
FAROFFPOS / CARD * 100 > X and (NEARINDREF + FARINDREF) * 100 / CARDF > X.
At the same time we ran INLINE STATISTICS with KEYCARD and UPDATE ALL
parameters.

Then, we REORG INDEX for all tablespaces reorganized in the previous point
plus other indexes (and its tablespaces associated) with high LEAFDIST
values. The execution was with the PREFORMAT parameter for improve later,
index access performance. Also we ran INLINE STATISTICS with KEYCARD and
UPDATE ALL parameters.

I was wondered, when I reviewed the statistics in SYSTABLESPACE,
SYSTABLEPART, SYSINDEXES, SYSINDEXPART and SYSCOLUMNS tables, that
indicates of a good DATA/INDEX ORGANIZATION like ....

FARINDREF = 0
NEARINDREF = 0
LEAFFAR = 0



***********************************************
but

NEAROFFPOS <> 0
FAROFFPOS <> 0
LEAFNEAR <> 0
LEADIST <> 0

Why this values doesn't appear in ZEROS after the REORG?
Is lacking of PTF's?


Thanks in advance an good look
Luis M Mtz
Mexico



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

Luis M Martinez Ch

Re: reorg index without updating catalog statistics
(in response to Michael Ebert)
Thanks Michael for your soon response.

Another interesting question here is .... the index holds its pages
logically in sequence, it doesn't matter that phisically it doesn't
maintain the correct order ... that is LP3 follows LP2, follows LP1 (LP=
Logical page), although, PP2 follows PP3 follows PP1 ( PP= Physical
page).DB2 ensures the index pages logical order.

When I reorg INDEX individually without exec RUN TABLESPACE over its
tablespace, It only claims the wasted space and organize the keys entries
for the CLUSTER IX or also for NON-CLUSTER index?


Could you explain me what happens at internal level please with REORG
INDEX?
Again, thk you very much

---------------------------------------------------------------------------------
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: reorg index without updating catalog statistics
(in response to Luis M Martinez Ch)
I have to reply through the web interface; replying by email gets me
a "host mail.IDUGDB2-L.ORG[207.36.18.105] said: 551 Message relaying to
this domain disabled (in reply to RCPT TO command)" delivery failure.
Anyone knows what's causing this? It was ok a few minutes before.
---------------

Without knowing any implementation details: a DB2 index (and probably most
other mass-storage indexes) is a variant of a B*-tree, which has the
property that all entries are always maintained in sorted order with
minimal overhead, and that all non-root pages are at least 50% full (at
least when doing sequential inserts, DB2 fills pages to 100% without page
fill balancing, so the last page may be less than 50% filled - a detail) so
that space wastage is kept within bounds.

However, such an index is a tree, a two-dimensional structure. When storing
it on disk in a VSAM file, it has to be embedded in a one-dimensional
linear structure. There are several possibilities for doing that. I would
suspect that the optimal storage would be the one from traversing the index
tree in pre-order. In that case, the REORG utility would write the physical
pages in that order on disk. This happens identically for clustering and
non-clustering indexes. The NEAR/FAROFFPOS[F] values are per index, but
they actually describe a table data property (the "sortedness" of the data
according to the index sort order) so they cannot be improved by a REORG
INDEX.

During "operation" of the index, the physical order would deteriorate. For
example, if a page needs to be split due to an insert, an additional empty
page would need to be provided. This could be a page left free due to
FREEPAGE, or which happened to become empty due to preceding deletes, or a
newly allocated page at the end of the VSAM page. (For a growing index with
FREEPAGE 0, this is the only possibility; so LEAFDIST will grow very
rapidly and to very high values for this type of situation).

REORG TABLESPACE has a simpler job, it just sorts the data in the order
determined by the clustering index. If there is no clustering index, it
doesn't even do that, it just establishes free space according to FREEPAGE
and PCTFREE and removes indirect references - a sort of defragmentation.

I hope I got all that correct, not being an SVL insider...

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



On Sat, 14 Jan 2006 12:54:06 -0600, Luis Miguel Mart=?ISO-8859-1?Q?
=C3=ADnez_Ch=C3=A1vez?= <[login to unmask email]> wrote:

>Thanks Michael for your soon response.
>
>Another interesting question here is .... the index holds its pages
>logically in sequence, it doesn't matter that phisically it doesn't
>maintain the correct order ... that is LP3 follows LP2, follows LP1 (LP=
>Logical page), although, PP2 follows PP3 follows PP1 ( PP= Physical
>page).DB2 ensures the index pages logical order.
>
>When I reorg INDEX individually without exec RUN TABLESPACE over its
>tablespace, It only claims the wasted space and organize the keys entries
>for the CLUSTER IX or also for NON-CLUSTER index?
>
>
>Could you explain me what happens at internal level please with REORG
>INDEX?
>Again, thk you very much
>

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

Chris Blaicher

Re: reorg index without updating catalog statistics
(in response to Michael Ebert)
Michael got it basically right, but here are a few more details if you
want them. The B-tree is built on the fly as the index leaf pages are
written.

Every index space has the same first 3 pages. Page 0 is the header
page, page 1 is the first space map page, and page 2 is the root page,
or the highest non-leaf page. This is typically followed by leaf pages
and lower level non-leaf pages which get inserted as they get filled.
Thus at some point you will find a non-leaf page separating two adjacent
leaf pages. A 3 level index is defined as where level 0 is the leaf
page, levels 1 and 2 are non-leaf pages, and level 3 is the root page.
Given a level 3 index, at some point in the object you will have a
series of pages like the following: a leaf page followed by a level 1
non-leaf page, followed by a level 2 non-leaf page, followed by a leaf
page. The level 3 root page is always at physical page 2.

Unless you have a very small index where there is just the root page and
a few leaf pages with no freepages, you will never get an index with
LEAFDIST of zero. Keeping the statistics for an index is not my
specialty, so I am not sure if the lowest possible value for that is 0
or 1.

DB2 allows you to specify the amount of free space you want in data
pages, index leaf pages and index non-leaf pages. The load and reorg
utilities will honor those values when loading or reorging the table.
Insert operations will use the free space before going to the next free
page.

When I was involved with performance management in a user environment,
the bottom line was did the application meet its performance objectives.
Some indexes could get way out of line before it affected performance,
while others had to be reorg'd on a frequent schedule.


Christopher Y. Blaicher
BMC Software, Inc.
Austin Development Labs
(512) 340-6154
BMC Software, Inc. makes no representations or promises regarding the
reliability, completeness, or accuracy of the information provided in
this discussion; all readers agree not to rely on this information or
take any action against BMC Software in response to this information.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Dr. Michael Ebert
Sent: Saturday, January 14, 2006 2:14 PM
To: [login to unmask email]
Subject: Re: [DB2-L] reorg index without updating catalog statistics

I have to reply through the web interface; replying by email gets me
a "host mail.IDUGDB2-L.ORG[207.36.18.105] said: 551 Message relaying
to
this domain disabled (in reply to RCPT TO command)" delivery failure.
Anyone knows what's causing this? It was ok a few minutes before.
---------------

Without knowing any implementation details: a DB2 index (and probably
most
other mass-storage indexes) is a variant of a B*-tree, which has the
property that all entries are always maintained in sorted order with
minimal overhead, and that all non-root pages are at least 50% full (at
least when doing sequential inserts, DB2 fills pages to 100% without
page
fill balancing, so the last page may be less than 50% filled - a detail)
so
that space wastage is kept within bounds.

However, such an index is a tree, a two-dimensional structure. When
storing
it on disk in a VSAM file, it has to be embedded in a one-dimensional
linear structure. There are several possibilities for doing that. I
would
suspect that the optimal storage would be the one from traversing the
index
tree in pre-order. In that case, the REORG utility would write the
physical
pages in that order on disk. This happens identically for clustering and
non-clustering indexes. The NEAR/FAROFFPOS[F] values are per index, but
they actually describe a table data property (the "sortedness" of the
data
according to the index sort order) so they cannot be improved by a REORG
INDEX.

During "operation" of the index, the physical order would deteriorate.
For
example, if a page needs to be split due to an insert, an additional
empty
page would need to be provided. This could be a page left free due to
FREEPAGE, or which happened to become empty due to preceding deletes, or
a
newly allocated page at the end of the VSAM page. (For a growing index
with
FREEPAGE 0, this is the only possibility; so LEAFDIST will grow very
rapidly and to very high values for this type of situation).

REORG TABLESPACE has a simpler job, it just sorts the data in the order
determined by the clustering index. If there is no clustering index, it
doesn't even do that, it just establishes free space according to
FREEPAGE
and PCTFREE and removes indirect references - a sort of defragmentation.

I hope I got all that correct, not being an SVL insider...

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

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

Luis M Martinez Ch

Re: reorg index without updating catalog statistics
(in response to Chris Blaicher)
Michael and Chris,

A lot of thanks for your comments

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