Re: How to Change sysdistrib tables extent size?

Hi Kagel, Its IDS 9.40FC4.Here 2 scenarios, 1) Need to create big extents during creation of New database itself or 2) after it reaches 150 extents or even more...(after some time) is it possible? rgds schillache

Is what possible? In 9.xx you cannot change the initial extent size of the TABLESPACE TABLESPACE nor of the system catalog tables. You can, as informix, change the NEXT SIZE for the catalog tables before you start to create tables in the database to minimize the fragmentation of the catalog. However, note that the important catalog records are cached in memory in IDS so if you have the data dictionary cache and data distributions cache properly sized, fragmentation of the catalog tables should not affect production much if at all. Are you asking about the extent sizes of the tables? You cannot set a default extent size in 9.xx like you can in later 11.50 releases, but, in the SQL DDL that creates the tables you can specify the sizes for the initial EXTENT SIZE and NEXT EXTENT size to something larger than the default (8 pages each) when you create the tables. Your second question I can't even guess at. Are you asking can you adjust the extent sizes for new extents at some point in the future? Yes "ALTER TABLE mytable NEXT SIZE 100;" will do that. Note that after 16 extents have been allocated at a particular size IDS automatically doubles the NEXT SIZE of the table for the next extent to be allocated. Are you rather asking can you reorganize an existing table with many extents down the road? Yes. Several ways: - Unload, drop, recreate, reload - ALTER and INDEX "TO CLUSTER" - ALTER FRAGMENT ON TABLE mytable INIT IN <dbspacename or fragmentation expression>; The last tends to be the fastest methods but except for the first method, you need enough disk space to hold two copies of the table until the reorg is completed. Also unless to ALTER the table to RAW before the reorg and back to STANDARD after, you will also need logical log space to hold all of the modifications to the table that the reorg produces. Does this answer your questions? If not, be clearer and a bit more verbose. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) IIUG Board of Directors (art@iiug.org) See you at the 2010 IIUG Informix Conference April 25-28, 2010 Overland Park (Kansas City), KS www.iiug.org/conf 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 Tue, Feb 16, 2010 at 10:29 PM, SCHIL ACHE <penfriend5@yahoo.co.uk> wrote: > Hi Kagel, > > Its IDS 9.40FC4.Here 2 scenarios, > > 1) Need to create big extents during creation of New database itself or > 2) after it reaches 150 extents or even more...(after some time) > > is it possible? > > rgds > schillache > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --000325555bf2ce37ba047fc4071f

You need to raise this as feature request, I know its been tested internally running in single user mode. There are backdoor ways of changing the systables when you run out of extents but they are 100% not supported :-) Cheers Paul > Is what possible? In 9.xx you cannot change the initial extent size of the > TABLESPACE TABLESPACE nor of the system catalog tables. You can, as > informix, change the NEXT SIZE for the catalog tables before you start to > create tables in the database to minimize the fragmentation of the > catalog. > However, note that the important catalog records are cached in memory in > IDS > so if you have the data dictionary cache and data distributions cache > properly sized, fragmentation of the catalog tables should not affect > production much if at all. > > Are you asking about the extent sizes of the tables? You cannot set a > default extent size in 9.xx like you can in later 11.50 releases, but, in > the SQL DDL that creates the tables you can specify the sizes for the > initial EXTENT SIZE and NEXT EXTENT size to something larger than the > default (8 pages each) when you create the tables. > > Your second question I can't even guess at. Are you asking can you adjust > the extent sizes for new extents at some point in the future? Yes "ALTER > TABLE mytable NEXT SIZE 100;" will do that. Note that after 16 extents > have > been allocated at a particular size IDS automatically doubles the NEXT > SIZE > of the table for the next extent to be allocated. Are you rather asking > can you reorganize an existing table with many extents down the road? Yes. > Several ways: > > - Unload, drop, recreate, reload > > - ALTER and INDEX "TO CLUSTER" > > - ALTER FRAGMENT ON TABLE mytable INIT IN <dbspacename or fragmentation > > expression>; > > The last tends to be the fastest methods but except for the first method, > you need enough disk space to hold two copies of the table until the reorg > is completed. Also unless to ALTER the table to RAW before the reorg and > back to STANDARD after, you will also need logical log space to hold all > of > the modifications to the table that the reorg produces. > > Does this answer your questions? If not, be clearer and a bit more > verbose. > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > IIUG Board of Directors (art@iiug.org) > > See you at the 2010 IIUG Informix Conference > April 25-28, 2010 > Overland Park (Kansas City), KS > www.iiug.org/conf > > 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 Tue, Feb 16, 2010 at 10:29 PM, SCHIL ACHE <penfriend5@yahoo.co.uk> > wrote: > >> Hi Kagel, >> >> Its IDS 9.40FC4.Here 2 scenarios, >> >> 1) Need to create big extents during creation of New database itself or >> 2) after it reaches 150 extents or even more...(after some time) >> >> is it possible? >> >> rgds >> schillache >> >> >> >> > ******************************************************************************* >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > > --000325555bf2ce37ba047fc4071f > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com www.advancedatatools.com Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid.

Hi Kagel, 1) Do the below possible for sysdistrib tables? " Unload, drop, recreate, reload - ALTER and INDEX "TO CLUSTER" - ALTER FRAGMENT ON TABLE mytable INIT IN <dbspacename or fragmentation expression>; " 2) During initial creation where do i need to modify the extent size & next size for sysdistrib table (like sysmaster.sql or NEXT SIZE in onconfig)? rgds schillache

No. Often when i'm answering posts while doing 3 other things I don't rea = the subject line. =20 First, sysdistrib is cached so as long as the distribution cache is sized c= orrectly many extents should not affect performance. =20 Second, you can set the next size for sysdistrib using alter to prevent fur= ther fragmentation. Third, you can only reorg catalog tables by dropping the database and relo= ading it after resizing the next sizes of the catalog tables. You can do t= his be adding the alters to the schema file that dbexport creates before db= importing the database. =20 Art=20 -----Original Message----- From: SCHIL ACHE <penfriend5@yahoo.co.uk> Sent: Wednesday, February 17, 2010 10:36 PM To: ids@iiug.org Subject: Re: How to Change sysdistrib tables extent size? [19072] Hi Kagel,=20 1) Do the below possible for sysdistrib tables?=20 " Unload, drop, recreate, reload=20 - ALTER and INDEX "TO CLUSTER"=20 - ALTER FRAGMENT ON TABLE mytable INIT IN <dbspacename or fragmentation=20 expression>; "=20 2) During initial creation where do i need to modify the extent size & next= =20 size for sysdistrib table (like sysmaster.sql or NEXT SIZE in onconfig)?=20 rgds=20 schillache=20 ***************************************************************************= ****=20 Forum Note: Use "Reply" to post a response in the discussion forum.=20