Managing automatic location and fragmentation

You can control whether the database server automatically chooses the location for databases, indexes, and tables and automatically fragments tables. You can control the list of dbspaces in which the database server stores databases, indexes, and table fragments.

If you enable automatic location and fragmentation, the database server performs the following tasks:

  • Stores new databases for which you do not specify a location in the optimal dbspace instead of in the root dbspace. By default, all dbspaces except dbspaces that are dedicated to tenant databases are available.
  • Stores new tables and indexes for which you do not specify a location in the optimal dbspace instead of in the same dbspace as the database.
  • Allocates an initial number of round-robin fragments for new tables. A table fragment does not have an extent until a row is inserted into the fragment, unless you include the FIRST EXTENT clause in the CREATE TABLE statement.
  • Adds more table fragments as the table grows.

To enable automatic location and fragmentation, set the AUTOLOCATE configuration parameter or the AUTOLOCATE session environment variable to a positive integer.

Automatic location is not applicable to tenant databases or the tables, fragments, and indexes within tenant databases.

To view the list of available dbspaces, query the sysautolocate system catalog table.

To add a dbspace to the list of available dbspaces, run the task() or admin() SQL administration API function with the autolocate database, the autolocate database add, or the autolocate database anywhere argument.

To remove a dbspace from the list of available dbspaces, run the task() or admin() SQL administration API function with the autolocate database remove argument.

To disable automatic location and fragmentation for tables in a particular database, run the task() or admin() SQL administration API function with the autolocate database off argument.

To disable automatic location and fragmentation of tables in all databases, set the AUTOLOCATE configuration parameter or the AUTOLOCATE session environment variable to 0.