Moving Catalog

Posted in 2010

Summary

Andre Luiz Rufino asked whether the catalog tables could be moved to another dbspace in IDS 11.5 FC5. John Miller III said it can only be set at create time. Khaled Bentebal gave a procedure creating a new database, copying tables, dropping the old one and renaming. Art Kagel said to unload, recreate and reload. Answered.

Auto-generated by Claude from the posts below — may be imperfect; read the full thread.

Topics: Storage & Space Management, Triggers, Constraints & Referential Integrity

André Luiz Rufino — Mon, 01 Feb 2010 16:13:23 — source: IIUG Forums & Mailing Lists

Hi Folks, Is there a way to move the catalog (systables, systriggers, etc) to another dbspaces? I want to change the database location. Is that possible in the 11.5 FC5 version? Thanks, André Luiz - MKI _________________________________________________________________ Quer compartilhar fotos com seus amigos? Conheça agora o Windows Live Fotos. http://www.eutenhomaisnowindowslive.com.br/?utm_source=MSN_Hotmail&utm_medium=Ta gline&utm_campaign=InfuseSocial

↪ replying to André Luiz Rufino

John Miller iii — Mon, 01 Feb 2010 16:36:30 — source: IIUG Forums & Mailing Lists

It maybe done only at create time and there is no move command. John F. Miller III STSM, Support Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 02/01/2010 08:13:24 AM: > Hi Folks, > > Is there a way to move the catalog (systables, systriggers, etc) to another > dbspaces? I want to change the database location. Is that possible > in the 11.5 > FC5 version? > > Thanks, > > Andr=E9 Luiz - MKI > > _________________________________________________________________ > Quer compartilhar fotos com seus amigos? Conhe=E7a agora o Windows Li= ve Fotos. > > http://www.eutenhomaisnowindowslive.com.br/? > utm_source=3DMSN_Hotmail&utm_medium=3DTagline&utm_campaign=3DInfuseSo= cial > > > ***********************************************************************= ******** > Forum Note: Use "Reply" to post a response in the discussion forum.= >=

↪ replying to André Luiz Rufino

Khaled Bentebal — Mon, 01 Feb 2010 17:55:17 — source: IIUG Forums & Mailing Lists

Hi, You cannot move system tables. What you will need to do is the following: 1. Create another database with a different name in another dbspace; make the original database a non logged database 2. Create the tables of the database in the existing dbspaces (where the current database resides) with the correct extent sizes in order to avoid fragmentation; this is if you have enough disk space. Otherwise, you will have to create your tables in other dbspaces 3. Copy the tables using for example : INSERT INTO <destination_table> SELECT * FROM <source table>4. Drop the original database 5. Rename the newly created database 6. Make the new database a logged database if this is what you had initially. This way, the system catalog tables will be in the dbspace desired. I hope that this helps. Khaled Bentebal Email: khaled.bentebal@consult-ix.fr ----- Original Message ----- From: "André Luiz Rufino" <andre_rufino@msn.com> To: <ids@iiug.org> Sent: Monday, February 01, 2010 5:13 PM Subject: Moving Catalog [18853] > Hi Folks, > > Is there a way to move the catalog (systables, systriggers, etc) to > another > dbspaces? I want to change the database location. Is that possible in the > 11.5 > FC5 version? > > Thanks, > > André Luiz - MKI > > _________________________________________________________________ > Quer compartilhar fotos com seus amigos? Conheça agora o Windows Live > Fotos. > > http://www.eutenhomaisnowindowslive.com.br/?utm_source=MSN_Hotmail&utm_medium=Ta gline&utm_campaign=InfuseSocial > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > >

↪ replying to André Luiz Rufino

Art Kagel — Mon, 01 Feb 2010 18:36:07 — source: IIUG Forums & Mailing Lists

You have to unload the database, drop and recreate it in a different dbspace(s), and reload it. User tables can be moved but not catalog objects. 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. 2010/2/1 André Luiz Rufino <andre_rufino@msn.com> > Hi Folks, > > Is there a way to move the catalog (systables, systriggers, etc) to another > dbspaces? I want to change the database location. Is that possible in the > 11.5 > FC5 version? > > Thanks, > > André Luiz - MKI > > _________________________________________________________________ > Quer compartilhar fotos com seus amigos? Conheça agora o Windows Live > Fotos. > > > http://www.eutenhomaisnowindowslive.com.br/?utm_source=MSN_Hotmail&utm_medium=Ta gline&utm_campaign=InfuseSocial > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --00151747b81ce22702047e8e41f2

Glossary

dbspace