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
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/12/2020 7:26:00 PM
From: David Grove
Subject: Default Database Location?
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