SEO

I tried running following query to get the total size of index for a
given table but I don't get any results. My indexes are in separate
dbspace as of row data:

SELECT st.tabname, sp.npused as indexpages,
(sp.npused * 2) as indexKB
FROM sysmaster:systabnames st,
sysmaster:systabnames st2,
sysmaster:sysptnhdr sp,
sysmaster:sysdbspaces sd
WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
AND st2.partnum = sp.lockid
AND st.partnum = sp.partnum
AND sd.dbsnum = (sp.partnum /1048576)
AND st.tabname != st2.tabname
UNION ALL
SELECT st.tabname, sp.npused as indexpages,
((sp.npused - npdata) * 2) as indexKB
FROM sysmaster:systabnames st,
sysmaster:systabnames st2,
sysmaster:sysptnhdr sp,
sysmaster:sysdbspaces sd
WHERE st2.dbsname = 'mydatabase' AND st2.tabname = 'mytable'
AND st2.partnum = sp.lockid
AND st.partnum = sp.partnum
AND sd.dbsnum = (sp.partnum /1048576)
AND st.tabname = st2.tabname

I also tried below query to get the total size of index but I am not
sure if this is correct:

select sysptprof.dbsname database,
systabnames.tabname indexname,
keylen,
round((ti_nptotal* sysshmvals.sh_pagesize)/1024,0) totalsize,
sum(lockreqs +
lockwts +
deadlks +
lktouts +
isreads +
iswrites +
isrewrites +
isdeletes +
bufreads +
bufwrites +
seqscans +
pagreads +
pagwrites) totalops
from sysptprof, sysptnkey, systabnames, sysshmvals, systabinfo
where
sysptnkey.partnum = sysptprof.partnum
and
sysptnkey.partnum = systabnames.partnum
and
systabnames.partnum = systabinfo.ti_partnum
and
systabnames.tabname not matches "*sys*"
group by 1,2,3,4

On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:
> I tried running following query to get the total size of index for a
> given table but I don't get any results. My indexes are in separate
> dbspace as of row data:
>
<SNIP>

Try this one Mohitan:

select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname
database,
st2.tabname partition, nptotal, npused,
npdata, (npused - npdata) npindex
from systabnames st1, systabnames st2, sysptnhdr sph
where st1.partnum = sp.lockid and st2.partnum = sph.partnum
and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable'
order by 2, 3, 1;

To filter for a particular index add a filter on: " and st2.tabname =
'myindexname' "

Art S. Kagel

On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a
> > given table but I don't get any results. My indexes are in separate
> > dbspace as of row data:
>
> <SNIP>
>
> Try this one Mohitan:
>
> select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname
> database,
> st2.tabname partition, nptotal, npused,
> npdata, (npused - npdata) npindex
> from systabnames st1, systabnames st2, sysptnhdr sph
> where st1.partnum = sp.lockid and st2.partnum = sph.partnum
> and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable'
> order by 2, 3, 1;
>
> To filter for a particular index add a filter on: " and st2.tabname =
> 'myindexname' "
>
> Art S. Kagel

the " sp.lockid " should be " sp2.lockid "

Art S. Kagel

On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
>
>
> > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a
> > > given table but I don't get any results. My indexes are in separate
> > > dbspace as of row data:
>
> > <SNIP>
>
> > Try this one Mohitan:
>
> > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname
> > database,
> > st2.tabname partition, nptotal, npused,
> > npdata, (npused - npdata) npindex
> > from systabnames st1, systabnames st2, sysptnhdr sph
> > where st1.partnum = sp.lockid and st2.partnum = sph.partnum
> > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable'
> > order by 2, 3, 1;
>
> > To filter for a particular index add a filter on: " and st2.tabname =
> > 'myindexname' "
>
> > Art S. Kagel
>
> the " sp.lockid " should be " sp2.lockid "
>
> Art S. Kagel

AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

Trying again:

the " sp.lockid " should be " sph.lockid "

Art S. Kagel

On Sep 4, 12:22 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
>
>
> > On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
> > > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a
> > > > given table but I don't get any results. My indexes are in separate
> > > > dbspace as of row data:
>
> > > <SNIP>
>
> > > Try this one Mohitan:
>
> > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname
> > > database,
> > > st2.tabname partition, nptotal, npused,
> > > npdata, (npused - npdata) npindex
> > > from systabnames st1, systabnames st2, sysptnhdr sph
> > > where st1.partnum = sp.lockid and st2.partnum = sph.partnum
> > > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable'
> > > order by 2, 3, 1;
>
> > > To filter for a particular index add a filter on: " and st2.tabname =
> > > 'myindexname' "
>
> > > Art S. Kagel
>
> > the " sp.lockid " should be " sp2.lockid "
>
> > Art S. Kagel
>
> AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>
> Trying again:
>
> the " sp.lockid " should be " sph.lockid "
>
> Art S. Kagel

Few more questions:

1. What does npdata stand for. I didn't understand npused - npdata in
the above query. I was earlier thinking of something like npused * 2k
page size.
2. One more thing I don't understand is this:
For eg: Table A has index I1 on 1 key of 4 bytes and index I2 on
2 keys of 8bytes, so logically total index size of I1 should be half
of the size of I2. But, when I see the statistics of my database I
don't see that relation. Is there something else because of which it
doesn't compute the way I think it should.

On Sep 5, 12:59 am, mohitanch...@gmail.com wrote:
> On Sep 4, 12:22 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
>
>
> > On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
> > > On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
> > > > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a
> > > > > given table but I don't get any results. My indexes are in separate
> > > > > dbspace as of row data:
>
> > > > <SNIP>
>
> > > > Try this one Mohitan:
>
> > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname
> > > > database,
> > > > st2.tabname partition, nptotal, npused,
> > > > npdata, (npused - npdata) npindex
> > > > from systabnames st1, systabnames st2, sysptnhdr sph
> > > > where st1.partnum = sp.lockid and st2.partnum = sph.partnum
> > > > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable'
> > > > order by 2, 3, 1;
>
> > > > To filter for a particular index add a filter on: " and st2.tabname =
> > > > 'myindexname' "
>
> > > > Art S. Kagel
>
> > > the " sp.lockid " should be " sp2.lockid "
>
> > > Art S. Kagel
>
> > AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>
> > Trying again:
>
> > the " sp.lockid " should be " sph.lockid "
>
> > Art S. Kagel
>
> Few more questions:
>
> 1. What does npdata stand for. I didn't understand npused - npdata in
> the above query. I was earlier thinking of something like npused * 2k
> page size.


NPTOTAL - Number of Pages Total - The total number of pages allocated
to the partition.
NPUSED - Number of Pages Used - The number of allocated pages that
actually have something written on them.
NPDATA - Number of Pages of Data - The number of dbspace pages marked
as data pages (originating, extension, or remainder)
NPUSED-NPDATA - The number of used pages which are NOT data pages -
this includes bitmap pages (1 per 4096 total pages) and index pages

The number of Index pages in a partition is actually: (NPUSED -
(NPDATA + ((NPTOTAL + 4095)/4096)))

Your NPUSED * 2 will convert pages to KB IFF pagesize is 2K for the
dbspace. In IDS 10.00+ it's better to use the pagesize column from
sysptnhdr to convert to KB. For earlier releases you should still use
sysshmvals.sh_pagesize rather than hard code '2'.

> 2. One more thing I don't understand is this:
> For eg: Table A has index I1 on 1 key of 4 bytes and index I2 on
> 2 keys of 8bytes, so logically total index size of I1 should be half
> of the size of I2. But, when I see the statistics of my database I
> don't see that relation. Is there something else because of which it
> doesn't compute the way I think it should.

Yeah, that's not going to work the way you think it will. The 8 byte
index will take up more space than the 4byte index but not twice as
much. That's partly because each index entry contains the rowid of
each row in addition to the key values. Also non-unique indexes
compress multiple rowids with the same non-unique key into a smaller
number of leave pages. Finally the leaf pages are not completely full
in any index and the slack is not directly related to the number or
size of the individual keys.

Art S. Kagel

On Sep 5, 7:21 am, "Art S. Kagel" <art.ka...@gmail.com> wrote:
> On Sep 5, 12:59 am, mohitanch...@gmail.com wrote:
>
>
>
>
>
> > On Sep 4, 12:22 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
> > > On Sep 4, 3:21 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
> > > > On Sep 4, 3:15 pm, "Art S. Kagel" <art.ka...@gmail.com> wrote:
>
> > > > > On Sep 4, 2:15 pm, mohitanch...@gmail.com wrote:> I tried running following query to get the total size of index for a
> > > > > > given table but I don't get any results. My indexes are in separate
> > > > > > dbspace as of row data:
>
> > > > > <SNIP>
>
> > > > > Try this one Mohitan:
>
> > > > > select dbinfo( 'dbspace', sph.partnum ) dbspace, st2.dbsname
> > > > > database,
> > > > > st2.tabname partition, nptotal, npused,
> > > > > npdata, (npused - npdata) npindex
> > > > > from systabnames st1, systabnames st2, sysptnhdr sph
> > > > > where st1.partnum = sp.lockid and st2.partnum = sph.partnum
> > > > > and st1.dbsname = 'mydatabase' AND st1.tabname = 'mytable'
> > > > > order by 2, 3, 1;
>
> > > > > To filter for a particular index add a filter on: " and st2.tabname =
> > > > > 'myindexname' "
>
> > > > > Art S. Kagel
>
> > > > the " sp.lockid " should be " sp2.lockid "
>
> > > > Art S. Kagel
>
> > > AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
>
> > > Trying again:
>
> > > the " sp.lockid " should be " sph.lockid "
>
> > > Art S. Kagel
>
> > Few more questions:
>
> > 1. What does npdata stand for. I didn't understand npused - npdata in
> > the above query. I was earlier thinking of something like npused * 2k
> > page size.
>
> NPTOTAL - Number of Pages Total - The total number of pages allocated
> to the partition.
> NPUSED - Number of Pages Used - The number of allocated pages that
> actually have something written on them.
> NPDATA - Number of Pages of Data - The number of dbspace pages marked
> as data pages (originating, extension, or remainder)
> NPUSED-NPDATA - The number of used pages which are NOT data pages -
> this includes bitmap pages (1 per 4096 total pages) and index pages
>
> The number of Index pages in a partition is actually: (NPUSED -
> (NPDATA + ((NPTOTAL + 4095)/4096)))
>
> Your NPUSED * 2 will convert pages to KB IFF pagesize is 2K for the
> dbspace. In IDS 10.00+ it's better to use the pagesize column from
> sysptnhdr to convert to KB. For earlier releases you should still use
> sysshmvals.sh_pagesize rather than hard code '2'.
>
> > 2. One more thing I don't understand is this:
> > For eg: Table A has index I1 on 1 key of 4 bytes and index I2 on
> > 2 keys of 8bytes, so logically total index size of I1 should be half
> > of the size of I2. But, when I see the statistics of my database I
> > don't see that relation. Is there something else because of which it
> > doesn't compute the way I think it should.
>
> Yeah, that's not going to work the way you think it will. The 8 byte
> index will take up more space than the 4byte index but not twice as
> much. That's partly because each index entry contains the rowid of
> each row in addition to the key values. Also non-unique indexes
> compress multiple rowids with the same non-unique key into a smaller
> number of leave pages. Finally the leaf pages are not completely full
> in any index and the slack is not directly related to the number or
> size of the individual keys.
>
> Art S. Kagel- Hide quoted text -
>
> - Show quoted text -

Is 4096 the limit on number of bitmap pages a partition can have

mohitanchlia@gmail.com wrote:
>
> Is 4096 the limit on number of bitmap pages a partition can have
>

Yes, the maximum number of pages per fragment (partition) is 16,775,134
pages. Since one bit map page can map 4096 pages you need 4096 pages to
map 16,775,134 pages.

On Sep 6, 12:05 am, Claus Samuelsen <c...@dk.ibm.com> wrote:
> mohitanch...@gmail.com wrote:
>
> > Is 4096 the limit on number of bitmap pages a partition can have
>
> Yes, the maximum number of pages per fragment (partition) is 16,775,134
> pages. Since one bit map page can map 4096 pages you need 4096 pages to
> map 16,775,134 pages.

What's lockid in sysptnhdr mean. Is it the dbspace number to which the
fragment is attached to ?

On Oct 1, 12:19 pm, mohitanch...@gmail.com wrote:
> On Sep 6, 12:05 am, Claus Samuelsen <c...@dk.ibm.com> wrote:
>
> > mohitanch...@gmail.com wrote:
>
> > > Is 4096 the limit on number of bitmap pages a partition can have
>
> > Yes, the maximum number of pages per fragment (partition) is 16,775,134
> > pages. Since one bit map page can map 4096 pages you need 4096 pages to
> > map 16,775,134 pages.
>
> What's lockid in sysptnhdr mean. Is it the dbspace number to which the
> fragment is attached to ?

It's the address of the initial fragment of the table itself (or only
fragment if the table's not fragmented). That is the address that's
locked for a table level lock.

Art S. Kagel