actual dbspaces/chunks size

Hi all, I need help on how to determine the actual free space on each chunks/dbspaces. After I have do the data purging. The free size seem to be the same. Regards, tham

Although you are removing/archiving data, the tables are probably still holding their initial extents. Try running this command: oncheck -pt DATABASE:TABLE | head -21 This will output a report in the following format: TBLspace Report for cl_bigsys_amarta:dba.contract Physical Address b09718 Creation date 12/01/2000 12:36:16 TBLspace Flags 802 Row Locking TBLspace use 4 bit bit-maps Maximum row size 248 Number of special columns 0 Number of keys 12 Number of extents 2 Current serial value 1348771 First extent size 230000 Next extent size 25000 Number of pages allocated 255000 >> Pages allocated (510 MB) Number of pages used 232311 >> Total Used (464 MB) Number of data pages 158122 >> Data Pages (Rest are indexes) Number of rows 1264971 Partition partnum 2129893 Partition lockid 2129893 From this report you can work out that 46MB is effectively being wasted, although if the table grows, I will not allocate another extent until that 46MB has been used. To reclaim the wasted space, you must run this report on each table (if their are many tables, you can run SQL over systabinfo in sysmaster to output to a file!!), work out the size of your first extent, taking in to account if the table grows etc etc..., then you need to unload the data, drop the table and recreate with that extent, and consider the next extent size as well. If we are running short of space in one of our development instances, I sometimes run the following SQL to determine where I can reclaim some space: unload to wasted_space.unl select b.tabname, b.dbsname, a.ti_npused/a.ti_nptotal as PercentUsed , (a.ti_nptotal - a.ti_npused)*2 as Wasted,a.ti_nptotal*2 as Allocated, a.ti_npused*2 as Used from systabinfo a, systabnames b where a.ti_partnum = b.partnum and b.owner <> "informix" --and a.ti_npused/a.ti_nptotal < 0.5 --Example 1 and (a.ti_nptotal - a.ti_npused)*2 > 500 --Example 2 --and a.ti_nptotal*2 > 100 --Example 3 The last part is where you can set thresholds, i.e. Example 1, where more than half of the total extents are being wasted, Example 2, where total wasted is more than 500KB, Example 3, where total table size is larger than 100KB. By using some of these, or all or none, you can get a report of wasted space and then choose which tables to recreate. Hope this helps. Jason Harrrington. > >Hi all, > >I need help on how to determine the actual free space on each >chunks/dbspaces. After I have do the data purging. The free size seem >to be the same. > >Regards, > >tham > -- Jason Harrington

In the DBA class I took, they said that "alter index to cluster" rewrites all records pysically (sorted) and free the extra spaces - extents. Otherwise, when you delete records from tables it does not free the empty extents. Hope I helped... Jason Harrington <jah466@century.demon.co.uk> wrote in message news:$XtUhDAffyW6IAP5@century.demon.co.uk... > Although you are removing/archiving data, the tables are probably still > holding their initial extents. > > Try running this command: > > oncheck -pt DATABASE:TABLE | head -21> > This will output a report in the following format: > > > TBLspace Report for cl_bigsys_amarta:dba.contract > > Physical Address b09718 > Creation date 12/01/2000 12:36:16 > TBLspace Flags 802 Row Locking > TBLspace use 4 bit bit-maps > Maximum row size 248 > Number of special columns 0 > Number of keys 12 > Number of extents 2 > Current serial value 1348771 > First extent size 230000 > Next extent size 25000 > Number of pages allocated 255000 >> Pages allocated (510 MB) > Number of pages used 232311 >> Total Used (464 MB) > Number of data pages 158122 >> Data Pages (Rest are indexes) > Number of rows 1264971 > Partition partnum 2129893 > Partition lockid 2129893 > > From this report you can work out that 46MB is effectively being wasted, > although if the table grows, I will not allocate another extent until > that 46MB has been used. > > To reclaim the wasted space, you must run this report on each table (if > their are many tables, you can run SQL over systabinfo in sysmaster to > output to a file!!), work out the size of your first extent, taking in > to account if the table grows etc etc..., then you need to unload the > data, drop the table and recreate with that extent, and consider the > next extent size as well. > > If we are running short of space in one of our development instances, I > sometimes run the following SQL to determine where I can reclaim some > space: > > unload to wasted_space.unl > select b.tabname, b.dbsname, a.ti_npused/a.ti_nptotal > as PercentUsed , (a.ti_nptotal - a.ti_npused)*2 as Wasted,> a.ti_nptotal*2 as Allocated, a.ti_npused*2 as Used > from systabinfo a, systabnames b > where a.ti_partnum = b.partnum > and b.owner <> "informix" > --and a.ti_npused/a.ti_nptotal < 0.5 --Example 1 > and (a.ti_nptotal - a.ti_npused)*2 > 500 --Example 2 > --and a.ti_nptotal*2 > 100 --Example 3 > > The last part is where you can set thresholds, i.e. Example 1, where > more than half of the total extents are being wasted, Example 2, where > total wasted is more than 500KB, Example 3, where total table size is > larger than 100KB. > > By using some of these, or all or none, you can get a report of wasted > space and then choose which tables to recreate. > > Hope this helps. > > Jason Harrrington. > > > > >Hi all, > > > >I need help on how to determine the actual free space on each > >chunks/dbspaces. After I have do the data purging. The free size seem > >to be the same. > > > >Regards, > > > >tham > > > > -- > Jason Harrington

Jason and Dafna have offered good solutions for compressing a table and recovering deleted row space for use by other tables. Here is another way which tends to be the fastest: ALTER FRAGMENT ON TABLE tablename INIT IN dbspacename_or_fragment_expression; This will, oddly enough, work for both fragmented and non-fragmented tables and tends to be faster than CLUSTERing an index since it does not have to sort the data. BTW re Dafna's solution again, if you already have a CLUSTERED index on the table you have to ALTER INDEX indexname TO NOT CLUSTERED; Art S. Kagel Tham Huei Hwan wrote: > > Hi all, > > I need help on how to determine the actual free space on each > chunks/dbspaces. After I have do the data purging. The free size seem > to be the same. > > Regards, > > tham