Actual free space by Chunk/dbspaces

--------------8746F29EEE2D58099CED7F45 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Hi all, If anyone out there can let me know the sql syntax that can be use to find out the actual free space by chunk / dbspaces after the data purging (instead of load/unload and alter index)? After the data purging, this doesn't reflected on the "onstat -d" output, the output still shown me "0" free pages. Thanks and Regards, Tham --------------8746F29EEE2D58099CED7F45 Content-Type: text/html; charset=us-ascii Content-Transfer-Encoding: 7bit <!doctype html public "-//w3c//dtd html 4.0 transitional//en"> <html> Hi all, <p>If anyone out there can let me know the sql syntax that can be use to find out the actual free space by <b>chunk / dbspaces </b>after the data purging (instead of load/unload and alter index)? After the data purging, this doesn't reflected on the "onstat -d" output, the output still shown me "0" free pages. <br>&nbsp; <p>Thanks and Regards, <br>Tham</html> --------------8746F29EEE2D58099CED7F45--

Tham Huei Hwan wrote in message <95vjra$s29$1@news.xmission.com>... > >If anyone out there can let me know the sql syntax that can be use to >find out the actual free space by chunk / dbspaces after the data >purging (instead of load/unload and alter index)? After the data >purging, this doesn't reflected on the "onstat -d" output, the output >still shown me "0" free pages. > Informix does not give back free space when you delete rows from a table. You MUST perform one of the unload/load or CLUSTER INDEX or ALTER FRAGMENT operations to cause the engine to rewrite the space. There is no escape.

Tham Huei Hwan wrote in message <95vjra$s29$1@news.xmission.com>... > >If anyone out there can let me know the sql syntax that can be use to >find out the actual free space by chunk / dbspaces after the data >purging (instead of load/unload and alter index)? After the data >purging, this doesn't reflected on the "onstat -d" output, the output >still shown me "0" free pages. This suggestion is from Jason Harrison a few weeks ago: 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. NOTE: if you created all your tables using the informix account, you will see nothing! I would replace the line and b.owner <> "informix" with and b.tabname not matches "TBL*" you can also add filters like: and b.dbsname = "mydatabase" and b.tabname in ("tab1", "tab2", "tab3") and so on.

Please do not post HTML/Mime, this is a text only newsgroup. Tham Huei Hwan wrote: > > Hi all, > > If anyone out there can let me know the sql syntax that can be use to > find out the actual free space by chunk / dbspaces after the data > purging (instead of load/unload and alter index)? After the data > purging, this doesn't reflected on the "onstat -d" output, the output > still shown me "0" free pages. Informix does not return space freed within a table by deletes to the pool of free space available to any table. The pages allocated to each table remain so but are reusable by that table. Oncheck -pt shows the free space within the table, as does a query against sysmaster:sysptnhdr. To return pages no longer needed by a table to the free pool you have to reorganize. If indeed you have zero free space the best solution is to dbexport (or just unload), drop the table, recreate it with a smaller first extent size (and maybe next size also), and reload it. Art S. Kagel