Defragmenting Systables

[testhandle]

Hi,

Some of the system tables is taking more extetns (14 extents). Is ther any way to defragment these system tables.

informix.sysprocplan
informix.sysprocbody
informix.syscolumns


Thanks

 

[tm88gt]

The sysmaster database is not a "physical" database - it's only views into shared memory segments. That being said, NEVER, EVER, EVER do any sort of db admin maintenance on the sysmaster database. You can possibly corrupt your entire instance

 

[testhandle]

Hi,

The informix version is online 5.10. This instance does not have any sysmaster database. All the system tables are present in rootdbs.

Thanks

 

[mjldba]

I agree it's probably not necessary to defrag the system catalog tables in any DB but I generally do it anyway .... why not do it when I'm defragging a DB because it presents me with the only opportunity I'll have to do it?

We're using IDS 9.30.UC3 and I generate a spreadsheet with # of 4K pages for any table with more that 3 extents.

Defragging std. DB tables is another issue but this is the way I handle the system catalog tables.

Multiple the # of pages by 4K giving you total KB's.

Divide this number by 1000 giving you a number that would be the intial extent size IF you could set intial extent size for any system catalog table but you can't.

So you accept that you'll have an initial extent size of 32KB and take the number that WAS going to be your initial extent size and divide it by 2 and round up to the nearest 1000. Use this number for the NEXT extent size and you'll have a manageable # of extents (3 extents) for that table.

Insert a line like this in the SQL script (generated by dbexport) immediately after the "grant connect" statements and before processing any of the std. DB tables:

alter table systables modify next size "some_number";

My method is arbitrary, this is not an exact science. It works with IDS but I can't vouch for other products.

Your mileage may vary so I'd wait for some confirmation from some of the gurus here before implementing this strategy.