ROOTDBS file

Hello Friends, Need your help with best answer. In my Environment, I installed IDS 12.1 FC8 on Windows server 2012 R2 with instance creation and initialization at the same time. by default, rootdbs.000 file is created with all remaining dbspaces. My developers created all table without mentioning any dbspace name in CREATE syntax. So by default all are stored in rootdbs (correct me if im wrong). so now rootdbs increases very largely and created another rootdbs file with name: rootdbs_p1.000 So at present is there any chance to change the bydefault file(rootdbs) to dbspaces(datadbs1,datadbs2...). Can I move all the tables from rootdbs to any dbspaces.

Alter fragment on table <table> init in <newspace> Insert into developers select sharp_object from causedmepain; Cheers Paul -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MUKESH TANUKU Sent: Tuesday, June 27, 2017 11:57 AM To: ids@iiug.org Subject: ROOTDBS file [39451] Hello Friends, Need your help with best answer. In my Environment, I installed IDS 12.1 FC8 on Windows server 2012 R2 with instance creation and initialization at the same time. by default, rootdbs.000 file is created with all remaining dbspaces. My developers created all table without mentioning any dbspace name in CREATE syntax. So by default all are stored in rootdbs (correct me if im wrong). so now rootdbs increases very largely and created another rootdbs file with name: rootdbs_p1.000 So at present is there any chance to change the bydefault file(rootdbs) to dbspaces(datadbs1,datadbs2...). Can I move all the tables from rootdbs to any dbspaces. **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

It is in production now, so can we execute the alter command will it effect any production. Can you brief about this please

You can move individual tables with: ALTER FRAGMENT ON TABLE mytablename INIT IN new_dbspace;or ALTER FRAGMENT ON TABLE mytablename INIT FRAGMENT BY <fragmentationexpressions using new dbspaces>; However, there is no way to move the database catalog tables except to unload the database(s) - perhaps using dbexport, drop the database(s), and reload them into a new dbspace - if you dbexport-ed you can use dbimport to reload the database. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Jun 27, 2017 at 12:57 PM, MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > Hello Friends, > Need your help with best answer. > > In my Environment, I installed IDS 12.1 FC8 on Windows server 2012 R2 with > instance creation and initialization at the same time. > > by default, rootdbs.000 file is created with all remaining dbspaces. > > My developers created all table without mentioning any dbspace name in > CREATE > syntax. So by default all are stored in rootdbs (correct me if im wrong). > > so now rootdbs increases very largely and created another rootdbs file with > name: rootdbs_p1.000 > > So at present is there any chance to change the bydefault file(rootdbs) to > dbspaces(datadbs1,datadbs2...). > > Can I move all the tables from rootdbs to any dbspaces. > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Yes. The ALTER FRAGMENT will lock the table, so this is a maintenance operation that you will have to perform while your applications are offline. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Jun 27, 2017 at 1:07 PM, MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > It is in production now, so can we execute the alter command will it effect > any production. > > Can you brief about this please > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Thanks Art, Ok fine i have dbexported then how to import all the data to different dbspaces like datadbs My dbexport data is 120GB (uncompressed) nearly 700 tables and 60 Functions, 50 Procedures and 15 triggers.All are stored in rootdbs dbspace. Now my plan is to make individual dbspaces for each. For example, master data in datadbs1, functions in datadbs2, procedures in datadbs3, triggers in datadbs4 like. Is it possible? how with db import?

Tanuku: First thing, go into the <databasename>.exp directory created by dbexport and edit the schema file (<databasename>.sql) and look for any IN clauses or FRAGMENT ... IN clauses and change them to use dbspaces other than ROOTDBS. Then you can cause all tables that do NOT have an IN CLAUSE to load into a different dbspace by using the -d <dbspace> option to dbimport. That said, you are under some misunderstanding. Tables and indexes can be stored in any dbspace that you want. All server items such as triggers, procedures, functions, constraints, and similar all reside in the system catalog tables which will all live in the dbspace in which the database is created. When you manually create a database with CREATE DATABASE you can include an IN <dbspace> clause to place the catalog tables in a specific dbspace. Without an IN <dbspace> clause the database will be created in ROOTDBS be default unless the ONCONFIG parameter AUTOLOCATE is set in which case the engine will select another non-root dbspace based on the rules invoked by AUTOLOCATE. When you use dbimport you can specify the -d <dbspace> option which is equivalent to creating the database with an IN clause specifying that dbspace. Don't forget to set the desired logging option to dbimport (-l or -l buffered). If you want indexes to be created in different dbspaces than tables and user tables also to be created in different dbspaces than the catalog tables the only way is to edit the schema file and add IN clauses to every table and index naming different dbspaces for each as desired. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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, Jun 27, 2017 at 1:19 PM, MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > Thanks Art, > Ok fine i have dbexported then how to import all the data to different > dbspaces like datadbs > > My dbexport data is 120GB (uncompressed) > > nearly 700 tables and 60 Functions, 50 Procedures and 15 triggers.All are > stored in rootdbs dbspace. > > Now my plan is to make individual dbspaces for each. > For example, master data in datadbs1, functions in datadbs2, procedures in > datadbs3, triggers in datadbs4 like. > > Is it possible? > > how with db import? > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Functions, procedures and triggers will all go into the same dbspace as the database - and I cannot think of a reason for them not to. You can modify the create table and index statements to specify storage locations. cheers j. On 6/27/17 1:19 PM, MUKESH TANUKU wrote: > Thanks Art, > Ok fine i have dbexported then how to import all the data to different > dbspaces like datadbs > > My dbexport data is 120GB (uncompressed) > > nearly 700 tables and 60 Functions, 50 Procedures and 15 triggers.All are > stored in rootdbs dbspace. > > Now my plan is to make individual dbspaces for each. > For example, master data in datadbs1, functions in datadbs2, procedures in > datadbs3, triggers in datadbs4 like. > > Is it possible? > > how with db import? > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

Thanks for your response Art. I will work on this and come back if any issues

Hello Art, AUTOLOCATE parameter in not available in onconfig.std or onconfig.<servername> file. How to setthat parameter and where can i find this parameter

Hi, https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.adref.doc/i ds_adr_1106.htm Add an entry to the onconfig file. Regards, David. > On 28 June 2017 at 07:41 MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > > > Hello Art, > > AUTOLOCATE parameter in not available in onconfig.std or onconfig.<servername> > file. > > How to setthat parameter and where can i find this parameter > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

This is why it is important to post your version and platform information. AUTOLOCATE came into the server in version 12.10.xC3 and later. If you have a version earlier than that then you don't have the AUTOLOCATE feature in your system. If you have 12.10.xC3 and later then you do. It is documented, along with the rest of the ONCONFIG parameters in the Administrator's Reference PDF manual or in the IBM Knowledge Center ( https://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0). You can search for "Informix AUTOLOCATE" in Google and it will present the Knowledge Center pages for the AUTOLOCATE ONCONFIG parameter and for the session environment setting (you can set it on or off at the session level). There is also a table, sysautolocate, that contains the list of dbspaces to use and SQL API functions to manage that list. You can download the full PDF documentation set at: http://www-01.ibm.com/support/docview.wss?uid=swg27023505 look for the table labeled "Documentation Sets to Download" Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.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 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 Wed, Jun 28, 2017 at 2:41 AM, MUKESH TANUKU <mukeshbt1328@gmail.com> wrote: > Hello Art, > > AUTOLOCATE parameter in not available in onconfig.std or > onconfig.<servername> > file. > > How to setthat parameter and where can i find this parameter > > > ************************************************************ > ******************* > Forum Note: Use "Reply" to post a response in the discussion forum. > >

I did not find the AUTOLOCATE parameter in onconfig file as well as onconfig.std file also. Im using IDS 12.10 FC8 version

You can certainly add it. The documentation for it and the API functions to manage yhe list of acceptable dbspaces to use is online. Art On Jun 28, 2017 14:01, "MUKESH TANUKU" <mukeshbt1328@gmail.com> wrote: I did not find the AUTOLOCATE parameter in onconfig file as well as onconfig.std file also. Im using IDS 12.10 FC8 version ************************************************************ ******************* Forum Note: Use "Reply" to post a response in the discussion forum.