Database Catalog
The poster asked whether the "sys" system catalog tables of a database can be moved to another dbspace without a dbexport/dbimport cycle, believing catalog growth was causing heavy extent fragmentation in his ERP tables. Jonathan Leffler and Art Kagel replied that the catalog's location is fixed at CREATE DATABASE time and can only be changed by recreating the database — either export/drop/recreate/import, or by building a new database, copying tables, dropping the original and renaming (slightly faster). Madison Pruet added that catalog fragmentation matters little since catalogs are read mainly into the dictionary cache, and suggested instead rebuilding the heavily fragmented user tables into their own dbspace.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Hi, everybody. Does anybody ever tried to move all "sys" database catalog
tables without dbexport/dbimport process?
Best regards,
André Luiz Rufino
What do you mean by 'move'? Place in a different dbspace?
The system catalog is created in the dbspace you specify when you execute
"CREATE DATABASE whatever IN dbspace", defaulting to the rootdbs.
Offhand, I don't think there's a way to change the dbspace where the
database is located, but I may easily have missed something that allows it
to happen.
On Tue, Apr 16, 2013 at 7:23 AM, André Luiz Rufino <andre_rufino@msn.com>wrote:
> Hi, everybody. Does anybody ever tried to move all "sys" database catalog
> tables without dbexport/dbimport process?
> Best regards,
> André Luiz Rufino
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."
--001a11c1b1424bd2f404da7b2f9a
You cannot relocate the database's catalog tables except by recreating the
database in a different dbspace. You can do this either by export, drop,
recreate, import or by creating an empty database with a different name,
creating the tables in the new database, copy the data from the original
database to the new one, drop the original database, rename the new
database. The latter can be faster than export/import because you save one
set of disk write/readback operations.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
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, Apr 16, 2013 at 10:23 AM, André Luiz Rufino
<andre_rufino@msn.com>wrote:
> Hi, everybody. Does anybody ever tried to move all "sys" database catalog
> tables without dbexport/dbimport process?
> Best regards,
> André Luiz Rufino
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--089e0117720589a4e204da7b3bb3
Ok. When I said database, I mean all the "sys" catalog tables about that
database. I think the catalog tables growth is making my database extremelly
fragmented (very large number of extents on my ERP transactions tables. So, I
thought that, if I could change de dbspace of all my "sys" tables, it could be
easier and faster than a dbeport/dbimport process...
> To: ids@iiug.org
> From: jonathan.leffler@gmail.com
> Subject: Re: Database Catalog [30060]
> Date: Tue, 16 Apr 2013 10:28:48 -0400
>
> What do you mean by 'move'? Place in a different dbspace?
>
> The system catalog is created in the dbspace you specify when you execute
> "CREATE DATABASE whatever IN dbspace", defaulting to the rootdbs.
>
> Offhand, I don't think there's a way to change the dbspace where the
> database is located, but I may easily have missed something that allows it
> to happen.
>
> On Tue, Apr 16, 2013 at 7:23 AM, André Luiz Rufino
> <andre_rufino@msn.com>wrote:
>
> > Hi, everybody. Does anybody ever tried to move all "sys" database catalog
> > tables without dbexport/dbimport process?
> > Best regards,
> > André Luiz Rufino
> >
> >
> >
> >
>
*******************************************************************************
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>
> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease to be
> amused."
>
> --001a11c1b1424bd2f404da7b2f9a
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
The catalog is the first thing put into dbspace when the database is
created. It might be a bit fragmented as new tables/columns/etc are
created, but the fragmentation of the catalogue is not significant beca=
use
we only read those tables when we open the table with the first prepare=
d
statement into in-memory structures (the dictionary cache), and we only=
reference the dictionary cache during normal query processing.
If you have other tables within the database which are highly fragmente=
d
and which tend to be used with sequential or light scans, then you migh=
t
consider placing those tables in some isolated dbspace by ''create
table .... in some_isolated_dbspace. You could do this by creating a=
new
table, copying the old table into the new table, dropping the old table=
,
and then renaming the new table.
M.P.
From: "Andr=E9 Luiz Rufino" <andre_rufino@msn.com>
To: ids@iiug.org,
Date: 04/16/2013 11:19 AM
Subject: RE: Database Catalog [30064]
Sent by: ids-bounces@iiug.org
Ok. When I said database, I mean all the "sys" catalog tables about tha=
t
database. I think the catalog tables growth is making my database
extremelly
fragmented (very large number of extents on my ERP transactions tables.=
So,
I
thought that, if I could change de dbspace of all my "sys" tables, it c=
ould
be
easier and faster than a dbeport/dbimport process...
> To: ids@iiug.org
> From: jonathan.leffler@gmail.com
> Subject: Re: Database Catalog [30060]
> Date: Tue, 16 Apr 2013 10:28:48 -0400
>
> What do you mean by 'move'? Place in a different dbspace?
>
> The system catalog is created in the dbspace you specify when you exe=
cute
> "CREATE DATABASE whatever IN dbspace", defaulting to the rootdbs.
>
> Offhand, I don't think there's a way to change the dbspace where the
> database is located, but I may easily have missed something that allo=
ws
it
> to happen.
>
> On Tue, Apr 16, 2013 at 7:23 AM, Andr=E9 Luiz Rufino
> <andre_rufino@msn.com>wrote:
>
> > Hi, everybody. Does anybody ever tried to move all "sys" database
catalog
> > tables without dbexport/dbimport process?
> > Best regards,
> > Andr=E9 Luiz Rufino
> >
> >
> >
> >
>
***********************************************************************=
********
> > Forum Note: Use "Reply" to post a response in the discussion forum.=
> >
> >
>
> --
> Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h>=
> Guardian of DBD::Informix - v2013.0118 - http://dbi.perl.org
> "Blessed are we who can laugh at ourselves, for we shall never cease =
to
be
> amused."
>
> --001a11c1b1424bd2f404da7b2f9a
>
>
>
***********************************************************************=
********
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=