Reference Manual
   CHAPTER 5. Database Options     

Introduction to database options

Database options control many aspects of database behavior. For example, you can use database options for the purposes such as the following:

• Compatibility     You can control how much like Adaptive Server Enterprise your Adaptive Server Anywhere database operates, and whether SQL that does not conform to SQL/92 generates errors.

• Error handling     You can control what happens when errors such as dividing by zero, or overflow errors, occur.

• Concurrency and transactions     You can control the degree of concurrency, and details of COMMIT behavior, using options.

  Setting options

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

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

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.

For example, the following statement applies an option change to the user DBA, if DBA is the user that issues it:

SET OPTION login_mode = mixed

The following statement applies a change to the PUBLIC user ID, a user group to which all users belong.

SET OPTION Public.login_mode = standard

  Finding option settings

Current option settings for your connection are available as connection properties. You can list all connection properties using the sa_conn_properties system procedure.

call sa_conn_properties

You can obtain a single setting using the connection_property system function. For example, the following statement reports the value of the Ansi_integer_overflow option:

SELECT connection_property ('Ansi_integer_overflow')

  The scope of database options

Some options (such as COMMIT behavior) are database-wide in scope. Setting these options requires DBA permissions. Other options apply only to the current connection (such as ISOLATION_LEVEL), and need no special permissions.

  Setting public options

Only users with DBA privileges have the authority to set an option for the PUBLIC user ID.

Changing the value of an option for the PUBLIC user ID sets the value of the option for all users who have not SET their own value. An option value cannot be set for an individual user ID unless there is already a PUBLIC user ID setting for that option.

  Setting temporary options

Adding the TEMPORARY keyword to the Set Option statement changes the duration of the change. Ordinarily 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.

Setting an option for the PUBLIC user ID temporarily 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 it temporarily means that a database relying on the security of a Windows NT domain will not be compromised if the database is shut down and copied to a local machine. In this case, the login_mode option will revert to its permanent value, which could be Standard, a mode where integrated logins are not permitted.

  Deleting option settings

If option-value is omitted, the specified option setting will be 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 ill-defined behavior. 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.

  Option classification

Adaptive Server Anywhere provides many options. It is convenient to divide them into a few general classes. The classes of options are:

• General database options

• Transact-SQL compatibility database options

• Replication database options

• Interactive SQL options

  Initial option settings

Connections to Adaptive Server Anywhere can be made through the TDS protocol (Open Client and jConnect JDBC connections) or through the Adaptive Server Anywhere protocol (ODBC, Embedded SQL).

If you have users who use both TDS and the Adaptive Server Anywhere-specific protocol, you can configure their initial settings using stored procedures. As it is shipped, Adaptive Server Anywhere uses this method to set Open Client connections and jConnect connections to reflect default Adaptive Server Enterprise behavior.

The initial settings are controlled using the LOGIN_PROCEDURE option. This option names a stored procedure to run when users connect. The default setting is to use the sp_login_environment system procedure. If you wish to change this behavior you can do so.

In its turn, sp_login_environment checks to see if the connection is being made over TDS. If it is, it calls the sp_tsql_environment procedure, which sets several options to new "default" values for the current connection.

     For more information, see LOGIN_PROCEDURE option, sp_login_environment system procedure, and sp_tsql_environment system procedure.

  Duration of options

Changes to option settings take place at different times, depending on the option. Changing a global option such as RECOVERY_TIME takes place the next time the server is started.

Options that affect the current connection only generally take place immediately. You can change option settings in the middle of a transaction, for example. One exception to this is that changing options when a cursor is open can lead to unreliable results. For example, changing DATE_FORMAT may not change the format for the next row when a cursor is opened. Depending on the way the cursor is being retrieved, it may take several rows before the change works its way to the user.