system catalog cleanup

I'm afraid I already know the answer to this, but is there any way to defragment (i.e., "clean up the extent interleaving") of the system catalog tables, short of dropping the database and recreating it? I've got a 100+ GB database that has system catalog tables spread out across the four corners of the globe, and I'd like to clean this up. Any help will be appreciated. Thanks, - Tom Girsch TJG Technical Services

"Thomas J. Girsch" wrote: > > I'm afraid I already know the answer to this, but is there any way to > defragment (i.e., "clean up the extent interleaving") of the system catalog > tables, short of dropping the database and recreating it? I've got a 100+ > GB database that has system catalog tables spread out across the four > corners of the globe, and I'd like to clean this up. Any help will be > appreciated. The only way would be to unload the entire database, drop and recreate the database empty, alter the NEXT SIZE of the system catalog tables, recreate and reload tables. HOWEVER, since the system catalog table data for active tables is cached in the Data Dictionary Cache (sized by DD_HASHSIZE and DD_HASHMAX) this fragmentation has little effect on performance and its biggest effect is to fragment other tables as they grow or are reorged. If you have more active tables than can be cached (onstat -g dic) then increase the size of the Data Dictionary cache. Art S. Kagel

In article <384ec660_1@news2.one.net>, "Thomas J. Girsch" <tgirsch@iname.com> wrote: > I'm afraid I already know the answer to this, but is there any way to > defragment (i.e., "clean up the extent interleaving") of the system > catalog tables, short of dropping the database and recreating it? > I've got a 100+ GB database that has system catalog tables spread out > across the four corners of the globe, and I'd like to clean this up. > Any help will be appreciated. > > Thanks, > > - Tom Girsch > TJG Technical Services Tom, you must be psychic! I was just discussing this issue with my manager today! The simplest way (in terms of commands) to defragment a table is to alter table so that the NEXT size is the current allocation of that table, then pick an index and enter "alter index <idx> to cluster". Itoccured to me that one should be able to do this with most catalogs (besides systables, syscolumns and sysindexes) with no ill effect. The main problem is that it creates a new table with a new tabid. That tabid would certainly be >= 100. I have no idea of the side effects when a system catalog has a tabid >= 100 but I'm fairly certain it's not healthy; that it violates some assumptions assumed by internal able- opening algorithm within the engine. I have yet to check if ALTER FRAGMENT FOR <catalog> INIT IN <its dbspace> also assigns a new tabid. I suspect it does. (I already know, via typos I have made, that the above does not generate an error.) Has anyone tried anything like this on a catalog? -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.