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

sa_server_option system procedure

Overrides a server option while the server is running.

sa_server_option(
opt,
val
)

• 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.

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 server option when the database server is started if one exists.

The following option settings can be changed:

CacheSizingStatistics    When set to YES, display cache information in the Server Messages window whenever the cache size changes. See -cs server option.

CollectStatistics    When set to YES, the database server collects Performance Monitor statistics. See -k server option.

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 Server Messages window 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. See -o server option.

ConsoleLogMaxSize    The maximum size, in bytes, of the file used to record Server Messages window information. When the output log file reaches the size specified by either the sa_server_option system procedure 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 output log file is then restarted. See -on server option.

DatabaseCleaner    Do not change the setting of this option except on the recommendation of iAnywhere Technical Support. See also sa_clean_database system procedure.

DebuggingInformation    Displays diagnostic messages and other messages for troubleshooting purposes. The messages appear in the Server Messages window. See -z server option.

IdleTimeout    Disconnects TCP/IP or SPX connections that have not submitted a request for the specified number of minutes. This prevents inactive connections from holding locks indefinitely. See -ti server option.

LivenessTimeout    A liveness packet is sent periodically across a client/server TCP/IP or SPX 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. See -tl server option.

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 property sheet 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. See Procedure profiling using system procedures.

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. See -tq server option.

RememberLastPlan property    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. See -zp server option.

You can obtain the current value of the LastPlan for a 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.

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' )

For more information, see Server-level properties and -zl server option.

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.

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 Server Messages window. Any backslash characters in the path must be doubled as this is a SQL string. See -zo server option.

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:

• SQL    enables logging of the following:

  • START DATABASE statements
  • STOP DATABASE statements
  • STOP ENGINE statements
  • Statement preparation and execution
  • EXECUTE IMMEDIATE statement
  • Option settings
  • COMMIT statements
  • ROLLBACK statements
  • PREPARE TO COMMIT operations
  • Connects and disconnects
  • Beginnings of transactions
  • DROP STATEMENT statements
  • Cursor explanations
  • Cursor open, close, and resume
  • Errors

• PLAN    enables logging of query plans (short form). Query 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 find the current value of the RequestLogging setting using SELECT property( 'RequestLogging' ).

For more information, see -zr server option, and Server-level properties.

RequestLogMaxSize    The maximum size of the file used to record request logging information, in bytes. If you 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. See -zs server option.

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. See -zn server option.

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

See Connection-level properties.

When the RequestTiming server property is on, there is a small overhead for each request to maintain the additional counters. See -zt server option, and sa_performance_diagnostics system procedure.

SecureFeatures    Specifies features that are disabled for databases running on this database server. The feature-list is a comma-separated list of feature names or feature sets. For a list of valid feature-list values, see -sf server option.

Any changes you make to enable or disable features take effect immediately. The settings do not affect the connection that executes the sa_server_option system procedure.

To use the sa_server_option system procedure to enable or disable features for all databases running on the current database server, you must specify a key with the -sk option when starting the database server, and then set the value of the secure_feature_key database option to the key specified by -sk. Setting the secure_feature_key database option to the -sk value enables all features for the current connection. See -sk server option and secure_feature_key [database].

DBA authority required

None

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 dbo.sa_server_option( 'RequestLogging', 'SQL+PROCEDURES+BLOCKS+PLAN+REPLACE' );