ASA SQL Reference
  System Procedures and Functions
    System and catalog stored procedures

sa_server_option system procedure

Function 

Overrides a server option while the server is running.

Syntax 

sa_server_option ( option_name, option_value )

Permissions 

DBA authority required

Side effects 

None

Description 

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

The options that can be reset are as follows:

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

procedure_profiling    Controls procedure profiling for stored procedures, functions, events, and triggers. The profiling commands are also available in the Database property sheet in 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 procedure profiling, see Profiling database procedures.

quitting_time    Instruct the database server to shut down at the specified time.

For more information, see -tq time server option.

remember_last_statement    Instruct 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 LastStatement property function as follows:

select connection_property( 'LastStatement' )

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

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 filename must be doubled, as this is a SQL string.

For more information, see -zo server option.

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 -zr server option, and Server-level properties.

Example 

The following statement disallows new connections to the database server.

CALL sa_server_option( 'disable_connections', 'ON')