Use this statement to change the values of database options.

Syntax

SET [ EXISTING ] [ TEMPORARY ] OPTION
 [ userid.| PUBLIC.]option-name = [ option-value ]

userid : identifier, string, or hostvar

option-name : identifier

option-value : string literal

Embedded SQL syntax

SET [ TEMPORARY ] OPTION
 [ userid.| PUBLIC.]option-name = [ option-value ]

userid : identifier, string, or hostvar

option-name : identifier, string, or hostvar

option-value : hostvar (indicator allowed), string, identifier, or number

Remarks

The SET OPTION statement is used to change options that affect the behavior of the database server. Setting the value of an option can change the behavior for all users or only for an individual user. The scope of the change can be either temporary or permanent.

Any option, whether user-defined or not, must have a public setting before a user-specific value can be assigned. The database server does not support setting TEMPORARY values for user-defined options.

The classes of options are:

• database options
• Transact-SQL compatibility options
• replication database options

For a listing and description of all available options, see Database options.

You can set options at three levels of scope: public, user, and temporary. A temporary option takes precedence over other options, and user options take precedence over public options. If you set a user level option for the current user, the corresponding temporary option gets set as well.

Syntax 1 does not allow you to specify a host variable for option-value. However, you may be able to achieve the outcome you want using the EXECUTE IMMEDIATE statement instead. See EXECUTE IMMEDIATE statement [SP].

If you use the EXISTING keyword, option values cannot be set for an individual user ID unless there is already a PUBLIC user ID setting for that option.

If you specify a user ID, the option value applies to that user (or, for a group user ID, the members of that group). If you specify PUBLIC, the option value applies to all users who do not have an individual setting for the option. By default, the option value applies to the currently logged on user ID that issued the SET OPTION statement.

For example, the following statement applies an option change to the user DBA, if DBA is the user issuing the SQL statement:

SET OPTION precision = 40;

However the following statement applies the change to the PUBLIC user ID, a user group to which all users belong.

SET OPTION Public.login_mode = Standard;

Only users with DBA privileges have the authority to set an option for the PUBLIC user ID.

Users can use the SET OPTION statement to change the values for their own user ID. Setting the value of an option for a user ID other then your own is permitted only if you have DBA authority.

Adding the TEMPORARY keyword to the SET OPTION statement changes the duration that the change takes effect. By default, the option value is permanent; it will not change until it is explicitly changed using the SET OPTION statement.

When the SET TEMPORARY OPTION statement is not qualified with a user ID, the new option value is in effect only for the current connection.

When SET TEMPORARY OPTION is used for the PUBLIC user ID, the change is in place for as long as the database is running. When the database is shut down, TEMPORARY options for the PUBLIC group revert back to their permanent value.

Setting temporary options for the PUBLIC user ID offers a security benefit. For example, when the login_mode option is enabled, the database relies on the login security of the system on which it is running. Enabling it temporarily means that a database relying on the security of a Windows domain will not be compromised if the database is shut down and copied to a local computer. In that case, the temporary enabling of the login_mode option reverts to its permanent value, which could be Standard, a mode where integrated logins are not permitted.

If option-value is omitted, the specified option setting is deleted from the database. If it was a personal option setting, the value reverts back to the PUBLIC setting. If a TEMPORARY option is deleted, the option setting reverts back to the permanent setting.

The SET OPTION statement is ignored by the SQL Flagger.

Permissions

None required to set your own options.

DBA authority is required to set database options for another user or PUBLIC.

Side effects

If TEMPORARY is not specified, an automatic commit is performed.

See also

• Database options
• Compatibility options
• SQL Remote options
• SET OPTION statement [Interactive SQL]

Standards and compatibility

Example

Set the date format option to on:

SET OPTION public.date_format = 'Mmm dd yyyy';

Set the wait_for_commit option to On:

SET OPTION wait_for_commit = 'On';

Following are two embedded SQL examples.

1. EXEC SQL SET OPTION :user.:option_name = :value;
2. EXEC SQL SET TEMPORARY OPTION date_format = 'mm/dd/yyyy';

Set the date_format option for the user that is currently connected. Future connections for the same user ID use this option value.

SET OPTION date_format = 'yyyy/mm/dd';

The following statement removes the setting of the date_format option for the current user ID. After executing this statement, the date_format setting for PUBLIC is used instead.

SET OPTION date_format=;