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 - SQL Reference » System procedures » Alphabetical list of system procedures

sa_server_option system procedure

Overrides a database server option while the database server is running.

Syntax
sa_server_option( 
opt 
, val 
)
Parameters
  • opt

    Use this CHAR(128) parameter to specify a database server option name.

  • val

    Use this LONG VARCHAR parameter to specify the new value for the database server option.

Remarks

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 database server shuts down. To change an option value every time you start the database server, specify the corresponding database server option when the database server is started (if one exists).

The following option settings can be changed. Default values are shown in bold:

Option name Values Additional information
AutoMultiProgrammingLevel

YES, NO

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 YES, NO

When set to YES, statistics for automatic multiprogramming level adjustments appear in the database server message log.

CacheSizingStatistics YES, NO

When set to YES, display cache information in the database server messages window whenever the cache size changes.

CockpitDB

To start or stop the Cockpit:

'DBF=filename | DBF=AUTO[; START=ON | START=OFF]'

To create a copy of the Cockpit database:

'SAVETO=filename[; ACTION=CONTINUE | ACTION=SWITCH] '

Use the CockpitDB option to start or stop the Cockpit, or change the Cockpit database. Specify at least one parameter. The DBF and START parameters cannot be specified with the SAVETO and ACTION parameters.

  • Use the DBF parameter to specify the Cockpit database for the Cockpit to use. The Cockpit configuration settings are saved to this file. If you specify a file that does not exist, then it is created. Specify the full path for the file. Otherwise if you use a relative path, it is read relative to the current working directory (or if disk sandboxing is enabled, then the relative path is read relative to the directory where the main database file is located). The value specified with the DBF parameter becomes the default DBF value until it is changed or the database server is shut down.

    Set DBF=AUTO to run the Cockpit using a temporary database. When the Cockpit stops, its temporary database is deleted, so any changes made within the Cockpit are not saved.

    The DBF parameter can only be specified when the Cockpit is not running. When the DBF parameter is specified, the Cockpit starts unless START=OFF is also specified.

    When START is set to ON, the Cockpit starts. When START is set to OFF, the Cockpit stops.

  • Use the SAVETO parameter to create a Cockpit database that contains the settings of the currently running Cockpit. Specify the full path for the file. Otherwise if you use a relative path, it is read relative to the current working directory (or if disk sandboxing is enabled, then the relative path is read relative to the directory where the main database file is located).

    Set ACTION=SWITCH to have the Cockpit use this new Cockpit immediately start using this database. Otherwise, when ACTION=CONTINUE is specified, the Cockpit continues using the current Cockpit database.

To start the Cockpit, execute CALL sa_server_option('CockpitDB' 'START=ON;DBF=filename');.

To stop the Cockpit, execute CALL sa_server_option('CockpitDB' 'START=OFF');.

To change the default file name for the Cockpit, first stop the Cockpit, and then execute CALL sa_server_option('CockpitDB 'START=OFF;DBF=filename); to define the new default file.

To create a backup copy of the Cockpit database, execute CALL sa_server_option ('CockpitDB' 'SAVETO=c:\file.db');.

To create a new Cockpit database and have the Cockpit use this file, execute CALL sa_server_option ('CockpitDB' 'SAVETO=c:\file.db;ACTION=SWITCH');.

CollectStatistics YES, NO

When set to YES, the database server collects Performance Monitor statistics.

When set through the sa_server_option to NO, will disable statement performance data collection.

ConnsDisabled YES, NO

When set to YES, no other connections are allowed to any databases on the database server.

ConnsDisabledForDB YES, NO

When set to YES, no other connections are allowed to the current database.

ConsoleLogFile filename

The name of the file used to record database server message log information. Specifying an empty string stops logging to the file. Double any backslash characters in the path because this value is a SQL string.

ConsoleLogMaxSize file-size, in bytes

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 Integer. Default is 20.

Sets the multiprogramming level of the database server.

DatabaseCleaner ON, OFF

Do not change the setting of this option except on the recommendation of Technical Support.

DeadlockLogging ON, OFF, RESET, CLEAR

Controls deadlock logging. The value deadlock_logging is also supported. 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 YES, NO

Displays diagnostic messages and other messages for troubleshooting purposes. The messages appear in the database server messages window.

DiskSandbox ON, OFF

Sets the default disk sandbox settings for all databases started on the database server that do not have explicit disk sandbox settings. Changing the disk sandbox settings by using the sa_server_option system procedure does not affect databases already running on the database server. To use the sa_server_option system procedure to change disk sandbox settings, you must provide the secured feature key for the manage_disk_sandbox feature.

DropBadStatistics YES, NO

Allows automatic statistics management to drop statistics that return bad estimates from the database.

DropUnusedStatistics YES, NO

Allows automatic statistics management to drop statistics that have not been used for 90 consecutive days from the database.

IdleTimeout Integer, in minutes. The default is 240.

Disconnects TCP/IP connections that have not submitted a request for the specified number of minutes. This prevents inactive connections from holding locks indefinitely.

The minimum value is 0 (no timeout) and the maximum value is 32767.

IPAddressMonitorPeriod Integer, in seconds. The default is 120 for portable devices, 0 otherwise.

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 Integer, in seconds. The default is 120.

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.

The minimum value is 0 (no timeout) and the maximum value is 32767.

MaxMultiProgrammingLevel Integer. The default is four times the value for CurrentMultiProgrammingLevel

Sets the maximum database server multiprogramming level.

MessageCategoryLimit Integer. The default is 400.

Sets the minimum number of messages of each severity and category that can be retrieved using the sa_server_messages system procedure.

MinMultiProgrammingLevel Integer. The default is the minimum of the value of the -gtc server option and the number of logical CPUs on the computer.

Sets the minimum database server multiprogramming level.

OptionWatchAction MESSAGE, ERROR

Specifies the action that the database server takes 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 Comma-separated list of database options.

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 YES, NO, RESET, CLEAR

See the ProcedureProfiling database property description for more information.

ProcessorAffinity Comma-delimited list of processor numbers and/or ranges. The default is that all processors are used or the setting of the -gta option.

Instructs the database server which logical processors to use on Windows or Linux. Specify a comma-delimited list of processor numbers and/or ranges. If the lower endpoint of a range is omitted, then it is assumed to be zero. If the upper endpoint of a range is omitted, then it is assumed to be the highest CPU available to the operating system. The in_use column returned by the sa_cpu_topology system procedure contains the current processor affinity of the database server, the in_use column indicates whether the database server is using a processor, and the user_selected column indicates which physical processors were specified by the -gta database server option or the ProcessorAffinity server property. Alternatively, you can query the value of the ProcessorAffinity database server property.

The database server might not use all of the specified logical processors in the following cases:

  • If one or more of the specified logical processors does not exist, or is offline.

  • If the license does not allow it.

If you specify an invalid processor ID, then sa_server_option returns an error.

ProfileFilterConn connection-id

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 user-id

Instructs the database server to capture profiling information for a specific user ID.

PropertyHistoryList ON, OFF, NONE, comma-delimited list of database server properties
  • ON When PropertyHistoryList is turned on, a default list of properties is tracked. The default is ON.
  • OFF When PropertyHistoryList is turned off, property tracking is disabled for the database server.
  • NONE Setting this property to NONE enables property tracking but no properties are tracked by the database server. Only properties requested by databases are tracked.
  • Comma-delimited list of database server properties

    Specify a comma-delimited list of database server properties to track.

PropertyHistorySize time, memory-size, MAX, DEFAULT

Specifies either the minimum amount of time to store tracked property values or the maximum amount of memory to use to store tracked property values. To set this property to a time, use the format '[HH:]MM:SS'. To set this property to a memory size, specify the memory size in bytes. For example, 1M. The default value is '00:10:00' (ten minutes), unless that amount of time violates the maximum size limit, in which case MAX is used as the default.

Specify MAX to request that the fixed maximum amount of memory is used. The maximum memory is either 2% of the cache or 256 MB, whichever is smaller.

When PropertyHistoryList is set, the amount of memory used for tracking property history is updated. If it has increased beyond the fixed maximum amount of memory allowed for storing property history, then an error is raised. If the amount of memory used has decreased and there is no longer enough memory to track the specified properties' history, then an error is raised. If an error is raised, then the property history reverts back to its previous value.

If there is insufficient memory to track all properties specified by PropertyHistoryList, an error is returned and the property is not added to the list of tracked properties.

QuittingTime Valid date and time

Instructs the database server to shut down at the specified time.

RememberLastPlan YES, NO

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, 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 YES, NO

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.

Caution

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. Use this option with caution and turn it off when it is not required.

RequestFilterConn connection-id, -1

Filter the request logging information so that only information for a particular connection is logged. This filtering can 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 log a specific connection once you have obtained the connection ID, execute the following statement:

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 database-id, -1

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 log only information for a particular database, execute the following statement:

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 filename

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. Double any backslash characters in the path because this value is a SQL string.

When client statement caching is enabled, set the max_client_statements_cached option 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 SQL, HOSTVARS, PLAN, PROCEDURES, TRIGGERS, OTHER, BLOCKS, REPLACE, ALL, YES, NONE, NO

This call turns on logging of individual SQL statements sent to the database server for use in troubleshooting 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). If logging of procedures (PROCEDURES) is enabled, execution plans for procedures are also recorded.

  • 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 request 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 value is equivalent to specifying SQL+PLAN+HOSTVARS+PROCEDURES+TRIGGERS+OTHER+BLOCKS. This setting can cause the request 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 file-size, in bytes

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 value is the default.

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 Integer

The number of request log file copies to retain.

If request logging is enabled over a long period, the request log file can become large. The -zn option allows you to specify the number of request log file copies to retain.

RequestTiming YES, NO

Instructs the database server to maintain timing information for each new connection. This feature is turned off by default. When it is turned on, the database server maintains cumulative timers for all new connections that indicate how much time the connection spent in the server in each of several states. The change is only effective for new connections, and lasts for the duration each connection.

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.

rlv_memory_mb Specifies the maximum amount of memory (the RLV store), in MB, to reserve for row-level versioning. The default value is 2048 MB. The minimum value is 1 MB. The maximum value is 2048. Any other value will set the amount of memory to 2048 MB.
SecureFeatures feature-list

Allows you to manage secured features for 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 secured features, specify a minus sign (-) before the feature name.

For example, to secure two features, use the following syntax:

CALL sa_server_option('SecureFeatures', 'console_log,webclient_log' );

After executing this statement, the list of secured features is set according to what has been changed.

To secure the LOCAL feature set, but exclude the LOCAL_IO feature subset, use the following syntax:

CALL sa_server_option('SecureFeatures', 'local,-local_io' );

To call sa_server_option('SecureFeatures', ...), the connection must have the MANAGE_FEATURES feature enabled on the connection. The -sf database server option secures MANAGE_FEATURES by default. If the current user/connection that set the SecureFeatures option does not have a feature explicitly enabled, and the list of features was disabled by that connection, then the connection is immediately affected.

Any changes you make to allow or prevent access to features take effect immediately for the database server. The connection that executes the sa_server_option system procedure may or may not be affected, depending on the secured feature key the connection is using and whether it allows the connection access to the specified features.

SingleCLRInstanceVersion 35, 40, 45 This option specifies whether or not the database server uses one CLR external environment for all databases running on the database server. The option value indicates which version to start.
Note This option cannot be changed once the database server starts a CLR external environment.
SingleJVMLocation filename This option specifies the location of the Java VM that the database server is using for all databases running on the database server. This option can only be set if the database server has been started with the -sjvm database server option, or the UseSingleJVMInstance database server option is set to ON.
StatisticsCleaner ON, OFF

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.

TopologyAwareScheduling ON, OFF

Turns topology-aware scheduling on or off. topology-aware scheduling causes tasks to use a single core per socket before attempting to use another core on the same socket (tasks are scheduled to use one thread per core before attempting to use other threads on the same core).

UseSingleJVMInstance ON, OFF

This option specifies whether or not the database server uses one Java VM for all databases running on the database server.

Note This option cannot be changed once the database server starts a Java VM.
WebClientLogFile filename

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. Double any backslash characters in the path because this value is a string.

WebClientLogging ON, OFF

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.

Privileges

You must have EXECUTE privilege on the system procedure.

You must have the MANAGE PROFILING system privilege to use the following options, which are related to request logging:

  • ProcedureProfiling
  • ProfileFilterConn
  • ProfileFilterUser
  • RequestFilterConn
  • RequestFilterDB
  • RequestLogFile
  • RequestLogging
  • RequestLogMaxSize
  • RequestLogNumFiles

For all other options, your must have the SERVER OPERATOR system privilege.

You must also have the MANAGE ANY EXTERNAL ENVIRONMENT system privilege to use the SingleJVMLocation option.

Side effects

None

Example

The following statement causes cache information to be displayed in the database server messages window whenever the cache size changes:

CALL sa_server_option( 'CacheSizingStatistics', '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 starts a new request log:

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