RE: Table Extents
Sean;
I downloaded this from the iiug site. It has worked well for me so
far.
-- tabextplan.sql
-- Discription: Display extents and proposed new extent sizes
-- NOTE: if you page size is 4096 change the "* 2 { Your systems page " to 4
-- if you growth factor is greater the 20% per year make the nessary
-- changes.
database sysmaster;
select dbsname,
tabname,
count(*) num_of_extents,
sum (pe_size ) pages_used,
round (sum (pe_size )
* 2 { Your systems page size in KB }
* 1.2 { Add 20% Growth factor })
ext_size, { First Extent Size in KB }
round (sum (pe_size )
* 2 { Your systems page size in KB }
* .2 { Estimated 20% Yearly Growth })
next_size { Next Extent Size in KB }
from systabnames, sysptnext
where partnum = pe_partnum
group by 1, 2 having count(*) > 15
order by 3 desc, 4 desc;
> -----Original Message-----
> From: Sean Kelsey [SMTP:Sean.Kelsey@lsa0103.wins.icl.co.uk]
> Sent: Thursday, January 07, 1999 8:57 AM
> To: informix-list@iiug.org
> Subject: Table Extents
>
> Happy New Year to One and All !!
>
> Chit-chat over, on with the problem !!
>
> OnLine 7.24.UC5 HP-UX 11
>
> Its that time of year when DBA's accross the world are establishing how
> fragmented our systems are and whether or not its worth re-jigging a few
> of
> the big tables. I am no exception.
>
> My problem is that I have over 300 tables ( in a BaaN database of over
> 14000 ) that have in excess of 32 fragments, all of which I would like to
> de-frag ! I have identified the tables, I know how big I want to make them
> but, and this is the real question, do any of you fine people have any
> scripts that will generate a SQL script that will have the correct EXTENT
> &
> NEXT sizes for each table ? ( talk about wanting my cake and eating it !!
> )
>
> Any reply's would be very, very welcome.
>
> Thanks in anticipation
>
> Sean
>
>
>
>
>
>