SET OPTION statement

Description

Changes database options.

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:

1. EXEC SQL SET OPTION :user.:option_name = :value;
2. 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 user group represented by userid or the PUBLIC user ID, the user group to which all users are a member. If no user group is specified, the option change is applied to the currently logged on user ID which 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 login_mode = mixed

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.

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 which has not SET their 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 DBA authority.

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.

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 will not change until it is explicitly changed using the SET OPTION statement.

When the SET TEMPORARY OPTION statement 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 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 log in security of the system on which it is running. Enabling it temporarily means a database relying on the security of a Windows domain will not be compromised if the database is shutdown and copied to a local machine. In that case, the temporary enabling of the login_mode option will revert 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 will be deleted from the database. If it was a personal option setting, then the value used will revert back to the PUBLIC setting. If a TEMPORARY option is deleted, the option setting will revert back to the permanent setting.

For all database options that accept integer values, 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 ill-defined 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.