Overrides a database server command line option while the database server is running.
sa_server_option ( option_name, option_value )
DBA authority required.
“sa_get_request_profile system procedure,” “sa_get_request_times system procedure,” and “sa_statement_text system procedure” in Adaptive Server Anywhere SQL Reference.
Database administrators can use this procedure to override some database server options without restarting the database server.
The following options can be reset:
Option name |
Values |
Default |
|---|---|---|
Disable_connections |
ON or OFF |
OFF |
Liveness_timeout |
Integer, in seconds |
120 |
Procedure_profiling |
ON, OFF, RESET, CLEAR |
OFF |
Profile_filter_conn |
connection-id |
|
Profile_filter_user |
user-id |
|
Quitting_time |
Valid date and time |
|
Remember_last_statement |
ON or OFF |
OFF |
Request_level_log_file |
Filename |
|
Request_level_log_size |
File-size, in bytes, |
|
Request_level_logging |
ALL, SQL, NONE, SQL+hostvars |
NONE |
Requests_for_connection |
connection-id, -1 |
|
Requests_for_database |
database-id, -1 |
disable_connections When set to ON, no other connections are allowed to any databases on the database server.
liveness_timeout 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 liveness_timeout period without detecting a liveness packet, the communication is severed.
For more information, see -tl command line option in “Server command-line switches” on page 8 in Chapter 1, “Running the Database Server” in the Sybase IQ Utility Guide.
procedure_profiling 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, as well as how long each line takes to execute. You can also set procedure profiling options on the Database property sheet in Sybase Central. Collected information appears on the Profile tab in the right pane of Sybase Central.
ON enables procedure profiling for the database you are currently connected to.
OFF disables procedure profiling and leaves the profiling data available for viewing.
RESET returns the profiling counters to zero, without changing the ON or OFF 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 stored procedures to retrieve profiling information from the database. For more information about these procedures, see the Adaptive Server Anywhere SQL Reference.
For more information about viewing procedure profiling information in Sybase Central, see “Profiling database procedures” in the Sybase IQ Performance and Tuning Guide.
profile_filter_conn Instructs the database server to capture profiling information for a specific connection ID.
profile_filter_user Instructs the database server to capture profiling information for a specific user ID.
quitting_time Instructs the database server to shut down at the specified time.
For more information, see the -tq server option in Chapter 1, “Running the Database Server” in the Sybase IQ Utility Guide.
remember_last_statement Instructs the database server to capture the most recently prepared SQL statement for each connection to databases 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 remember_last_statement setting using the RememberLastStatement property function as follows:
SELECT property( 'RememberLastStatement' )
For more information, see -zl server option in Chapter 1, “Running the Database Server” in the Sybase IQ Utility Guide.
When remember_last_statement is turned on, the following statement returns the most recently prepared statement for the specified connection.
SELECT connection_property( 'LastStatement', conn_id )
The stored procedure sa_conn_activity returns this same information for all connections.
request_level_log_file The name of the file used to record logging information. A name of NULL stops logging to file. Any backslash characters in the file name must be doubled, as this is a SQL string.
request_level_log_size The maximum size of the file used to record logging information, in bytes.
When the request-level 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-level log file is then restarted.
request_level_logging Can be ALL, SQL, NONE, or SQL+hostvars. ON and ALL are equivalent. OFF and NONE are equivalent. 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. The settings request_level_debugging and request_level_logging are equivalent.
When you set request_level_logging to OFF, the request-level log file is closed.
If you select SQL, only the following types of request are recorded:
START DATABASE
STOP ENGINE
STOP DATABASE
Statement preparation
Statement execution
EXECUTE IMMEDIATE statements
Option settings
COMMIT statements
ROLLBACK statements
PREPARE TO COMMIT operations
Connections
Disconnections
Beginnings of transactions
DROP STATEMENT statement
Cursor explanations
Cursor closings
Cursor resume
Errors
Setting request_level_logging to SQL+hostvars
outputs both SQL (as though you specified request_level_logging=SQL) and host
variable values to the log.
You can find the current value of the request_level_logging
setting using property('RequestLogging').
For more information, see the -z, -zr, -zs, -zo, and -o command line options in Chapter 1, “Running the Database Server” in the Sybase IQ Utility Guide. See “-zr level” on page 29 in the Sybase IQ Utility Guide for a list of requests that are logged by SQL request-level logging.
requests_for_connection Filter the request-level logging information so that only information for a particular connection is logged. This can help reduce the size of the request-level log file when monitoring a 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( 'requests_for_connection', 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( 'requests_for_connection', -1)
requests_for_database Filter the request-level logging information so that only information for a particular database is logged. This can help reduce the size of the request-level log file when monitoring a server with many active connections or 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( 'requests_for_database', 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( 'requests_for_database', -1 )
The following statement disallows new connections to the database server.
call sa_server_option( 'disable_connections', 'ON')
