SET OPTION Statement

Syntax

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

Parameters

userid – identifier, string, or host-variable
option-name – identifier, string, or host-variable
option-value – host-variable (indicator allowed), string, identifier, or number

Examples

Example 1 – Set the DATE_FORMAT option:

SET OPTION public.date_format = 'Mmm dd yyyy'

Example 2 – Set the WAIT_FOR_COMMIT option to on:
```
SET OPTION wait_for_commit = 'on'
```

Example 3 – Embedded SQL examples:

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

Usage

The SET OPTION statement is used to change options that affect the behavior of the database and its compatibility with Transact-SQL. Setting the value of an option can change the behavior for all users or an individual user, in either a temporary or permanent scope.

The classes of options are:

General database options
Transact-SQL compatibility database options

Specifying either a user ID or the PUBLIC user ID determines whether the option is set for an individual user, a role represented by userid, or the PUBLIC user ID (the role to which all users are a member). If the option applies to a role ID, option settings are not inherited by members of the role—the change is applied only to the role ID. If no role is specified, the option change is applied to the currently logged-in user ID that issued the SET OPTION statement.

For example, this statement applies an option change to the PUBLIC user ID:

SET OPTION Public.login_mode = standard

Only users with the SET ANY SYSTEM OPTION system privilege have the ability to set a SYSTEM option for the PUBLIC user ID. Only users with the SET ANY SECURITY OPTION system privilege have the ability to set a SECURITY option for the PUBLIC user ID.

In Embedded SQL, only database options can be set temporarily.

Changing the value of an option for the PUBLIC user ID sets the value of the option for any user that has not set its own value. Option values cannot be set for an individual user ID unless there is already a PUBLIC user ID setting for that option.

Users cannot set the options of another user, unless they have the SET ANY PUBLIC OPTION system privilege.

Users can use the SET OPTION statement to change the values for their own user IDs. Setting the value of an option for a user ID other than your own is permitted only if you have the SET ANY PUBLIC OPTION system privilege.

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.

Adding the TEMPORARY keyword to the SET OPTION statement changes the duration that the change takes effect. Without the TEMPORARY keyword, an option change is permanent: it does not change until it is explicitly changed using SET OPTION.

When SET TEMPORARY OPTION is applied using an individual user ID, the new option value is in effect as long as that user is logged in to the database.

When SET TEMPORARY OPTION is used with 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 user ID revert back to their permanent value.

Temporarily setting an option for the PUBLIC user ID, as opposed to setting the value of the option permanently, offers a security advantage. 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 the option temporarily means a database relying on the security of a Windows domain is not compromised if the database is shut down and copied to a local machine. In that case, the temporary enabling of LOGIN_MODE reverts to its permanent value, which might be Standard, a mode in which 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 used reverts to the PUBLIC setting. If a TEMPORARY option is deleted, the option setting reverts to the permanent setting.

Note: For all database options that accept integer values, SAP Sybase IQ truncates any decimal option-value setting to an integer value. For example, the value 3.8 is truncated to 3.

The maximum length of option-value when set to a string is 127 bytes.

Warning! Changing option settings while fetching rows from a cursor is not supported, as it can lead to unpredictable behavior. For example, changing the DATE_FORMAT setting while fetching from a cursor returns different date formats among the rows in the result set. Do not change option settings while fetching rows.

Standards

SQL—Vendor extension to ISO/ANSI SQL grammar.
Sybase—Not supported by Adaptive Server Enterprise. SAP Sybase IQ does support some Adaptive Server Enterprise options using the SET statement.

Permissions

No specific system privileges are required to set your own options. However, the SET ANY SYSTEM OPTION system privilege is required to set system database options for PUBLIC. The SET ANY SECURITY OPTION system privilege is required to set security database options for PUBLIC. Finally, the SET ANY PUBLIC OPTION system privilege is required to set database options for another user.