In this post, originally written by Glenn Paulley and posted to sybase.com in September of 2009, Glenn discusses how options are used in SQL Anywhere, including the ability to create and set your own custom options, and the ability to monitor and prevent users from changing options.
In SQL Anywhere, server, database, and connection-level options provide application control of various behaviours that can affect server operation and/or application-visible semantics. "Options" as implemented in SQL Anywhere are not part of the ISO SQL Standard, though the concept of support for "global variables" has been discussed within the SQL standards process. SQL Anywhere options implement "global variables" in conjunction with separate mechanisms to update and retrieve their values.
SQL Anywhere server options are persistently stored in the system catalog and can be viewed using the SYSOPTION view:
Server-defined options are typed: some are binary settings (ON vs. OFF), some are integers, some are specific character string settings, and some are free-form strings. The "magic" of options is that each system option has a default value - the PUBLIC setting - which for many options can be overridden, either permanently or temporarily, by a user or even a specific connection. In the figure above, note that each option and its setting belong to a specific user; in the DEMO database, user_id "2" is the user PUBLIC. Specific option settings for other users are also saved in the catalog with their respective user_ids. These settings "override" the PUBLIC setting for that user; if a user setting does not exist, the PUBLIC setting is used as a default.
For example, by default the PUBLIC setting for the BLOCKING option is ON, meaning that SQL requests will block when conflicting on a row lock. If one desires that SQL statements should fail with an error, rather than block, one can set the BLOCKING option to OFF. Changing the option setting is done through the SET OPTION statement as follows:
SET OPTION BLOCKING = 'OFF';
which changes the BLOCKING option setting for this user and this modification is made to the database catalog. As explained in the SQL Anywhere help, there are timing dependencies to some of the 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 database is started. Generally, only options that affect the current connection 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.
Changing a connection option, such as BLOCKING, typically affects new connections that connect with that userid, and of course the connection that initiated the change, but it does not affect other existing connections. If desired, one can set the option temporarily for the current connection only; this setting is retained in memory and is not made persistent to the database catalog. Changing an option temporarily is done using the TEMPORARY keyword:
SET TEMPORARY OPTION BLOCKING = 'OFF';
Since this temporary setting is not made persistent, one must query its value through other means. In DBISQL, one can use the SET DBISQL command to display the current values (temporary or otherwise) of all of the server-defined options for this connection. In the figure below, note that the value of the BLOCKING option is now set to OFF:
If not using DBISQL, one can query these values using the system procedure sa_conn_properties(), which, in addition to returning option settings for all server-defined connection options, also returns a variety of connection-related counters that can be used for diagnostic purposes:
Connection properties - including options - can be queried individually using the CONNECTION_PROPERTY() function. Note that SQL Anywhere classifies server-defined options into three categories: connection, database, and server. These properties are queried using the CONNECTION_PROPERTY(), DB_PROPERTY(), and PROPERTY() functions respectively. For options, these property functions return their current value, even if set temporarily. Temporarily setting a PUBLIC option, ie.
SET TEMPORARY OPTION PUBLIC.BLOCKING = 'OFF'
alters its value until the database is shutdown, and all new connections will inherit this setting (unless it is overridden for that user). When the database is restarted, the persistent value from the SYSOPTION view will once again be used as the default.
User-defined options
Options in SQL Anywhere are a generic mechanism; while system-defined options are by far more common, any application can establish a user-defined option to store "global variables". To establish a user-defined option, a user with DBA authority must issue a SET OPTION statement to set the value of the option for the PUBLIC user.
For example, the following statement creates the user-defined option "foo":
SET OPTION PUBLIC.FOO = 'ON';
and option "foo" is now stored persistently in the catalog. Once an option has a PUBLIC setting, we're good to go. If, as user DBA, we decide to set our own value for "foo", we can override the PUBLIC setting with:
SET OPTION FOO = 'OFF';
and now both values are visible in the SYSOPTION view (userid "1" is DBA):
User-defined option values are VARCHAR strings - they are not typed in the same manner as system-defined options. The maximum length of a user-defined option depends on the database page size; for 4K pages (the default), the maximum length is 3468 bytes. Unfortunately, user-defined options are not returned by sa_conn_properties() nor can they be queried via the connection_property() function. For most interfaces, the only means to query their value is to execute an SQL query against the SYSOPTION catalog view. However, if your application uses embedded SQL, one can utilize embedded SQL's GET OPTION statement to retrieve the option value for any option, including user-defined options.
Restricting (or reporting on) option settings
Finally, I'd like to point out a feature first added to the 10.0.1 SQL Anywhere release, intended to aid in migration from one SQL Anywhere version to another. That feature is the ability to add "watches" to detect and deny (or report upon) alterations to option settings. This is accomplished through two parameters to the sa_server_option() system procedure: OptionWatchList and OptionWatchAction. Specifying OptionWatchList permits one to specify which options (server- or user-defined) to monitor. OptionWatchAction indicates what action is to be performed: either MESSAGE, which causes a message to appear in the server's console window, or ERROR, which will cause the SET OPTION statement to return an error rather than modify the option value.
As an example, suppose after creating the user-defined option "foo" we want to prohibit any changes to its value. We first set up the watch as follows:
CALL sa_server_option( 'OptionWatchList', 'foo' );
and then specify the desired action:
CALL sa_server_option( 'OptionWatchAction', 'ERROR' );
With this watch in place, any attempt to modify the option results in an error:
- SAP Managed Tags:
