SET OPTION Statement

Quick Links:

Syntax

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

Parameters

(back to top)

option-value – a host-variable (indicator allowed), string, identifier, or number. The maximum length of option-value when set to a string is 127 bytes.
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.
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.

EXISTING – option values cannot be set for an individual user ID unless there is already a PUBLIC user ID setting for that option.

TEMPORARY – changes the duration that the change takes effect. Without the TEMPORARY clause , an option change is permanent: it does not change until it is explicitly changed using SET OPTION.
When the TEMPORARY clause 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 the TEMPORARY clause 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 to their permanent value.
If a TEMPORARY option is deleted, the option setting reverts to the permanent setting.

Examples

(back to top)

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

(back to top)

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

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.

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.

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

(back to top)

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

Permissions

(back to top)

No specific system privileges are required to set your own options.

The SET ANY PUBLIC OPTION system privilege is required to set database options for another user.

The SET ANY SYSTEM OPTION system privilege is required to set a SYSTEM option for the PUBLIC user ID.

The SET ANY SECURITY OPTION system privilege is required to set a SECURITY option for the PUBLIC user ID.