How to find Page number or RID of a record

Pradeep Gunjala

How to find Page number or RID of a record
Hello Everyone,

How can I find out that if 2 records are ended up on the same index page and
data page or on different pages.

In other words how can I find out a perticular record is stored on what data
page and the corresponding index is stored on what index page.

Thanks,
Pradeep

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Avram Friedman

Re: How to find Page number or RID of a record
(in response to Pradeep Gunjala)
Pradeep,
The first question you must ask is "Am I authorized to run full backups
(before) and full diagnostics and recovries after the proposed investigation"
Should the answer be no proceed no further.

There are very subtile variations of the following procedure that can and will
distroy your data.

Now that you are comfortable with the risk there is a DB2 utility that does
what you want.

Its the REPAIR utility.

Start with the index first.
The LOCATE command will allow you to locate an index entry with a given hex
key.
The DUMP command will display the index entry and more.
From the DUMP display you should beable to observe its RBA address and the
Pointer to the page space location.

Note there are a few things that can make this very complex.
A) If the key you are looking for is the first or last key in a leaf page you may
get non leaf page hits from the locate. You have to look at every dumped
page and verify its a leaf page.

B) If any of the columns in the index have been subjects of ALTERS you will
have to construct your searches for all possible column versions.

C) If there have been any deletes of index rows (because of deletes or
updates in the base table even if the work load abended) you will need to
identify psudo deletes and ignore them.

D) If the index is not unique (In which case your original question is some
what silly) the format of leaf pages changes.

E) There are other unusual conditions like variable length keys that can make
things tricky.

The structure of index pages is in the diagnostic reference.

Another options at your disposal is to run a few selects with the appropate
traces on and look at the traces.


It is a big effort as a first time exercise. I comend you on your interest.

Regards
Avram Friedman

On Thu, 24 Jan 2008 19:50:01 +0000, Pradeep Gunjala
<[login to unmask email]> wrote:

>Hello Everyone,
>
>How can I find out that if 2 records are ended up on the same index page and
>data page or on different pages.
>
>In other words how can I find out a perticular record is stored on what data
>page and the corresponding index is stored on what index page.
>
>Thanks,
>Pradeep
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list
archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services at
http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Pradeep Gunjala

Re: How to find Page number or RID of a record
(in response to Avram Friedman)
Avram,

Thank you for this valuable information. I don't know if I can try this
today but definitely I will try this in my free time.

Thanks,
Pradeep




Avram Friedman <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/24/2008 04:39 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] How to find Page number or RID of a record






Pradeep,
The first question you must ask is "Am I authorized to run full backups
(before) and full diagnostics and recovries after the proposed
investigation"
Should the answer be no proceed no further.

There are very subtile variations of the following procedure that can and
will
distroy your data.

Now that you are comfortable with the risk there is a DB2 utility that
does
what you want.

Its the REPAIR utility.

Start with the index first.
The LOCATE command will allow you to locate an index entry with a given
hex
key.
The DUMP command will display the index entry and more.
From the DUMP display you should beable to observe its RBA address and the

Pointer to the page space location.

Note there are a few things that can make this very complex.
A) If the key you are looking for is the first or last key in a leaf page
you may
get non leaf page hits from the locate. You have to look at every dumped
page and verify its a leaf page.

B) If any of the columns in the index have been subjects of ALTERS you
will
have to construct your searches for all possible column versions.

C) If there have been any deletes of index rows (because of deletes or
updates in the base table even if the work load abended) you will need to
identify psudo deletes and ignore them.

D) If the index is not unique (In which case your original question is
some
what silly) the format of leaf pages changes.

E) There are other unusual conditions like variable length keys that can
make
things tricky.

The structure of index pages is in the diagnostic reference.

Another options at your disposal is to run a few selects with the
appropate
traces on and look at the traces.


It is a big effort as a first time exercise. I comend you on your
interest.

Regards
Avram Friedman

On Thu, 24 Jan 2008 19:50:01 +0000, Pradeep Gunjala
<[login to unmask email]> wrote:

>Hello Everyone,
>
>How can I find out that if 2 records are ended up on the same index page
and
>data page or on different pages.
>
>In other words how can I find out a perticular record is stored on what
data
>page and the corresponding index is stored on what index page.
>
>Thanks,
>Pradeep
>
>The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list
archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug
under the Listserv tab. While at the site, you can also access the IDUG
Online Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services
at
http://www.idug.org/lsms

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information, and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms