SET ENVIRONMENT statement

Use the SET ENVIRONMENT statement to specify settings for session environment variables that can affect how queries are executed in the same routine, or other operations in the current user session. For some options, the session variable overrides default behavior that is set by a configuration parameter or by an environment variable of the client or of the database server.

This statement is an extension to the ISO/ANSI standard for the SQL language.

Syntax

(1)
Notes:
  • 1 With AUTO_READAHEAD only. Neither '2' nor integer is valid for theIFX_BATCHEDREAD options.
  • 2 With EXTDIRECTIVES only. DEFAULT is not valid for IFX_AUTO_REPREPARE, NOVALIDATE, or USTLOW_SAMPLE.
  • 3 With BOUND_IMPL_PDQ and IMPLICIT_PDQ only. 'integer' is not valid for AUTO_STAT_MODE.
  • 4 With RETAINUPDATELOCKS only. 'CURSOR STABILITY' is not valid for USELASTCOMMITTED.
  • 5 With only
  • 6 See USE_DWA session environment options
Element Description Restrictions Syntax
char A single character to set as the default escape character in LIKE or MATCHES operands in the session Must be a single-byte character Quoted String
grid Name of default existing grid for subsequent queries with no explicit GRID clause Must exist, and be defined by the cdr define grid command Quoted String
integer See Options supporting ranges of integer values for the semantics and ranges of integer settings among session environment options Must be valid for the specified session environment option Quoted String
region Default region within an existing grid for subsequent queries with no explicit GRID clause Must exist, and be defined by the cdr define region command Quoted String

Usage

The SET ENVIRONMENT statement can set session environment variables that affect queries or resource use during the current session. Many of its options can override the explicit or default value of an environment variable or of a configuration parameter.

For example, the following statement enables external directives during the current session:
SET ENVIRONMENT EXTDIRECTIVES "1";
This instructs the query optimizer to consider external optimizer directives in the sysdirectives system catalog table, if any are registered there, when choosing query execution plans. The database server complies, even if this conflicts with settings of the EXT_DIRECTIVES configuration parameter and IFX_EXTDIRECTIVES environment variable that disable external directives. If several mechanisms can define database server behavior that a session environment variable can also control, the SET ENVIRONMENT setting generally takes precedence over the following settings during the current session:
  • conflicting system default values,
  • conflicting explicit or default settings of configuration parameters,
  • conflicting explicit or default settings of client or server environment variables.

The scope of session environment variables

As the term session environment variable implies, most options influence the server only during the same session that issues the SET ENVIRONMENT statement. When that session terminates, permanent database objects that it created or modified persist, but other sessions follow the system default behavior, unless they issue their own SET ENVIRONMENT statements. (But options set by a sysdbopen routine are restored in future sessions, if the same sysdbopen routine runs.)

For some options, however, a setting persists only while the routine that set it is running, rather than until the session ends. For example,
SET ENVIRONMENT OPTCOMPIND '2';
instructs the query optimizer to use cost as the basis for subsequent join plans during the session, rather than favoring nested-loop joins. The setting takes effect even if this behavior conflicts with the current 0 or 1 setting of the OPTCOMPIND configuration parameter, or of the environment variable with the same name. Concurrent sessions are not affected by SET ENVIRONMENT OPTCOMPIND statements in UDRs of another session. See also the OPTCOMPIND session environment option.

SQL syntax in some DDL or DML statements can override a session environment variable setting for some database objects, as in this example, where unseen is a fragmented table:

SET ENVIRONMENT AUTO_STAT_MODE OFF;
UPDATE STATISTICS HIGH FOR TABLE unseen AUTO;
The SET ENVIRONMENT statement instructs the server to ignore STATCHANGE criteria for recalculating only stale distribution statistics, so that by default, statistics must be recalculated for all fragments. The AUTO keyword, however, instructs the server to disregard the OFF setting of AUTO_STAT_MODE. This has higher precedence than SET ENVIRONMENT, placing the UPDATE STATISTICS statement outside the scope of the session environment variable. Statistics for fragment of the unseen table will be recalculated selectively, without recalculating any fragments with HIGH mode statistics that are not stale. See also AUTO_STAT_MODE session environment option and STATCHANGE session environment option.
Similarly, this SET ENVIRONMENT statement makes automatic location and implicit fragmentation the default storage option for tables created in the session:
SET ENVIRONMENT AUTOLOCATE "2";
SELECT sname FROM state WHERE LENGTH(sname) < 7 
   INTO RAW reSulT IN dbsp07; --IN clause blocks AUTOLOCATE
The IN clause prevents automatic location for the two fragments that the AUTOLOCATE setting of "2" implies. Rather than storing result table fragments in dbspaces automatically chosen by the database server, this query creates a nonfragmented reSulT table in the dbsp07 dbspace. This explicit Storage Options syntax excludes this SELECT example from the scope of the AUTOLOCATE setting, which can take effect only for new tables that are created during the session with syntax satisfying the automatic location requirements. For more information, see AUTOLOCATE session environment option.

Effects of SET ENVIRONMENT options on concurrent sessions

For some session environment variables, however, SET ENVIRONMENT statements in one session can affect the server behavior in concurrent sessions. For example, suppose a nonadministrative user needs to limit how many ROW locks are available to a session by setting the IFX_SESSION_LIMIT_LOCKS option below the explicit or default SESSION_LIMIT_LOCKS configuration parameter value. A statement like this can accomplishes that goal: 
SET ENVIRONMENT IFX_SESSION_LIMIT_LOCKS "1700";
Until the session ends, users in other sessions who do not hold administrative privileges can hold no more than 1700 locks. The IFX_SESSION_LIMIT_LOCKS setting has no effect on administrators like user informix or DBSA users. See also IFX_SESSION_LIMIT_LOCKS session environment option.

The FORCE_DDL_EXEC session environment option also can affect concurrent transactions, but in an asymmetrical way. The effects of enabling this option can force out transactions in concurrent sessions that hold locks on tables that the enabling session references in ALTER FRAGMENT ON TABLE statements. For more information, see FORCE_DDL_EXEC session environment option.

Options supporting ranges of integer values

The following SET ENVIRONMENT session environment variables can be set to values with delimited integer settings. This table identifies those options, their ranges, and effects of their integer settings.

Table 1. Effects of session environment options that accept integer values

Effects of session environment options that accept integer values
Environment option Integer range Effect
AUTOLOCATE '1' 'integer' '32' Enables automatic storage location of tables, and allocates that number of initial round-robin fragments. A setting of '0' disables this storage automation.
AUTO_READAHEAD '4' 'integer' '4096' For the optional second parameter, a value in the range '4'-'4096' specifies how many pages to include in automatic read-ahead requests. If omitted, the default is '128'.
BOUND_IMPL_PDQ and IMPLICIT_PDQ '1' 'integer' '100' To use explicit PDQPRIORITY environment variable settings as the upper bound (and optional lower bound) of memory granted to a query, set the BOUND_IMPL_PDQ option. The database server scales its PDQPRIORITY estimate by the specified IMPLICIT_PDQ value.
FORCE_DDL_EXEC '2' 'integer' Limits the number of seconds to allow the server to force out transactions that are open or hold a lock on the target table of an ALTER FRAGMENT ON TABLE statement, until the server obtains a lock and exclusive access on the table. If any of those transactions persist beyond the specified time limit, the server stops attempting to force out the transactions.
IFX_SESSION_LIMIT_LOCKS '500' 'integer' SESSION_LIMIT_LOCKS The maximum number of locks for users who are not administrators. This limit cannot exceed the SESSION_LIMIT_LOCKS configuration parameter value. If this parameter is not set, the default value of 2147483647 is the upper limit for 'integer'.
CONNECT_TIMEOUT SESSION_LIMIT _LOCKS 'integer' Limits for how many seconds the CONNECT statement attempts to establish a connection to a database server. A setting of '0' defaults to the CONNECT_TIMEOUT configuration parameter value.
CONNECT_RETRIES '1' 'integer' Sets the maximum number of additional connection attempts by the CONNECT statement after the first connection failure. A connection attempt can end sooner than the specified value, if the CONNECT_TIMEOUT value is exceeded.
OPTCOMPIND '0', '1', or '2' Prioritizes available execution plans for join queries:
  • AUTO_READAHEAD favors nested-loop joins
  • '1' bases the decision on the isolation level
  • '2' favors the lowest cost.
QUERY_TIMEOUT ‘0’ ≤ ‘integer’ ≤ ‘10000’ Sets the maximum clock time, in seconds, that a query can run before it is internally interrupted. This timeout does not affect DML or DDL statements, or administration commands. A setting of ‘0’ effectively disables the timeout.
STATCHANGE '1' 'integer' '100' Sets a percentage of rows that were modified since distribution statistics were calculated. The server uses this as a data-change threshold for UPDATE STATISTICS operations in automatic mode on tables or fragments.
In addition to the session environment variables that accept as their setting a cardinal number in the integer ranges 1isted here, the following session environment variables support as their setting the values '1' and '0', encoding these Boolean semantics:
'1'
Enables the database server behavior associated with the session environment variable
'0'
Disables the database server behavior associated with the session environment variable.
These are the SET ENVIRONMENT options that accept '1' or '0' as their setting:
  • AUTO_READAHEAD,
  • EXTDIRECTIVES,
  • FORCE_DDL_EXEC,
  • IFX_AUTO_REPREPARE,
  • IFX_BATCHEDREAD_INDEX,
  • IFX_BATCHEDREAD_TABLE,
  • NOVALIDATE,
  • USTLOW_SAMPLE.

The OFF, ON, and DEFAULT keyword options

The following keywords have similar effects for several session environment options:

  • OFF disables the specified option
  • ON enables the option
  • DEFAULT sets the option to its default value.

The arguments that follow the option name depend on the syntax of the option. The option name and its ON, OFF, and DEFAULT keywords do not require quotation mark delimiters, and are not case-sensitive. All other arguments must be enclosed between single ( ' ) or double ( " ) quotation marks. If a quoted string is a valid argument for a session environment option, the argument is not case-sensitive. The SET ENVIRONMENT statement syntax diagram is simplified, by showing only single ( ' ) quotation mark around syntax tokens for which double ( " ) quotation marks are also valid as delimiters. As in all SQL operations, both delimiters of a string value (or of an empty string) must be identical.

If you specify an unsupported session environment option name, error -19840 is returned. If you specify an unsupported integer or digit value as the setting for a valid environment option, an option-specific error is returned (for example, error -19843, Invalid IFX_AUTO_REPREPARE value specified).