How to know the table name
Hello,
I used the following scrip to find the unused page of the index that more than
10000, Do anybody know how to find the table name based on the partition
number of the index?
------------------------------------------------------------------
--This is a SQL to find indexes whose unused page is more than 10000
dbaccess - <<!
database sysmaster;
unload to idx_partnum.unl delimiter " "
select partnum from sysptnhdr where partnum != lockid
and npused > 100000 and rowsize > 50;!
while read PN
do
dbaccess - <<!
database sysmaster;
select $PN partnum, count(*) empty_pages
from syspaghdr
where pg_nslots = 0 and
mod(round(pg_flags / 16), 2) != 0 -- PG_BTREE 0x0010 = 16
and pg_partnum = $PN;
!
done < ./idx_partnum.unl
-------------------------------------------------------------------
Look up the index name in sysmaster:systabnames. The lockid of that partnum
will be the partnum of the table or table fragment to which the index or
index fragment belongs.
Art
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Oninit, the IIUG, nor any other organization
with which I am associated either explicitly or implicitly. Neither do
those opinions reflect those of other individuals affiliated with any entity
with which I am affiliated nor those of the entities themselves.
On Sun, Sep 20, 2009 at 11:15 AM, CHUAN LU <luchuan114@sina.com> wrote:
> Hello,
>
> I used the following scrip to find the unused page of the index that more
> than
> 10000, Do anybody know how to find the table name based on the partition
> number of the index?
> ------------------------------------------------------------------
> --This is a SQL to find indexes whose unused page is more than 10000
> dbaccess - <<!
> database sysmaster;
> unload to idx_partnum.unl delimiter " "
> select partnum from sysptnhdr where partnum != lockid
> and npused > 100000 and rowsize > 50;> !
>
> while read PN
> do
> dbaccess - <<!
> database sysmaster;>
> select $PN partnum, count(*) empty_pages
> from syspaghdr
> where pg_nslots = 0 and
>
> mod(round(pg_flags / 16), 2) != 0 -- PG_BTREE 0x0010 = 16
>
> and pg_partnum = $PN;
> !
> done < ./idx_partnum.unl
> -------------------------------------------------------------------
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--00151744805e86b63404740bdc07