Setting database options

You set options with the SET OPTION statement. It has the following general syntax:

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

You can specify a user ID or group name to set the option for that user or group only. Every user belongs to the PUBLIC group. If no user ID or group is specified, the option change is applied to the currently logged on user ID that issued the SET OPTION statement.

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, the following statement applies an option change to the user DBA, if DBA is the user that issues it:

SET OPTION blocking_timeout = 3;

The following statement applies a change to the PUBLIC user ID, a user group to which all users belong. You must have DBA authority to execute this statement.

SET OPTION PUBLIC.login_mode = 'Standard';

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.

Caution

Changing option settings while fetching rows from a cursor is not supported because it can lead to unreliable results. For example, changing the date_format setting while fetching from a cursor would lead to different date formats among the rows in the result set. Do not change option settings while fetching rows.

Note

In databases that use a Turkish collation or are case sensitive, executing a query on SYSOPTION or a query like the following may not match any rows if the option name is used with the wrong case:

SELECT * FROM sa_conn_properties( ) WHERE propname = 'BLOCKING';

For information about the proper case for option names, see Alphabetical list of options.

Setting database options from Sybase Central

To set database options (Sybase Central)

Open the database server.
Right-click the database and choose Options.
Edit the values.

Tips

With the Database Options window, you can also set database options for specific users and groups (when you open this window for a user or group, it is called the User Options window or Group Options window, respectively).

When you set options for the database itself, you are actually setting options for the PUBLIC group in that database because all users and groups inherit option settings from PUBLIC.