IDS 12.10.FC12
Solaris 10  1/13

The default location for new databases is the root dbspace.

I would like to be able to change the default location.

It appears that the "Autolocate Database" SQL Admin API argument applies only to a single, specifically named database (that you must supply when invoking the command).  So, that doesn't help with the general case.  (I welcome being set straight, if I am wrong.)

Is there any way to set a default dbspace(s) that applies to all new CREATE DATABASE statements?

Thank you.

DG


Edit:  I would add that, now that I am organizing some of our databases into separate dbspaces (we had previously used S.A.M.E. [Stripe And Mirror Everything] with great success) with additional attention given to putting indexes into separate dbspaces [to use 16KB page size], and also smart large objects also into database-specific sbspaces, I see it as a very useful feature to also be able to specify default locations for these objects, too.

------------------------------
David Grove
------------------------------
#Informix

Hello David,

There seems to be no environment variable or option to set the default dbspace in the CREATE DATABASE statement.

How about making the procedure execute the CREATE DATABASE statement?

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Thank you, SangGyu.

Your suggestion is definitely possible.

But, the "CREATE DATABASE" statement could still be executed (by developers), and then a database could be created in a dbspace other than the one I designated for development/experimental work on that server.  (Which is what I am trying to avoid.)

Regards,

DG

------------------------------
David Grove
------------------------------

Original Message:
Sent: Sat June 13, 2020 08:15 PM
From: SangGyu Jeong
Subject: Default Database Location?

Hello David,

There seems to be no environment variable or option to set the default dbspace in the CREATE DATABASE statement.

How about making the procedure execute the CREATE DATABASE statement?

------------------------------
SangGyu Jeong
Software Engineer
Infrasoft
Seoul Korea, Republic of
------------------------------

Thank you, Art.

But, as you stated:

"...new databases should be automatically located in any dbspace EXCEPT the root dbspace or a dbspace designated as a TENANT dbspace."

That's what I am trying to avoid.  In my new architecture, I have our main databases in their own dedicated dbspaces. I am doing this as a way to get around Informix's limitation of not being able to backup a database, because then I can use onbar to back up particular dbspaces, and, in effect, have a backup of a database.  So, I do not want any other databases in any of these dedicated dbspaces.  I want any new databases which may be created by the CREATE DATABASE statement to be created in a specific dbspace (not root, and not any of the other dbspaces that are dedicated to a single database.

I don't think Informix provides a way to do this.

Who knows, maybe in the future... But, given that IBM has declined to provide a utility to backup a database for at least 15 years (that is, at least since the inclusion of non-traditional data types has made it impossible to use onunload), I'm not holding my breath.  [Aside: Of course dbexport/dbimport can be used to backup a database, but those utilities are not really usable for a large, production database because they lock the database, as well as take a long time.]

Regards,

DG

------------------------------
David Grove
------------------------------

Original Message:
Sent: Sun June 14, 2020 08:39 AM
From: Art Kagel
Subject: Default Database Location?

David:

Yes, there is. Set the AUTOLOCATE parameter in the ONCONFIG file to 1 indicating that new databases should be automatically located in any dbspace EXCEPT the root dbspace or a dbspace designated as a TENANT dbspace.

You cannot control which dbspace is used, but you can force new databases out of the root dbspace. Setting AUTOLOCATE to a number greater than 1 will cause new tables and indexes to be partitioned into that number of partitions by default.

Once a database is created, you can specify the list of dbspaces used by default instead of the database's home dbspace for new tables and indexes in that database. The list is stored in the new sysautolocate catalog table in each database. You can use the "autolocate database add" SQL API function to add the desired dbspace(s) to that catalog table and it will be used for all CREATE DATABASE statements from then on.

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.

Original Message:
Sent: 6/15/2020 1:16:00 PM
From: David Grove
Subject: RE: Default Database Location?

Thank you, Art.

But, as you stated:

"...new databases should be automatically located in any dbspace EXCEPT the root dbspace or a dbspace designated as a TENANT dbspace."

That's what I am trying to avoid.  In my new architecture, I have our main databases in their own dedicated dbspaces. I am doing this as a way to get around Informix's limitation of not being able to backup a database, because then I can use onbar to back up particular dbspaces, and, in effect, have a backup of a database.  So, I do not want any other databases in any of these dedicated dbspaces.  I want any new databases which may be created by the CREATE DATABASE statement to be created in a specific dbspace (not root, and not any of the other dbspaces that are dedicated to a single database.

I don't think Informix provides a way to do this.

Who knows, maybe in the future... But, given that IBM has declined to provide a utility to backup a database for at least 15 years (that is, at least since the inclusion of non-traditional data types has made it impossible to use onunload), I'm not holding my breath.  [Aside: Of course dbexport/dbimport can be used to backup a database, but those utilities are not really usable for a large, production database because they lock the database, as well as take a long time.]

Regards,

DG

------------------------------
David Grove
------------------------------

Original Message:
Sent: 6/15/2020 1:43:00 PM
From: Art Kagel
Subject: RE: Default Database Location?

Just designate each database as a tenant database. Then no other database can be put into that/those dbspace(s)!

Art

Original Message:
Sent: 6/15/2020 1:16:00 PM
From: David Grove
Subject: RE: Default Database Location?

Thank you, Art.

But, as you stated:

"...new databases should be automatically located in any dbspace EXCEPT the root dbspace or a dbspace designated as a TENANT dbspace."

That's what I am trying to avoid.  In my new architecture, I have our main databases in their own dedicated dbspaces. I am doing this as a way to get around Informix's limitation of not being able to backup a database, because then I can use onbar to back up particular dbspaces, and, in effect, have a backup of a database.  So, I do not want any other databases in any of these dedicated dbspaces.  I want any new databases which may be created by the CREATE DATABASE statement to be created in a specific dbspace (not root, and not any of the other dbspaces that are dedicated to a single database.

I don't think Informix provides a way to do this.

Who knows, maybe in the future... But, given that IBM has declined to provide a utility to backup a database for at least 15 years (that is, at least since the inclusion of non-traditional data types has made it impossible to use onunload), I'm not holding my breath.  [Aside: Of course dbexport/dbimport can be used to backup a database, but those utilities are not really usable for a large, production database because they lock the database, as well as take a long time.]

Regards,

DG

------------------------------
David Grove
------------------------------

Thank you for the tip and elucidation, Art.

Now, I see that I need to become informed about tenant databases.

DG

------------------------------
David Grove
------------------------------

Original Message:
Sent: Mon June 15, 2020 01:44 PM
From: Art Kagel
Subject: Default Database Location?

Oh, also, tenant databases with multiple dbspaces can be backed up and restored together as a whole tenant! 

Art

Original Message:
Sent: 6/15/2020 1:43:00 PM
From: Art Kagel
Subject: RE: Default Database Location?

Just designate each database as a tenant database. Then no other database can be put into that/those dbspace(s)!

Art

Original Message:
Sent: 6/15/2020 1:16:00 PM
From: David Grove
Subject: RE: Default Database Location?

Thank you, Art.

But, as you stated:

"...new databases should be automatically located in any dbspace EXCEPT the root dbspace or a dbspace designated as a TENANT dbspace."

That's what I am trying to avoid.  In my new architecture, I have our main databases in their own dedicated dbspaces. I am doing this as a way to get around Informix's limitation of not being able to backup a database, because then I can use onbar to back up particular dbspaces, and, in effect, have a backup of a database.  So, I do not want any other databases in any of these dedicated dbspaces.  I want any new databases which may be created by the CREATE DATABASE statement to be created in a specific dbspace (not root, and not any of the other dbspaces that are dedicated to a single database.

I don't think Informix provides a way to do this.

Who knows, maybe in the future... But, given that IBM has declined to provide a utility to backup a database for at least 15 years (that is, at least since the inclusion of non-traditional data types has made it impossible to use onunload), I'm not holding my breath.  [Aside: Of course dbexport/dbimport can be used to backup a database, but those utilities are not really usable for a large, production database because they lock the database, as well as take a long time.]

Regards,

DG

------------------------------
David Grove
------------------------------


Original Message:
Sent: Sun June 14, 2020 08:39 AM
From: Art Kagel
Subject: Default Database Location?

David:

Yes, there is. Set the AUTOLOCATE parameter in the ONCONFIG file to 1 indicating that new databases should be automatically located in any dbspace EXCEPT the root dbspace or a dbspace designated as a TENANT dbspace.

You cannot control which dbspace is used, but you can force new databases out of the root dbspace. Setting AUTOLOCATE to a number greater than 1 will cause new tables and indexes to be partitioned into that number of partitions by default.

Once a database is created, you can specify the list of dbspaces used by default instead of the database's home dbspace for new tables and indexes in that database. The list is stored in the new sysautolocate catalog table in each database. You can use the "autolocate database add" SQL API function to add the desired dbspace(s) to that catalog table and it will be used for all CREATE DATABASE statements from then on.

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.