IDS Forum

Re: Howto rebuild sysmaster

Posted By: Art Kagel
Date: Friday, 14 May 2010, at 12:24 p.m.

In Response To: Re: Howto rebuild sysmaster (LEONARDO SANTAGOSTINI)

Nope. In order to reduce the number of extents a table has, you have to
reorg the table. The easiest and quickest way to do that prior to IDS
11.50.xC4 is with:

ALTER FRAGMENT ON TABLE <some table> INIT IN <dbspace or fragmentation
expression>;

the dbspace or fragment expression you use can be the same one under which
the table currently resides. Before you do that you'll want to modify the
tables NEXT SIZE (and if you have a more recent 11.50 release the EXTENT
SIZE) so that the entire table will fit into a single or small number of
extents.

If you have 11.50xC4 or later you can take advantage of table REPACK and
SHRINK through OAT or the admin API which is more efficient and can be
performed without locking the table from users:

execute function task( ' TABLE REPACK", <table>, <database>, <owner>);
execute function task( ' TABLE SHRINK", <table>, <database>, <owner>);
execute function task( ' TABLE REPACK SHRINK", <table>, <database>,
<owner>);

However, this will have no effect on the sysprocplan lockouts. These are
probably happening because the client applications are not exiting cleanly
from the engine and so the sessions in the engine are still alive and
holding a lock on a procedure that it was in the midst of running or some
DDL it executed has caused the procedure to recompile itself.

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 Fri, May 14, 2010 at 11:54 AM, LEONARDO SANTAGOSTINI <
lsantagostini@gmail.com> wrote:

> Ok, didnt know about OAT.
>
> so i will give it a try.
>
> By the other hand, here the main application is written in powerbuilder,
> and
> when a user terminates the application, or the application stops
> responding,
> it locks the sysprocplan table, as far as i can see.
> So i checked all extents on sysmaster and noticed that so there is at
> least 30
> tables that has more than 100 extents.
> So, i am supposing that making again sysmaster could fix the extents issue
> altering table definition.
>
> Thanks for the reply,
> Knd regards,
> Leonardo
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--001636e0a9b7c7406f0486904d1e

Messages In This Thread

• Howto rebuild sysmaster
  LEONARDO SANTAGOSTINI -- Friday, 14 May 2010, at 11:35 a.m.
  • Re: Howto rebuild sysmaster
    Art Kagel -- Friday, 14 May 2010, at 11:43 a.m.
    • Re: Howto rebuild sysmaster
      LEONARDO SANTAGOSTINI -- Friday, 14 May 2010, at 11:54 a.m.
      • Re: Howto rebuild sysmaster
        Art Kagel -- Friday, 14 May 2010, at 12:24 p.m.
  • Re: Howto rebuild sysmaster
    John Miller iii -- Friday, 14 May 2010, at 2:02 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.