AUTOLOCATE session environment option

Use the AUTOLOCATE environment option of the SET ENVIRONMENT statement to enable or disable the automatic location of databases, indexes, and permanent tables.

Setting this session environment option to an integer value greater than '0' but less than '33' also enables round-robin fragmentation as the default distributed storage for new tables created during the current session, using the AUTOLOCATE setting as the initially allocated number of fragments.

The SET ENVIRONMENT AUTOLOCATE statement of SQL supports the following syntax:


AUTOLOCATE environment option

|--SET ENVIRONMENT AUTOLOCATE--+-'0'-----------+----------------|
                               '-'--integer--'-'

Element	Description	Restrictions	Syntax
integer	Nonnegative integer that defines how many round-robin fragments to allocate initially	Must be in the range `1 ≤ integer ≤ 32`	Quoted String

Usage

The AUTOLOCATE session environment option can have the following values:

'0' or "0": Disables automatic location and implicit fragmentation during the current session.
'integer' or "integer" where 1 ≤ integer ≤ 32: Enables automatic location and round-robin fragmentation. The integer value defines how many round-robin fragments to initially allocate to each permanent table created in the current session.

The current setting of the AUTOLOCATE option overrides the value of the AUTOLOCATE configuration parameter during the current session.

Effect of automatic location on the CREATE DATABASE statement

The setting of the AUTOLOCATE session environment option affects CREATE DATABASE statements that include no IN clause list of dbspaces.

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.

If the AUTOLOCATE configuration parameter has a valid setting greater than 0, the above criteria for automatically choosing a dbspace are already in effect.

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.

Effect on CREATE TABLE statements

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.

Effect on CREATE INDEX statements

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.

Effect on permanent result tables of SELECT statements

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.

The AUTOLOCATE setting has no effect, however, on result tables created with the INTO TEMP or INTO EXTERNAL keyword options, or on result tables created with a Storage Options clause.

Effect on other DML operations that insert rows

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.

See also the IBM® Informix® Administrator's Reference for information about how to run admin( ) and task( ) SQL administration API commands with one of the autolocate datatabase arguments to manage the list of dbspaces that can store table fragments created with automatic location enabled by the AUTOLOCATE environment option or by the AUTOLOCATE configuration parameter.

Disabling automatic location and implicit fragmentation

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.