AUTOLOCATE session environment option

Suppose that automatic location is disabled, because the AUTOLOCATE configuration parameter is set to 0. You can issue the following statement to enable automatic location and implicit fragmentation in the current session:

SET ENVIRONMENT AUTOLOCATE '2';

The new AUTOLOCATE session environment setting of '2' has the following effect during the session on how the database server processes subsequent CREATE DATABASE statements in which the IN clause is omitted, such as the following example:

CREATE DATABASE IF NOT EXISTS stores_new 
   WITH LOG NLSCASE SENSITIVE;

• The stores_new database will not be created in the root dbspace, if a noncritical dbspace is available.
• Dbspaces with the smallest page size will be favored over those with larger pages.
• Dbspaces with the most free space will be favored.
• Dbspaces with extendable chunks will be favored.

The enabled AUTOLOCATE session environment setting has no effect, however, on the following CREATE DATABASE statement, because the dbspaces specified in the IN clause overrides automatic selection of dbspaces by the database server for the stores_newer database:

CREATE DATABASE IF NOT EXISTS stores_newer IN dbsp04, dbsp05
   WITH LOG NLSCASE SENSITIVE;

Databases like stores_newer that are created without automatic location can store tables and indexes whose storage location and fragmentation are based on the AUTOLOCATE session environment setting. For example, any permanent table or index that is created in the stores_newer database during a session with the AUTOLOCATE session environment option enabled can use implicit round-robin fragmentation for fragments in the dbsp04 and dbsp05 dbspaces, if the table or index is created without the Storage Options clause.

The same SET ENVIRONMENT AUTOLOCATE '2' example has the following effect during the session on CREATE TABLE statements that omit the FIRST EXTENT and Storage Options clauses:

• The database server implicitly uses round-robin distributed storage for each permanent table that is created without a Storage Options clause, allocating two fragments, as specified by the AUTOLOCATE session environment setting.
• The dbspaces that store those fragments will be chosen automatically, based on the same criteria identified above for the CREATE DATABASE statement.

The same SET ENVIRONMENT AUTOLOCATE '2' example has the following effect during the session on CREATE INDEX statements when the Storage Options clause is omitted:

• The database creates a nonfragmented index by default in a dbspace chosen by the server, based on the above criteria. The same index will also store index-key information about rows in any additional table fragments that the database creates automatically, if the storage capacity limit or the maximum number of rows is exceeded for both of the initially allocated table fragments.
• You cannot apply the IN TABLE storage option of the CREATE INDEX statement to indexes on tables that are implicitly fragmented by the SET ENVIRONMENT AUTOLOCATE session environment option.

The SET ENVIRONMENT AUTOLOCATE '2' example also applies automatic storage and implicit fragmentation to SELECT statements that use the INTO STANDARD or INTO RAW keywords, with no Storage Options clause, to store the result of a query in a new permanent table, as in the following query:

SELECT col1::INT fcol1, col2
   FROM tab1 INTO STANDARD MyResultTab;

Because the AUTOLOCATE session environment setting is '2', the database server automatically allocates two fragments for the MyResultTab permanent table that the SELECT statement created.

During the same session, the SET ENVIRONMENT AUTOLOCATE '2' example has the following effects on INSERT, LOAD, and MERGE statements that insert new rows into a table that was created with implicit fragmentation. When rows are inserted, the fragment with the fewest rows is favored until all fragments contain the same number of rows. But if the DML operation attempts to insert more rows than can fit in the existing fragments, or attempts to insert rows beyond the maximum number of rows that a fragment can contain, the database server takes the following actions:

• It creates a new round-robin fragment of the same page size as the original fragments, allocating this in a dbspace that has enough free pages. As in the case of CREATE DATABASE, if noncritical dbspaces are available, the database server does not choose critical dbspaces.
• After the new fragment is automatically attached to the table, the database server resumes inserting rows for the current INSERT, LOAD, or MERGE operation.

Any other integer setting for AUTOLOCATE within the range from "1" to "32" has the effects described above, but for integer initially allocated fragments. For example, the following statement instructs the database server to initially allocate 9 round-robin fragments to each new permanent table that uses automatic location and implicit fragmentation:

SET ENVIRONMENT AUTOLOCATE '9';

.

The next example enables automatic location and allocates a single initial fragment.

SET ENVIRONMENT AUTOLOCATE '1';

The fragment is initially empty, but if rows are inserted into the table, all are inserted into that fragment, because no other exists. Tables created in the session with implicit fragmentation can be referenced by the ALTER FRAGMENT statement, despite their superficial resemblance to nonfragmented tables. When no additional rows can be inserted into the original fragment, the database server automatically attaches a new round-robin fragment to the table.

To disable automatic location and default round-robin fragmentation for the current session, run the following statement:

SET ENVIRONMENT AUTOLOCATE '0';

This setting only affects databases, indexes, and permanent tables that are created by subsequent DDL operations in the current session, Other sessions follow the behavior that corresponds to the AUTOLOCATE configuration parameter setting, or to SET ENVIRONMENT AUTOLOCATE statements issued in those sessions.

When automatic location is not in effect, the database server replaces the behavior described above with the following legacy behavior of Informix releases earlier than 12.10.xC3:

• Databases are created by default in the root dbspace, or in the dbspace that the IN clause of the CREATE DATABASE statement specifies,
• Tables are created without implicit fragmentation, and stored according to the default or explicit Storage Options clauses of the CREATE TABLE, ALTER TABLE, and ALTER FRAGMENT statements.
• New round-robin fragments are not created automatically when all existing fragments of the table are at their limit for storage size or for the number of rows in a fragment.