Re: dbspace of non-fragmented tables
Toine Schijvenaars wrote:
>
> How can I determine the dbspace in which a certain table is allocated if
> this table is not fragmented.
>
> thanx,
>
> Toine
>
>
select tabname,
dbinfo('dbspace', partnum) dbspace
from systables
where tabid > 99
and tabtype = 'T'
and partnum != 0 -- not fragmented
union all
select tabname,
dbspace dbspace
from systables t, outer sysfragments f
where t.tabid > 99
and tabtype = 'T'
and partnum = 0 -- fragmented
and t.tabid = f.tabid
order by 1,2;
Vardan Aroustamian
--
vaar@geocities.com
Thanx a lot, it works fine, except we used UNION instead of UNION ALL
Vardan.Aroustamian@chase.com wrote:
> Toine Schijvenaars wrote:
> >
> > How can I determine the dbspace in which a certain table is allocated if
> > this table is not fragmented.
> >
> > thanx,
> >
> > Toine
> >
> >
>
> select tabname,
> dbinfo('dbspace', partnum) dbspace
> from systables
> where tabid > 99
> and tabtype = 'T'
> and partnum != 0 -- not fragmented
> union all
> select tabname,
> dbspace dbspace
> from systables t, outer sysfragments f
> where t.tabid > 99
> and tabtype = 'T'
> and partnum = 0 -- fragmented
> and t.tabid = f.tabid
> order by 1,2> ;
>
> Vardan Aroustamian
>
> --
>
> vaar@geocities.com
--
==================================================================
Drs. Toine Schijvenaars Baron van Nagellstraat 136A
System Development Postbus 113
Roccade Public 3770 AC Barneveld
mobiel: 06 51 28 29 47 tel: 0342 403693
e-mail: tsch00@solair1.inter.nl.net fax: 0342 403400
==================================================================
Our ultimate statement is:
select tabname,
dbinfo('dbspace', partnum) dbspace
from systables
where tabid > 99
and tabtype = 'T'
and partnum != 0 -- not fragmented
union
select tabname,
dbspace dbspace
from systables t, outer sysfragments f
where t.tabid > 99
and tabtype = 'T'
and partnum = 0 -- fragmented
and t.tabid = f.tabid
and f.fragtype <> 'I'
order by 1,2;
We' ve added fragtype, although we're not certain whether the outer clause is
necessary in the second select statement.
Vardan.Aroustamian@chase.com wrote:
> Toine Schijvenaars wrote:
> >
> > How can I determine the dbspace in which a certain table is allocated if
> > this table is not fragmented.
> >
> > thanx,
> >
> > Toine
> >
> >
>
> select tabname,
> dbinfo('dbspace', partnum) dbspace
> from systables
> where tabid > 99
> and tabtype = 'T'
> and partnum != 0 -- not fragmented
> union all
> select tabname,
> dbspace dbspace
> from systables t, outer sysfragments f
> where t.tabid > 99
> and tabtype = 'T'
> and partnum = 0 -- fragmented
> and t.tabid = f.tabid
> order by 1,2> ;
>
> Vardan Aroustamian
>
> --
>
> vaar@geocities.com
--
==================================================================
Drs. Toine Schijvenaars Baron van Nagellstraat 136A
System Development Postbus 113
Roccade Public 3770 AC Barneveld
mobiel: 06 51 28 29 47 tel: 0342 403693
e-mail: tsch00@solair1.inter.nl.net fax: 0342 403400
==================================================================