Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 17 » SQL Anywhere Server - Database Administration » Database configuration

Database options

Set database options by using the SET OPTION statement.

The syntax for this statement is as follows:

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

You can specify a user ID to set the option for that user. You can also specify the PUBLIC role name, which sets the option for all users that have not had an option explicitly set for them. If no user ID or PUBLIC is specified, the option change is applied to the currently logged in user that issued the SET OPTION statement. The following are examples of setting a value for a PUBLIC option and then setting a different value for the user Browser.

SET OPTION PUBLIC.isolation_level = 1;
SET OPTION Browser.isolation_level = 2;

A user-defined option can also be set, however the option 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. The following example creates a user-defined option and sets it to a different value for the DBA user.

SET OPTION PUBLIC.user_defined_option = 'allusers';
SET OPTION DBA.user_defined_option = 'DBAonly';

You can set database options at two levels of scope:

  • roles

  • users

Setting an option for a role is only meaningful for the PUBLIC role and user-extended roles. Setting an option for a user-extended role only applies to that user when they are logged in. A user who inherits a role does not inherit that role's option settings.

The order of precedence for option settings is as follows:

  1. All users inherit the PUBLIC role option setting unless the option is set specifically for the user.

  2. A temporary PUBLIC option setting takes precedence over a permanent PUBLIC option setting.

  3. A user option setting takes precedence over a temporary or permanent PUBLIC role setting.

  4. A temporary user option setting for takes precedence over a permanent user option setting, a temporary PUBLIC option setting, and a permanent PUBLIC option setting. A temporary user option can only be set for the currently connected user.

A permanent setting is recorded in the database to which the user issuing a SET OPTION statement is connected.

If you set a permanent option for a user, the corresponding temporary option is set for the user as well. If you set a permanent PUBLIC option, the corresponding temporary PUBLIC option is set as well.

A temporary PUBLIC option setting applies to the current connection, and all new connections. In general, setting a temporary PUBLIC option does not apply to other existing connections unless otherwise noted in the description of the option.

A temporary user option setting applies to the current connection only.

When the option-value is omitted, the specified option is removed for the specified user.

When a temporary or permanent option setting is removed then the option setting reverts to the next temporary or permanent setting according to the order of precedence presented earlier. The following example will help to illustrate the hierarchy. Suppose that the DBA user executes the following statements.

SET OPTION PUBLIC.isolation_level = 0;
SET TEMPORARY OPTION PUBLIC.isolation_level = 1;
SET OPTION isolation_level = 2;
SET TEMPORARY OPTION isolation_level = 3;
SELECT connection_property( 'isolation_level' );

The isolation level for the current user (DBA) is 3. Other users who log in as DBA, have an isolation level of 2 because the isolation level has been set permanently for them. Users other than DBA have a temporary isolation level of 1 until the database is shut down and restarted (assuming that the isolation level has not been set permanently for them).

When the temporary option setting for the current user (DBA) is removed, the setting reverts to the permanent user setting for DBA. This is shown next.

SET TEMPORARY OPTION isolation_level =;
SELECT connection_property( 'isolation_level' );

The isolation level for the current user (DBA) is now 2. When the permanent option setting for the user DBA is removed, the setting reverts to the temporary PUBLIC setting. This is shown next.

SET OPTION isolation_level =;
SELECT connection_property( 'isolation_level' );

The isolation level for the current user (DBA) is now 1. When a temporary option setting for PUBLIC is removed, the setting reverts to the permanent PUBLIC setting. This is shown next.

SET TEMPORARY OPTION PUBLIC.isolation_level =;
SELECT connection_property( 'isolation_level' );

The isolation level for the current user (DBA) is now 0.

Some options (such as those that affect COMMIT behavior) are database-wide in scope. Setting these options requires specific privileges. Other options, such as the isolation_level option, can be applied to just the current connection and need no special privileges.

Changes to option settings take effect at different times, depending on the option. Changing a global database option such as the recovery_time option takes place the next time the database is started. Changing another user's database option takes place the next time the other user reconnects to the database. Generally, only options that affect the current user connection occur immediately. For example, you can change option settings in the middle of a transaction.

Some options that can only be set for the PUBLIC role take effect immediately for existing connections, even though the changed setting is not visible to users via the CONNECTION_PROPERTY function. An example of this behavior is the global_database_id option. For this reason, PUBLIC-only options should not be changed while other users are connected to the database.

Caution

Do not change option values while a cursor is open. Changing the option values while a cursor is open can lead to inconsistent results within the cursor. For example, changing the date_format option while a cursor is open can result in some rows being returned in the old format and some rows returned in the new format. To ensure that the rows in the result set are computed consistently using the new option value, open the cursor after the option value is changed.

Note

In databases that use a Turkish collation or are case-sensitive, executing a query on the SYSOPTION system view or a query similar to the following might not match any rows if the option name is used with the wrong case:

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

Example

The following statement applies an option change for the currently logged in user, in the database to which the user is currently connected.

SET OPTION blocking_timeout = 3;

The following statement applies an option change for the currently logged in user, for the duration of the connection only.

SET TEMPORARY OPTION blocking_timeout = 3;

The following statement applies an option change for the user Browser, in the database to which the user issuing the SET OPTION statement is currently connected.

SET OPTION Browser.blocking_timeout = 3;

The following statement applies a change to the PUBLIC role, in the database to which the user issuing the SET OPTION statement is currently connected. For the option in this example, the user issuing the SET OPTION statement must have the SET ANY SECURITY OPTION system privilege.

SET OPTION PUBLIC.login_mode = 'Standard';

The following statement removes the ansi_blanks option for the currently logged in user, in the database to which the user is currently connected. The ansi_blanks option for the currently logged in user reverts to the temporary or permanent PUBLIC role setting.

SET OPTION ansi_blanks =;