Re: dbspace of non-fragmented tables
Toine Schijvenaars wrote:
> 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.
I don't remember why I wrote it that way some time ago. Probably
it's just result of some cut and paste operations. So, you're right
regarding union and outer. BTW (again) sql of John Carlson is better
and I'll not post mine any more :-)
Regards
Vardan
>
> 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
> ==================================================================
>
--
vaar@geocities.com