Option values   With Syntax 1, option values can be one of:

• the keywords ON, OFF, or NULL
• a string literal value, within single quotation marks
• a number of any valid format, including NUMERIC
• within an embedded SQL program, the value of a host variable hostvar
• the value of a SQL variable with a variable-name that must begin with an @ sign

With Syntax 2, you can specify any valid identifier as an option value. With Syntax 2, the database server treats the name of the identifier as if it were a string literal enclosed within single quotes. For example, the statement:

SET TEMPORARY OPTION ansi_update_constraints = 'strict';

is equivalent to

SET TEMPORARY OPTION ansi_update_constraints = strict;

Option scope   With most options, you can set their value at three levels of scope: public, user, and connection. Some specific options, such as login_mode, are restricted to the public level only. A connection option takes precedence over the other two levels, and user options take precedence over public options. You set a connection-level option by using the TEMPORARY keyword. If you set a user-level option for the current user, the corresponding connection-level option is set at the same time.

If you specify a user ID, the option value applies to that user. 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 group to which all users belong:

SET OPTION PUBLIC.login_mode = 'Standard';

TEMPORARY options   By default, a new option value is made permanent unless the TEMPORARY keyword is specified. Adding the TEMPORARY keyword to the SET OPTION statement affects the duration of the change.

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 is not 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.

Removing option settings   If option-value is omitted, the specified option setting is deleted from the database. If it was a user-level 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 for that user.

Option data types   Options can have Boolean, numeric, or string values, but are always stored as strings in the database. Option settings are always returned as strings as the result of a property function or when returned as a result of a function or system stored procedure. Option values cannot be larger than the database page size.

User-defined options   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. For example, to create a user-defined option named ApplicationControl, you first issue the statement:

SET OPTION PUBLIC.ApplicationControl = 'Default';

This statement sets the ApplicationControl option to Default for all users, and takes effect with each new connection to the server. Subsequently, an individual user may establish their own setting for this option by issuing a separate SET OPTION statement.

Restrictions   Only users with DBA authority have the authority to set an option for the PUBLIC user ID or for other database users.

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.