Invalid output for index after REORG utility

SUBSCRIBE DB2-L sn.pr

Invalid output for index after REORG utility
Hi All,

We have a problem after running REORG on indexes of a table .It happens
everytime to one index ( primary or alternate one).The Reorg job is ended
with zero condition which shows It is ok ,but the number of rows for the index
is not equal with the number of rows for the table.It seems we lost some rows
after running REORG ,although the REORG job is ended with zero
code.This is happenning to primary index or an alternate index .
My guess is that there should be problem with some pages into index, but
there is no possibility to run check utility before Reorg for the reason of high
number of records on these tables.
The table is partitioned one.
In addition , we're working with DB2 v7.I would be so greatful if anybody can
help.

Regards
Sanaz P.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: Invalid output for index after REORG utility
(in response to SUBSCRIBE DB2-L sn.pr)
Hi Sanaz

This does indeed sound odd, but so does your description of the problem!

So, some more questions to try and understand what is going on

1. You say this problem happens to " primary index or an alternate index "
- so it is NOT ALWAYS the same index?
2. Are you running a full tablespace reorg or a partition level one
3. When you say " number of rows for the index is not equal with the number
of rows for the table " - I assume you mean the number of KEYS for the index
is not equal to the number of rows in the table? Is it a unique index? If
not, the number of keys will always be less than the number of rows in the
table (due to duplicates)
4. When are you planning to migrate to Version 8?

Phil Grainger

Grainger Database Solutions Ltd

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of SUBSCRIBE DB2-L
sn.pr
Sent: 20 January 2010 08:39
To: [login to unmask email]
Subject: [DB2-L] Invalid output for index after REORG utility

Hi All,

We have a problem after running REORG on indexes of a table .It happens
everytime to one index ( primary or alternate one).The Reorg job is ended
with zero condition which shows It is ok ,but the number of rows for the
index
is not equal with the number of rows for the table.It seems we lost some
rows
after running REORG ,although the REORG job is ended with zero
code.This is happenning to primary index or an alternate index .
My guess is that there should be problem with some pages into index, but
there is no possibility to run check utility before Reorg for the reason of
high
number of records on these tables.
The table is partitioned one.
In addition , we're working with DB2 v7.I would be so greatful if anybody
can
help.

Regards
Sanaz P.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every
level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: Invalid output for index after REORG utility
(in response to Phil Grainger)
they only thing that springs to mind is this old HIPER from 2006...

PK32423 (UK19095) MISSING DATA CHANGES (INSERT/UPDATE/DELETE) AFTER REORG
SHRLEVEL CHANGE SUCCESSFUL COMPLETION 06/10/05 PTF PECHANGE

But I would sincerely hope you have it applied!!



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




"SUBSCRIBE DB2-L sn.pr" <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
20.01.2010 09:39
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] Invalid output for index after REORG utility






Hi All,

We have a problem after running REORG on indexes of a table .It happens
everytime to one index ( primary or alternate one).The Reorg job is ended
with zero condition which shows It is ok ,but the number of rows for the
index
is not equal with the number of rows for the table.It seems we lost some
rows
after running REORG ,although the REORG job is ended with zero
code.This is happenning to primary index or an alternate index .
My guess is that there should be problem with some pages into index, but
there is no possibility to run check utility before Reorg for the reason
of high
number of records on these tables.
The table is partitioned one.
In addition , we're working with DB2 v7.I would be so greatful if anybody
can
help.

Regards
Sanaz P.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical
presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI,
Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for
every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-content/index.html has THOUSANDS of free technical presentations!
DB2 LUW, DB2 z/OS, Performance, Installation, Tuning, Coding, BI, Warehouses, - among
many more categories of help waiting for you!
Whether you are an old hand or a DB2 newbie, we have presentations for every level.
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

SUBSCRIBE DB2-L sn.pr

Re: Invalid output for index after REORG utility
(in response to Roy Boxwell)
Hi Phil,

You're right, It's not always happening to same index and It's a partition level
one .For the third question,they are unique ones.
I mean for primary index , the number of keys should be equal to number of
rows for each partion , which It's not sometimes(after running REORG for a
partition).
For Alternate indexes,the number of keys for them should be the same
(according to key combinations w have on them(3 alternate indexes as e.g)),
which after running REORG for one of them , It 's different .Please notice that
REORG job is ended with zero condition.

In addition, we're migrating our DB2 to db2 v8,but It takes time to be
operational.So we have to solve this problem now.

Regards
Sanaz P.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

SUBSCRIBE DB2-L sn.pr

Re: Invalid output for index after REORG utility
(in response to SUBSCRIBE DB2-L sn.pr)
Hi Roy,

Many thanks for your good responses always.

I read the explanation for this PTF .It says that this is the fix for Missing data
after REORG, for the data being updated, inserted,deleted in the mean time
RORG was running.But our problem is that we do'nt have any of them during
the running utility, just we read some rows .

Do you know If it's effective for this problem we have?

Thanks & Regards

Sanaz P.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Roy Boxwell

Re: Invalid output for index after REORG utility
(in response to SUBSCRIBE DB2-L sn.pr)
then no...but simply losing data is very very bad...are you sure no-one is
doing online work? If yes then this apar does address the problem.
One really horrible thought is that with V7 you could use an external sort
routine (SYNCSORT etc.) - Could it be that someone has done something
to this sort? Perhaps they have used a hard coded exit routine....E15 or
such...I can never remember the names but I do recall that it was possible

to do horrible things without really be aware of it.....just a
thought.....at least in V8 you use DFSORT which is much more "controlled"
in DB2....



Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Siegfried Fürst, Gerhard Schubert




"SUBSCRIBE DB2-L sn.pr" <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
21.01.2010 07:20
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
Re: [DB2-L] Invalid output for index after REORG utility






Hi Roy,

Many thanks for your good responses always.

I read the explanation for this PTF .It says that this is the fix for
Missing data
after REORG, for the data being updated, inserted,deleted in the mean time

RORG was running.But our problem is that we do'nt have any of them during
the running utility, just we read some rows .

Do you know If it's effective for this problem we have?

Thanks & Regards

Sanaz P.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions
Journal
Technical atricles from world famous authors in DB2's most prestigious,
peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's DB2-L


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

SUBSCRIBE DB2-L sn.pr

Re: Invalid output for index after REORG utility
(in response to Roy Boxwell)
Roy,

We're doing online work ,but just reading from that table in the mean time.

Regards
Sanaz Pourdarab

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/solutions-journal.html - home of the IDUG Solutions Journal
Technical atricles from world famous authors in DB2's most prestigious, peer reviewed
magazine now on-line!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L