How can I shrink the table's space
Shan Sean (IDS 11.5 FC6) deleted about two thirds of a large table's rows and wanted the space released without recreating the table. Netsky Liao and Art Kagel recommended the sysadmin API task, TABLE REPACK and TABLE SHRINK (11.50.xC4 or later), via TASK() or ADMIN(); Art noted deleted space is reused within the table anyway. Shan found it in the SQL Admin API docs. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi, everybody， I have a large table, and I delete about 2/3 rows lately, but the space not release, how can I shrink the not use spaces but recreate the table ?
Note： My IDS is 11.5 FC6
>Hi, everybody， I have a large table, and I delete about 2/3 rows
lately, but the space not release, how can I shrink the not use spaces but
recreate the table ?
1.do repack in database sysadmin
execute function task("table repack","tabname","dbname");2.do shrink in database sysadmin
execute function task("table shrink","tabname","dbname");
then use oncheck -pt dbname:tabname to validate.
Version and platform information should ALWAYS be included when you post a
question. In this case it affects the answer. The simplest way to reorg a
table and release the space that was used by deleted rows for reuse by other
tables is to use the ADMIN API's REPACK function with the SHRINK option.
However, this option is only available if you have Informix 11.50xC4 or
later. There are other ways if you have an earlier release. Post your
version and platform info if that's the case and we will help.
Note that Informix automatically reused deleted row space within the table
for new rows, you do not have to reorg the table to be able to reuse the
free space inside the table for inserts. However, if you need to free up
that space for other tables/indexes to use, do this:
EXECUTE FUCTION ADMIN( 'TABLE REPACK SHRINK')'
-or-
EXECUTE FUNCTION TASK( 'TABLE REPACK SHRINK' );
The only difference is that the ADMIN() function returns a numberic
success/failure code and the TASK() function returns a text message.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.
On Wed, Sep 8, 2010 at 4:18 AM, SHAN SEAN <shanshl@qq.com> wrote:
> Hi, everybody， I have a large table, and I delete about 2/3 rows
> lately, but the space not release, how can I shrink the not use spaces but
> recreate the table ?
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--0016e64351d89d76d3048fbfbf46
Thank you for all response, I see finally, use the SQL API. I knew the 11.5 have function for shrink table, so I find the sql syntax in the IBM reference book, but nothing in it. finally it in the SQL API, I learn it from all of you, and I'll learn the SQL API in the futhure, Thanks! ^_^