IDS Forum

Re: Truncated table still occupied many pages

Posted By: Art Kagel
Date: Friday, 21 March 2014, at 7:29 a.m.

In Response To: Truncated table still occupied many pages (JACOB SALOMON)

What does a search of sysextents show? Are there really any extents
assigned to the table or the index?

select dbsname, tabname, sum(size), count(*)
from sysextents
where dbsname = 'somedatabase'

and tabname in ('sometable', 'someindexname')
group by 1, 2
order by 1, 2;

Art

Art S. Kagel, Principal Consultant
ASK Database Management

Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions
and do not reflect on 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 Thu, Mar 20, 2014 at 11:41 PM, JACOB SALOMON <jakesalomon@yahoo.com>wrote:

> Greeting, Folks.
>
> Today my client asked me to truncate a couple of tables on a heap of
> servers.
> In one case it was quite bad - 59 million rows. No problem, i used the
> command:
>
> truncate table <whatever> drop storage;
>
> Using my fragments.sh script (or just examining columns (ti_npused,
> ti_nptotl)
> in sysmaster:systabinfo) I can see that npused has indeed gone down to 1
> or 2
> on the data and index partitions. But the nptotl remains stubbornly the
> same,
> at over 1 million pages. I was *sure* that the truncate command would
> release
> it back to the free pages list!
>
> On the development server, I tried alter index to cluster on one of the now
> empty tables. The nptotal for the table stayed where it was while the
> nptotl
> on the index partition doubled! Now that looks just plain kooky!
>
> BTW, before I truncated the table I did:
> alter table <whatever> extent size 10000 next size 5000;
>
> Obviously, this made no difference. So what can I do to free up all that
> space?
>
> As I type this it occurs to me to try ALTER FRAGMENT INIT IN another
> dbspace,
> then do it again to get it back in its original place. I'm hoping someone
> here
> will come up with something more clever than that.
>
> Any takers here? :-)
>
> Thanks.
>
> -- Jacob S.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001a11c3f020038f3404f51c33bd

Messages In This Thread

• Truncated table still occupied many pages
  JACOB SALOMON -- Thursday, 20 March 2014, at 11:41 p.m.
  • Re: Truncated table still occupied many pages
    DOUG LAWRY -- Friday, 21 March 2014, at 6:09 a.m.
    • Re: Truncated table still occupied many pages
      DOUG LAWRY -- Friday, 21 March 2014, at 8:31 a.m.
    • Re: Truncated table still occupied many pages
      JACOB SALOMON -- Friday, 21 March 2014, at 11:31 a.m.
      • Re: Truncated table still occupied many pages
        John Miller iii -- Friday, 21 March 2014, at 11:53 a.m.
  • Re: Truncated table still occupied many pages
    Art Kagel -- Friday, 21 March 2014, at 7:29 a.m.
    • Re: Truncated table still occupied many pages
      JACOB SALOMON -- Friday, 21 March 2014, at 10:21 a.m.
      • Re: Truncated table still occupied many pages
        Art Kagel -- Friday, 21 March 2014, at 10:34 a.m.
      • Re: Truncated table still occupied many pages
        DOUG LAWRY -- Friday, 21 March 2014, at 12:10 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.