sa_server_option system procedure

Overrides a server option while the server is running.

Syntax

sa_server_option( 
opt, 
val 
)

Arguments

opt – Use this CHAR(128) parameter to specify a server option name.
val – Use this CHAR(128) parameter to specify the new value for the server option.

Remarks

Database administrators can use this procedure to override some database server options temporarily, without restarting the database server.

The option values that are changed using this procedure are reset to their default values when the server shuts down. If you want to change an option value every time the server is started, you can specify the corresponding database server option when the database server is started if one exists (these are listed in the rightmost column in the table below).

The following option settings can be changed:

Option name	Values	Default
AutoMultiProgrammingLevel	YES, NO	YES
AutoMultiProgrammingLevelStatistics	YES, NO	NO
CacheSizingStatistics	YES, NO	NO
CollectStatistics	YES, NO	YES
ConnsDisabled	YES, NO	NO
ConnsDisabledForDB	YES, NO	NO
ConsoleLogFile	`filename`
ConsoleLogMaxSize	`file-size`, in bytes
CurrentMultiProgrammingLevel	Integer	20
DatabaseCleaner	ON, OFF	ON
DeadlockLogging	ON, OFF, RESET, CLEAR	OFF
DebuggingInformation	YES, NO	NO
DropBadStatistics	YES, NO	YES
DropUnusedStatistics	YES, NO	YES
IdleTimeout	Integer, in minutes	240
IPAddressMonitorPeriod	Integer, in seconds	120 for portable devices, 0 otherwise
LivenessTimeout	Integer, in seconds	120
MaxMultiProgrammingLevel	Integer	Four times the CurrentMultiProgrammingLevel value
MessageCategoryLimit	Integer	400
MinMultiProgrammingLevel	Integer	The minimum of the value of the -gtc server option and the number of logical CPUs on the computer
OptionWatchAction	MESSAGE, ERROR	MESSAGE
OptionWatchList	comma-separated list of database options
ProcedureProfiling	YES, NO, RESET, CLEAR	NO
ProfileFilterConn	`connection-id`
ProfileFilterUser	`user-id`
QuittingTime	valid date and time
RememberLastPlan	YES, NO	NO
RememberLastStatement	YES, NO	NO
RequestFilterConn	`connection-id`, -1
RequestFilterDB	`database-id`, -1
RequestLogFile	filename
RequestLogging	SQL, HOSTVARS, PLAN, PROCEDURES, TRIGGERS, OTHER, BLOCKS, REPLACE, ALL, YES, NONE, NO	NONE
RequestLogMaxSize	`file-size`, in bytes
RequestLogNumFiles	Integer
RequestTiming	YES, NO	NO
SecureFeatures	`feature-list`
StatisticsCleaner	ON, OFF	ON
WebClientLogFile	filename
WebClientLogging	ON, OFF	OFF

AutoMultiProgrammingLevel – When set to YES, the database server automatically adjusts its multiprogramming level, which controls the maximum number of tasks that can be active at a time. If you choose to control the multiprogramming level manually by setting this option to NO, you can still set the initial, minimum, and maximum values for the multiprogramming level.
AutoMultiProgrammingLevelStatistics – When set to YES, statistics for automatic multiprogramming level adjustments appear in the database server message log.
CacheSizingStatistics – When set to YES, display cache information in the database server messages window whenever the cache size changes.
CollectStatistics – When set to YES, the database server collects Performance Monitor statistics.
ConnsDisabled – When set to YES, no other connections are allowed to any databases on the database server.
ConnsDisabledForDB – When set to YES, no other connections are allowed to the current database.
ConsoleLogFile – The name of the file used to record database server message log information. Specifying an empty string stops logging to the file. Any backslash characters in the path must be doubled because this is a SQL string.
ConsoleLogMaxSize – The maximum size, in bytes, of the file used to record database server message log information. When the database server message log file reaches the size specified by either this property or the -on server option, the file is renamed with the extension .old appended (replacing an existing file with the same name if one exists). The database server message log file is then restarted.
CurrentMultiProgrammingLevel – Sets the multiprogramming level of the database server.
DatabaseCleaner – Do not change the setting of this option except on the recommendation of iAnywhere Technical Support.
DeadlockLogging – Controls deadlock logging. The value deadlock_logging is also supported. Deadlock logging options can also be configured in the Database Properties window in Sybase Central. The following values are supported:
- ON – Enables deadlock logging.
- OFF – Disables deadlock logging and leaves the deadlock data available for viewing.
- RESET – Clears the logged deadlock data, if any exists, and then enables deadlock logging.
- CLEAR – Clears the logged deadlock data, if any exists, and then disables deadlock logging.
Once deadlock logging is enabled, you can use the sa_report_deadlocks system procedure to retrieve deadlock information from the database.
DebuggingInformation – Displays diagnostic messages and other messages for troubleshooting purposes. The messages appear in the database server messages window.
DropBadStatistics – Allows automatic statistics management to drop statistics that return bad estimates from the database.
DropUnusedStatistics – Allows automatic statistics management to drop statistics that have not been used for 90 consecutive days from the database.
IdleTimeout – Disconnects TCP/IP connections that have not submitted a request for the specified number of minutes. This prevents inactive connections from holding locks indefinitely.
IPAddressMonitorPeriod – Sets the time to check for new IP addresses in seconds. The minimum value is 10 and the default is 0. For portable devices, the default value is 120 seconds.
LivenessTimeout – A liveness packet is sent periodically across a client/server TCP/IP network to confirm that a connection is intact. If the network server runs for a LivenessTimeout period without detecting a liveness packet, the communication is severed.
MaxMultiProgrammingLevel – Sets the maximum database server multiprogramming level.
MessageCategoryLimit – Sets the minimum number of messages of each severity and category that can be retrieved using the sa_server_messages system procedure.
MinMultiProgrammingLevel – Sets the minimum database server multiprogramming level.
OptionWatchAction – Specifies the action the database server should take when an attempt is made to set an option in the list. The supported values are MESSAGE and ERROR. When OptionWatchAction is set to MESSAGE, and an option specified by OptionWatchList is set, a message appears in the database server messages window indicating that the option being set is on the options watch list.
When OptionWatchAction is set to ERROR, an error is returned indicating that the option cannot be set because it is on the options watch list.

You can view the current setting for this property by executing the following query:
```
SELECT DB_PROPERTY( 'OptionWatchAction' );
```
OptionWatchList – Specifies a comma-separated list of database options that you want to be notified about, or have the database server return an error for, when they are set. The string length is limited to 128 bytes. By default, it is an empty string. For example, the following command adds the automatic_timestamp, float_as_double, and tsql_hex_constant option to the list of options being watched:
```
CALL sa_server_option( 'OptionWatchList','automatic_timestamp,
                                  float_as_double,tsql_hex_constant' )
```
You can view the current setting for this property by executing the following query:
```
SELECT DB_PROPERTY( 'OptionWatchList' );
```
ProcedureProfiling – Controls procedure profiling for stored procedures, functions, events, and triggers. Procedure profiling shows you how long it takes your stored procedures, functions, events, and triggers to execute. You can also set procedure profiling options on the Database Properties window in Sybase Central
- YES – enables procedure profiling for the database you are currently connected to.
- NO – disables procedure profiling and leaves the profiling data available for viewing.
- RESET – returns the profiling counters to zero, without changing the YES or NO setting.
- CLEAR – returns the profiling counters to zero and disables procedure profiling.
  Once profiling is enabled, you can use the sa_procedure_profile_summary and sa_procedure_profile system procedures to retrieve profiling information from the database.
ProfileFilterConn – Instructs the database server to capture profiling information for a specific connection ID, without preventing other connections from using the database. When connection filtering is enabled, the value returned for SELECT PROPERTY( 'ProfileFilterConn' ) is the connection ID of the connection being monitored. If no ID has been specified, or if connection filtering is disabled, the value returned is -1.
ProfileFilterUser – Instructs the database server to capture profiling information for a specific user ID.
QuittingTime – Instructs the database server to shut down at the specified time.
RememberLastPlan – Instructs the database server to capture the long text plan of the last query executed on the connection. This setting is also controlled by the -zp server option.
When RememberLastPlan is turned on, you can obtain the textual representation of the plan of the last query executed on the connection by querying the value of the LastPlanText connection property:
```
SELECT CONNECTION_PROPERTY( 'LastPlanText' );
```
RememberLastStatement – Instructs the database server to capture the most recently prepared SQL statement for each database running on the server. For stored procedure calls, only the outermost procedure call appears, not the statements within the procedure.
When RememberLastStatement is turned on, you can obtain the current value of the LastStatement for a connection by querying the value of the LastStatement connection property:
```
SELECT CONNECTION_PROPERTY( 'LastStatement' );
```
When client statement caching is enabled, and a cached statement is reused, this property returns an empty string.

When RememberLastStatement is turned on, the following statement returns the most recently-prepared statement for the specified connection:
```
SELECT CONNECTION_PROPERTY( 'LastStatement', connection-id );
```
The sa_conn_activity system procedure returns this same information for all connections.

Warning! When -zl is specified, or when the RememberLastStatement server setting is turned on, any user can call the sa_conn_activity system procedure or obtain the value of the LastStatement connection property to find out the most recently-prepared SQL statement for any other user. This option should be used with caution and turned off when it is not required.
RequestFilterConn – Filter the request logging information so that only information for a particular connection is logged. This can help reduce the size of the request log file when monitoring a database server with many active connections or multiple databases. You can obtain the connection ID by executing the following:
```
CALL sa_conn_info( );
```
To specify a specific connection to be logged once you have obtained the connection ID, execute the following:
```
CALL sa_server_option( 'RequestFilterConn', connection-id );
```
Filtering remains in effect until it is explicitly reset, or until the database server is shut down. To reset filtering, use the following statement:
```
CALL sa_server_option( 'RequestFilterConn', -1 );
```
RequestFilterDB – Filter the request logging information so that only information for a particular database is logged. This can help reduce the size of the request log file when monitoring a server with multiple databases. You can obtain the database ID by executing the following statement when you are connected to the desired database:
```
SELECT CONNECTION_PROPERTY( 'DBNumber' );
```
To specify that only information for a particular database is to be logged, execute the following:
```
CALL sa_server_option( 'RequestFilterDB', database-id );
```
Filtering remains in effect until it is explicitly reset, or until the database server is shut down. To reset filtering, use the following statement:
```
CALL sa_server_option( 'RequestFilterDB', -1 );
```
RequestLogFile – The name of the file used to record request information. Specifying an empty string stops logging to the request log file. If request logging is enabled, but the request log file was not specified or has been set to an empty string, the server logs requests to the database server messages window. Any backslash characters in the path must be doubled as this is a SQL string.
When client statement caching is enabled, the max_client_statements_cached option should be set to 0 to disable client statement caching while the request log is captured, if the log will be analyzed using the tracetime.pl Perl script.
RequestLogging – This call turns on logging of individual SQL statements sent to the database server for use in troubleshooting, in conjunction with the database server -zr and -zo options. Values can be combinations of the following, separated by either a plus sign (+), or a comma:
- PLAN – enables logging of execution plans (short form). Execution plans for procedures are also recorded if logging of procedures (PROCEDURES) is enabled.
- HOSTVARS – enables logging of host variable values. If you specify HOSTVARS, the information listed for SQL is also logged.
- PROCEDURES – enables logging of statements executed from within procedures.
- TRIGGERS – enables logging of statements executed from within triggers.
- OTHER – enables logging of additional request types not included by SQL, such as FETCH and PREFETCH. However, if you specify OTHER but do not specify SQL, it is the equivalent of specifying SQL+OTHER. Including OTHER can cause the log file to grow rapidly and could negatively impact server performance.
- BLOCKS – enables logging of details showing when a connection is blocked and unblocked on another connection.
- REPLACE – at the start of logging, the existing request log is replaced with a new (empty) one of the same name. Otherwise, the existing request log is opened and new entries are appended to the end of the file.
- ALL – logs all supported information. This is equivalent to specifying SQL+PLAN+HOSTVARS+PROCEDURES+TRIGGERS+OTHER+BLOCKS. This setting can cause the log file to grow rapidly and could negatively impact server performance.
- NO or NONE – turns off logging to the request log.
You can view the current setting for this property by executing the following query:
```
SELECT PROPERTY( 'RequestLogging' );
```
RequestLogMaxSize – The maximum size of the file used to record request logging information, in bytes. If you specify 0, then there is no maximum size for the request logging file, and the file is never renamed. This is the default value.
When the request log file reaches the size specified by either the sa_server_option system procedure or the -zs server option, the file is renamed with the extension .old appended (replacing an existing file with the same name if one exists). The request log file is then restarted.
RequestLogNumFiles – The number of request log file copies to retain.
If request logging is enabled over a long period of time, the request log file can become large. The -zn option allows you to specify the number of request log file copies to retain.
RequestTiming – Instructs the database server to maintain timing information for each connection. This feature is turned off by default. When it is turned on, the database server maintains cumulative timers for each connection that indicate how much time the connection spent in the server in each of several states. You can use the sa_performance_diagnostics system procedure to obtain a summary of this timing information, or you can retrieve individual values by inspecting the following connection properties:
- ReqCountUnscheduled
- ReqTimeUnscheduled
- ReqCountActive
- ReqTimeActive
- ReqCountBlockIO
- ReqTimeBlockIO
- ReqCountBlockLock
- ReqTimeBlockLock
- ReqCountBlockContention
- ReqTimeBlockContention
When the RequestTiming server property is on, there is a small overhead for each request to maintain the additional counters.
SecureFeatures – Allows you to enable or disable secure features of a database server that is already running. The feature-list is a comma-separated list of feature names or feature sets. By adding a feature to the list, you limit its availability. To remove items from the list of secure features, specify a minus sign (-) before the secure feature name.
Any changes you make to enable or disable features take effect immediately for the connection. The settings do not affect the connection that executes the sa_server_option system procedure; you must disconnect and reconnect to see the change.

Note: To use the sa_server_option system procedure to enable or disable features, you must have specified a key with the -sk option when starting the database server, and set the value of the secure_feature_key database option to the key you specified for -sk (for example, SET TEMPORARY OPTION secure_feature_key = 'j978kls12'). Setting the secure_feature_key database option to the -sk value allows you to change the setting for secure features.

For example, to disable two features and enable a third, you would use this syntax:
```
CALL sa_server_option('SecureFeatures', 'CONSOLE_LOG,WEBCLIENT_LOG,-REQUEST_LOG' );
```
After executing this statement, CONSOLE_LOG, and WEBCLIENT_LOG are added to the list of secure features, and REQUEST_LOG is removed from the list.
StatisticsCleaner – The statistics cleaner fixes statistics that give bad estimates by performing scans on tables. By default the statistics cleaner runs in the background and has a minimal impact on performance.
Turning off the statistics cleaner does not disable the statistic governor, but when the statistics cleaner is turned off, statistics are only created or fixed when a query is run.
WebClientLogFile – The name of the web service client log file. The web service client log file is truncated each time you use the -zoc server option or the WebClientLogFile property to set or reset the file name. Any backslash characters in the path must be doubled because this is a string.
WebClientLogging – This option enables and disables logging of web service clients. The information that is logged includes HTTP requests and response data. Specify ON to start logging to the web service client log file, and specify OFF to stop logging to the file.

Permissions

The following options, which are related to application profiling or request logging, require either DBA or PROFILE authority:

ProcedureProfiling
ProfileFilterConn
ProfileFilterUser
RequestFilterConn
RequestFilterDB
RequestLogFile
RequestLogging
RequestLogMaxSize
RequestLogNumFiles

All other options require DBA authority.

Side effects

None

Example

The following statement disallows new connections to the database server:

CALL sa_server_option( 'ConnsDisabled', 'YES' );

The following statement disallows new connections to the current database:

CALL sa_server_option( 'ConnsDisabledForDB', 'YES' );

The following statement enables logging of all SQL statements, procedure calls, plans, blocking and unblocking events, and specifies that a new request log be started:

CALL sa_server_option( 'RequestLogging', 'SQL+PROCEDURES+BLOCKS+PLAN+REPLACE' );