The SET ENVIRONMENT DS_CLASS Statement

New syntax of the SET ENVIRONMENT statement of SQL can associate an existing resource class with the current user session, or change the mutability attribute of the DS_CLASS session environment variable:

>>-SET ENVIRONMENT DS_CLASS--+-'--ds_class_name--'-+-----------><
                             +-'DEFAULT'-----------+
                             +-MUTABLE-------------+
                             '-IMMUTABLE-----------'
 

Here DS_CLASS is a new session environment variable that determines what portion of shared memory can be parceled out to a query, and how many queries accessing PDQ memory can execute concurrently in sessions associated with the specified resource class. (The onutil utility can create or update resource class definitions.)

The term that follows the DS_CLASS keyword has one of the following effects:

• ds_class_name associates the current user session with the specified name of an existing resource class, as declared in the onconfig file, or by the onutil utility, or else the built-in DEFAULT class.
• MUTABLE enables a non-DBA user to reset the DS_CLASS value.
• IMMUTABLE prevents users from specifying a different resource class. (The DBA privilege is required to specify MUTABLE or IMMUTABLE.)

For example, the following statements associate the session with the DEVELOPMENT resource class, and set the mutability to IMMUTABLE:

SET ENVIRONMENT DS_CLASS 'DEVELOPMENT';
SET ENVIRONMENT DS_CLASS IMMUTABLE;

By default, a query uses the built-in DEFAULT resource class if any of the following is true:

• SET ENVIRONMENT DS_CLASS "DEFAULT" was successfully executed in the same session before the query began to execute.
• No onutil command specified DS_CLASSINFO before the session started.
• No valid SET ENVIRONMENT DS_CLASS statement was issued prior to the execution of a query.
• The most recent SET ENVIRONMENT DS_CLASS statement in the same session referenced an unknown value for ds_class_name that no onutil command had previously declared for the database server.

The DBA can use the onutil SET command when the server is online to alter the CLASS_DS_MEM_QUOTA and CLASS_DS_MAX_QUERIES settings for any DS_CLASS. Since this must be done one DS_CLASS at a time, the administrator might have to execute a sequence of commands to achieve the desired settings. The sequence of commands would have to also ensure that at no point an attempt at a setting causes the total CLASS_DS_MEM_QUOTA for all defined resource classes to exceed 100%.

Typically a DBA who uses resource classes would include the new SET ENVIRONMENT statement in the public.sysdbopen( ) or user.sysdbopen( ) procedures to associate each database with a resource class. If public.sysdbopen( ) specifies a resource class for the PUBLIC group, and the DBA creates user.sysdbopen( ) procedures for one or more users, the DBA should include an appropriate SET ENVIRONMENT DS_CLASS statement in each user.sysdbopen( ) procedure, because the public.sysdbopen( ) procedure is not executed when these users connect to the database.

If the SET ENVIRONMENT statement within sysdbopen( ) fails, then (by default) the session is associated with the DEFAULT resource class.

SET ENVIRONMENT DS_CLASS can specify a resource class only when DS_CLASS is set to MUTABLE. Only a user who holds the DBA access privilege can use the SET ENVIRONMENT DS_CLASS statement (or the onmode -q command, as described below) to set the mutability of the DS_CLASS session environment variable.