Howto rebuild sysmaster

Hello all, again. I was taking control for a new database, and i was looking at the sysmaster database some tables that has more than 100 extents, so, im facing lock problem under sysprocplan, by example. So, 1) is there a way to do extent reorganization on sysmaster's tables ? 2) it is possible to rebuild sysmaster without loosing the catalog data ? Maybe some doc, link, redbook ? Thanks for be patient with me. Regards, Leonardo

You can rebuild sysmaster any time you want, however, that will not do what you seem to think it will do. Sysmaster tables are not "REAL" tables they are windows into memory and disk based data structures that the engine uses to manage and monitor the data. The sysextents is a good example. It is NOT a table containing your extents. It is a window into the extent lists that are kept on each table's partition header page. There is no locking when viewing these tables through the SMI (sysmaster) interface. What problems are you seeing with locks on sysprocplan, though? Is this in sysmaster? That would be unusual, sysprocplan locks are normally encountered during the recompilation of stored procedures performed as part of running UPDATE STATISTICS. If that's what is happening, you can try disabling the Automated Update Statistics tasks in the sysadmin database. You can do that pretty easily in OAT. 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:35 AM, LEONARDO SANTAGOSTINI < lsantagostini@gmail.com> wrote: > Hello all, again. > > I was taking control for a new database, and i was looking at the sysmaster > database some tables that has more than 100 extents, so, im facing lock > problem under sysprocplan, by example. > > So, > > 1) is there a way to do extent reorganization on sysmaster's tables ? > 2) it is possible to rebuild sysmaster without loosing the catalog data ? > > Maybe some doc, link, redbook ? > > Thanks for be patient with me. > Regards, > Leonardo > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636e0a80c52fa3204868fbd21

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

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 fragmentationexpression>; 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

One large locking issue with sysprocplan had to do with re-compilation = of stored procedures. The automatic recompilation can be done for a variety of reason and many users are not aware that it even happens. The recompilation was done under the users current transaction which meant that until the users session committed/rolledback their transaction the recompilation held locks on sysprocplan. Other users would not be allowed to run thes A new improvement done around the 11.50 time frame was to recompile the store procedure under its own transaction and hence the locks are held for a very short time and the users actions have no bearing on how= long the locks are held during the automatic recompilation of the stored procedure. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) = From: "LEONARDO SANTAGOSTINI" <lsantagostini@gmail.com> = = To: ids@iiug.org = = Date: 05/14/2010 08:36 AM = = Subject: Howto rebuild sysmaster [20160] = = Sent by: ids-bounces@iiug.org = = Hello all, again. I was taking control for a new database, and i was looking at the sysma= ster database some tables that has more than 100 extents, so, im facing lock= problem under sysprocplan, by example. So, 1) is there a way to do extent reorganization on sysmaster's tables ? 2) it is possible to rebuild sysmaster without loosing the catalog data= ? Maybe some doc, link, redbook ? Thanks for be patient with me. Regards, Leonardo ***********************************************************************= ******** Forum Note: Use "Reply" to post a response in the discussion forum. =