SET ENVIRONMENT statement

>>-SET ENVIRONMENT---------------------------------------------->

>--+-+-AUTOLOCATE -------------+--'integer'---------------------------+-><
   | '-IFX_SESSION_LIMIT_LOCKS-'                                      |  
   | '-QUERY_TIMEOUT-----------'                                      | 
   +-+-AUTO_READAHEAD--------+--+-'0'-------+--+--------------------+-+   
   | +-IFX_BATCHEDREAD_INDEX-+  +-'1'-------+  |  (1)               | |   
   | '-IFX_BATCHEDREAD_TABLE-'  |  (1)      |  '-------,--'integer'-' |   
   |                            '-------'2'-'                         |   
   +-+-EXTDIRECTIVES------+--+-OFF-----------+------------------------+   
   | +-IFX_AUTO_REPREPARE-+  +-'0'-----------+                        |   
   | +-NOVALIDATE---------+  +-ON------------+                        |   
   | '-USTLOW_SAMPLE------'  +-'1'-----------+                        |   
   |                         |  (2)          |                        |   
   |                         '-------DEFAULT-'                        |   
   +-+-AUTO_STAT_MODE-+--+-OFF-------------+--------------------------+   
   | +-BOUND_IMPL_PDQ-+  +-ON--------------+                          |   
   | '-IMPLICIT_PDQ---'  |  (3)            |                          |   
   |                     '-------'integer'-'                          |   
   +-CLUSTER_TXN_SCOPE--+-'SESSION'-+---------------------------------+   
   |                    +-'SERVER'--+                                 |   
   |                    +-'CLUSTER'-+                                 |   
   |                    '-DEFAULT---'                                 |   
   +-DEFAULTESCCHAR--+-'char'-+---------------------------------------+   
   |                 '-'NONE'-'                                       |
 
   +-DBINFO_DBSPACE_RETURN_NULL_FOR_INVALID_PARTNUM--+-'0'-+----------+
   |                                                 '-'1'-'          | 
   +-FORCE_DDL_EXEC--+-OFF-------+------------------------------------+   
   |                 +-'0'-------+                                    |   
   |                 +-ON--------+                                    |   
   |                 +-'1'-------+                                    |   
   |                 '-'integer'-'                                    |   
   |                 .-OFF---------.                                  |   
   +-GRID_NODE_SKIP--+-+----+------+----------------------------------+   
   |                 | '-ON-'      |                                  |   
   |                 '-+---------+-'                                  |   
   |                   '-DEFAULT-'                                    |   
   +-HDR_TXN_SCOPE--+-'ASYNC'-----+-----------------------------------+   
   |                +-'FULL_SYNC'-+                                   |   
   |                '-'NEAR_SYNC'-'                                   |   
   +-+-INFORMIXCONRETRY-+--+-----------+------------------------------+   
   | '-INFORMIXCONTIME--'  '-'integer'-'                              |   
   +---+-OPTCOMPIND-+----+-DEFAULT---+--------------------------------+   
   |   '-STATCHANGE-'    '-'integer'-'                                |   
   +-+-RETAINUPDATELOCKS-+--+-'ALL'--------------------+--------------+   
   | '-USELASTCOMMITTED--'  +-'NONE'-------------------+              |   
   |                        +-'DIRTY READ'-------------+              |   
   |                        +-'COMMITTED READ'---------+              |   
   |                        |  (4)                     |              |   
   |                        '-------'CURSOR STABILITY'-'              |   
   +-+-SELECT_GRID-----+--+-'grid'---+--------------------------------+   
   | '-SELECT_GRID_ALL-'  +-'region'-+                                |   
   |                      '-DEFAULT--'                                |   
   |  (5)                               (6)                           |   
   +-------USE_DWA--| USE_DWA options |-------------------------------+   
   | '-USE_SHARDING--+-ON--+----------------------------------------' |    
   |                '-OFF-'                                           | 
   | '-SHARDJOIN_FALLBACK--+-ON--+----------------------------------'-|   
   |                       '-OFF-'                                    |                                      

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.

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.

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.

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.

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.