index name stored in sysextents's column "tabname"
Roger found index names, not just table names, in sysextents.tabname, which skewed his query counting extents per table before reorganising. John Miller suggested getting extent counts from the partition header instead (join systabnames to sysptnhdr on partnum and check nextns), but the same index names still appeared. Obnoxio asked if they were detached indexes; Roger said no. Art Kagel explained they are: since roughly IDS 7.31/9.21, indexes are detached (or "semi-attached") by default, with their own extents separate from table data, so those entries are expected.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hello ,
sysextents table 's column "tabname", supposed to store a table's
name.
When I selected from this table, and found that there are many index
name stored .
I used to run the SQL command:
SELECT tabname,count(*) from sysextents group by tabname
having count(*) > 10To calculate the table's extent number, and do the reorgranization of
the table.
I like to know why it store index-name on tabname column,
and how can I tell the table's extent number ?
Roger:
The number of extents a table has is stored in the partition header
which is easy to access using the sysptnhdr table.
How about
select A.tabname[1,40], B.nextns
from systabnames A, sysptnhdr B
where A.partnum = B.partnum
and B.nextns > 10
order by 2 DESC
John
roger@star2000.com.tw wrote:
> Hello ,
> sysextents table 's column "tabname", supposed to store a table's
> name.
> When I selected from this table, and found that there are many index
> name stored .
> I used to run the SQL command:
> SELECT tabname,count(*) from sysextents group by tabname
> having count(*) > 10> To calculate the table's extent number, and do the reorgranization of
> the table.
> I like to know why it store index-name on tabname column,
> and how can I tell the table's extent number ?
>
John Miller 寫道：
> Roger:
>
>
> The number of extents a table has is stored in the partition header
> which is easy to access using the sysptnhdr table.
>
> How about
>
> select A.tabname[1,40], B.nextns
> from systabnames A, sysptnhdr B
> where A.partnum = B.partnum
> and B.nextns > 10
> order by 2 DESC
>
> John
>
>
>
> roger@star2000.com.tw wrote:
> > Hello ,
> > sysextents table 's column "tabname", supposed to store a table's
> > name.
> > When I selected from this table, and found that there are many index
> > name stored .
> > I used to run the SQL command:
> > SELECT tabname,count(*) from sysextents group by tabname
> > having count(*) > 10> > To calculate the table's extent number, and do the reorgranization of
> > the table.
> > I like to know why it store index-name on tabname column,
> > and how can I tell the table's extent number ?
> >
I run John's SQL Command:
select A.tabname[1,40], B.nextns
from systabnames A, sysptnhdr B
where A.partnum = B.partnum
and B.nextns > 10
order by 2 DESC
I got the same result , the "tabname" still contain index-name.
roger
roger@star2000.com.tw said:
>
> John Miller 寫éï¼
>
>> Roger:
>>
>>
>> The number of extents a table has is stored in the partition header
>> which is easy to access using the sysptnhdr table.
>>
>> How about
>>
>> select A.tabname[1,40], B.nextns
>> from systabnames A, sysptnhdr B
>> where A.partnum = B.partnum
>> and B.nextns > 10
>> order by 2 DESC
>>
>> John
>>
>>
>>
>> roger@star2000.com.tw wrote:
>> > Hello ,
>> > sysextents table 's column "tabname", supposed to store a table's
>> > name.
>> > When I selected from this table, and found that there are many index
>> > name stored .
>> > I used to run the SQL command:
>> > SELECT tabname,count(*) from sysextents group by tabname
>> > having count(*) > 10>> > To calculate the table's extent number, and do the reorgranization of
>> > the table.
>> > I like to know why it store index-name on tabname column,
>> > and how can I tell the table's extent number ?
>> >
> I run John's SQL Command:
> select A.tabname[1,40], B.nextns
> from systabnames A, sysptnhdr B
> where A.partnum = B.partnum
> and B.nextns > 10
> order by 2 DESC
>
> I got the same result , the "tabname" still contain index-name.
Detached index?
--
Bye now,
Obnoxio
"... no bill is required as no value was provided."
-- Christine Normile
Obnoxio The Clown 寫道：
> roger@star2000.com.tw said:
> >
> > John Miller 寫道：
> >
> >> Roger:
> >>
> >>
> >> The number of extents a table has is stored in the partition header
> >> which is easy to access using the sysptnhdr table.
> >>
> >> How about
> >>
> >> select A.tabname[1,40], B.nextns
> >> from systabnames A, sysptnhdr B
> >> where A.partnum = B.partnum
> >> and B.nextns > 10
> >> order by 2 DESC
> >>
> >> John
> >>
> >>
> >>
> >> roger@star2000.com.tw wrote:
> >> > Hello ,
> >> > sysextents table 's column "tabname", supposed to store a table's
> >> > name.
> >> > When I selected from this table, and found that there are many index
> >> > name stored .
> >> > I used to run the SQL command:
> >> > SELECT tabname,count(*) from sysextents group by tabname
> >> > having count(*) > 10> >> > To calculate the table's extent number, and do the reorgranization of
> >> > the table.
> >> > I like to know why it store index-name on tabname column,
> >> > and how can I tell the table's extent number ?
> >> >
> > I run John's SQL Command:
> > select A.tabname[1,40], B.nextns
> > from systabnames A, sysptnhdr B
> > where A.partnum = B.partnum
> > and B.nextns > 10
> > order by 2 DESC
> >
> > I got the same result , the "tabname" still contain index-name.
>
> Detached index?
>
> --
> Bye now,
> Obnoxio
>
> "... no bill is required as no value was provided."
> -- Christine Normile
no ! normal index.
roger
roger@star2000.com.tw wrote:
> Obnoxio The Clown 寫道：
>
>
>>roger@star2000.com.tw said:
>>
>>>John Miller 寫道：
>>>
>>>
>>>>Roger:
>>>>
>>>>
>>>>The number of extents a table has is stored in the partition header
>>>>which is easy to access using the sysptnhdr table.
>>>>
>>>>How about
>>>>
>>>>select A.tabname[1,40], B.nextns
>>>>from systabnames A, sysptnhdr B
>>>>where A.partnum = B.partnum
>>>>and B.nextns > 10
>>>>order by 2 DESC
>>>>
>>>>John
>>>>
>>>>
>>>>
>>>>roger@star2000.com.tw wrote:
>>>>
>>>>>Hello ,
>>>>> sysextents table 's column "tabname", supposed to store a table's
>>>>>name.
>>>>>When I selected from this table, and found that there are many index
>>>>>name stored .
>>>>>I used to run the SQL command:
>>>>> SELECT tabname,count(*) from sysextents group by tabname
>>>>> having count(*) > 10>>>>>To calculate the table's extent number, and do the reorgranization of
>>>>>the table.
>>>>>I like to know why it store index-name on tabname column,
>>>>>and how can I tell the table's extent number ?
>>>>>
>>>
>>>I run John's SQL Command:
>>> select A.tabname[1,40], B.nextns
>>>from systabnames A, sysptnhdr B
>>>where A.partnum = B.partnum
>>>and B.nextns > 10
>>>order by 2 DESC
>>>
>>>I got the same result , the "tabname" still contain index-name.
>>
>>Detached index?
>>
>>--
>>Bye now,
>>Obnoxio
>>
>>"... no bill is required as no value was provided."
>>-- Christine Normile
>
>
> no ! normal index.
NO! What version are you using? Since around versions 7.31 & 9.21 all
indexes except those created by an earlier version of IDS are detached (or
at least what IBM internally calls semi-attached) unless an environment or
onconfig variable reestablishes the old behavior or the IN TABLE clause is
included. Those extents belong to detached indexes. Semi-attached indexes
are indexes that are housed implicitely (without an IN clause) in the same
dbspace, or using fragmentation schema, as the table. Their extents are
independent, not interleaved with table data pages as they were in older
versions. This was done to make dropping semi-attached indexes as
instantaneous as fully detached ones.
Art S. Kagel