Problem: Delete from Tab doesn't release DBspace

Hello, I have a Database without transaction (i.e logging -none) and one Table in it. I loaded this table with a lot of data (~100MB) using command 'dbaccess - t.sql' where 'script t.sql' had following entries: load from "data.txt"delimiter " " insert into t; After loading table I created some indeces and started Space Explorer. It showed me 100MB dbspace with 1.5MB free (as expected). I droped indeces, deleted table contents and started Space Explorer again. Result was the same - 100MB dbspace with 1.5MB free. I tried to backup DB and restore, nothing helps. Only dropping the table makes dbspace free. ( I can still load other data to table t2, i.e more then 1.5 DM, but when for example I perform query 'select * from t', it runs ~20 sec. as it really has a lot of data to fetch) Please help me to fix this problem. I run NT4+sp3 & Informix Online DS TM v. 7.22.TC1 Joel

Joel Golovaty wrote in message <787i60$kp9$1@pollux.ip-plus.net>... >Hello, >I have a Database without transaction (i.e logging -none) and one Table in >it. >I loaded this table with a lot of data (~100MB) using command 'dbaccess - >t.sql' >where 'script t.sql' had following entries: > >load from "data.txt">delimiter " " >insert into t;> >After loading table I created some indeces and started Space Explorer. It >showed me 100MB dbspace with 1.5MB free (as expected). > >I droped indeces, deleted table contents and started Space Explorer again. >Result was the same - 100MB dbspace with 1.5MB free. >I tried to backup DB and restore, nothing helps. Only dropping the table >makes dbspace free. ( I can still load other data to table t2, i.e more >then 1.5 DM, but when for example I perform query 'select * from t', it >runs ~20 sec. as it really has a lot of data to fetch) > >Please help me to fix this problem. > >I run NT4+sp3 & Informix Online DS TM v. 7.22.TC1 > >Joel > > (I think) this is cos when you add rows to a table, the DB allocates extents, but when data is deleted from the table, these extents are not deallocated, and thus your data could still be lurking in one of the extents. What may help is something like: alter fragment on <table> init in <other_dbspace>; Which will effectively rebuild the table, in the other dbspace; i guess you could do the same again to move it back to the original... Not too sure that this is the best way to do it though. You can find the number of extents by using oncheck -pe The following script is not one i wrote, but I dont know the name of the original author to credit. (M$ Excahgen will proably break it up into lots of wierd lines anyway) James ---CUT--- #!/bin/sh #The following script displays the tables with more than one extent, #listing the most fragmented tables first. The number of extents and #pages is listed for each table.: echo | awk '{printf "%-30s\\t%5s\\t%12s\\n", "TABLE","EXTS.","PAGES";}' oncheck -pe | sort | grep '^ *[a-z]'|awk ' BEGIN {matchit = ""; count = 0;size = 0;} {if (matchit != $1) { if (count > 1) printf "%-30s\\t%5d\\t%12d\\n",mat chit, count, size; matchit = $1; count = 1; size = $3; } else { count++; size += $3; } } END {if (count > 1) printf "%-30s\\t%5d\\t%12d\\n", matchit, co unt,size}'| sort -t ' ' -rn -k2

Joel Golovaty wrote: > > Hello, > I have a Database without transaction (i.e logging -none) and one Table in > it. > I loaded this table with a lot of data (~100MB) using command 'dbaccess - > t.sql' > where 'script t.sql' had following entries: > > load from "data.txt"> delimiter " " > insert into t;> > After loading table I created some indeces and started Space Explorer. It > showed me 100MB dbspace with 1.5MB free (as expected). > > I droped indeces, deleted table contents and started Space Explorer again. > Result was the same - 100MB dbspace with 1.5MB free. > I tried to backup DB and restore, nothing helps. Only dropping the table > makes dbspace free. ( I can still load other data to table t2, i.e more > then 1.5 DM, but when for example I perform query 'select * from t', it > runs ~20 sec. as it really has a lot of data to fetch) > > Please help me to fix this problem. Informix allocates space to a table in chunks known as extents. An extent is never released by a table so the table never shrinks. There are several ways to compress the space in a table and recover unused pages. You've already hit on one: export the data and drop and recreate the table and reload. The best and fastest is: ALTER FRAGMENT ON TABLE mytable INIT IN somedbspace; Art S. Kagel

In article <787i60$kp9$1@pollux.ip-plus.net>, "Joel Golovaty" <golovaty@compudata.ch> wrote: > Hello, > I have a Database without transaction (i.e logging -none) and one Table in > it. > I loaded this table with a lot of data (~100MB) using command 'dbaccess - > t.sql' > where 'script t.sql' had following entries: > > load from "data.txt"> delimiter " " > insert into t;> > After loading table I created some indeces and started Space Explorer. It > showed me 100MB dbspace with 1.5MB free (as expected). > > I droped indeces, deleted table contents and started Space Explorer again. > Result was the same - 100MB dbspace with 1.5MB free. > I tried to backup DB and restore, nothing helps. Only dropping the table > makes dbspace free. ( I can still load other data to table t2, i.e more > then 1.5 DM, but when for example I perform query 'select * from t', it > runs ~20 sec. as it really has a lot of data to fetch) > > Please help me to fix this problem. > > I run NT4+sp3 & Informix Online DS TM v. 7.22.TC1 > > Joel > > This is normal. When you delete rows from a table, the data is removed but the table extent(s) remain. As you insert new data into the table, the extent(s) will fill back up again. If you really need the space back after you empty the table, the quickest way would be to just drop the table and recreate it (paying attention to any referential constraints, of course). If this will not work for you, the next best bet is to: 1) Delete the data. 2) Select one index on that table. 3) Run "ALTER INDEX <index name> TO CLUSTER;" on that index. Clustering the index will actually rebuild your (now empty) table, and should reduce the amount of disk space used to the size of your table's initial extent size. Bob Davis --------- -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own