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 )

Arguments 

• option_name    This char(128) parameter specifies a server option name.

• option_value    This char(128) parameter specifies the new value for the server option.

Remarks 

Database administrators can use this procedure to override some database server options 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 if one exists.

The options that can be reset are as follows:

ConnsDisabled    

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

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.

For more information, see -tl server option.

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.

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

ProfileFilterConn    

Instruct the database server to capture profiling information for a specific connection ID.

ProfileFilterUser    

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

QuittingTime    

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

For more information, see -tq server option.

RememberLastStatement    

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 RememberLastStatement setting using the RememberLastStatement property function as follows:

SELECT property( 'RememberLastStatement' )

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

The stored procedure sa_conn_activity 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 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 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( '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 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.

RequestLogging    

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.

When you set RequestLogging to OFF, the request 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 RequestLogging to SQL+hostvars outputs both SQL (as though you specified ('RequestLogging', 'SQL')) and host variable values to the log.

You can find the current value of the RequestLogging setting using property('RequestLogging').

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

RequestLogMaxSize    

The maximum size of the file used to record logging information, in bytes.

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.

For more information, 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.

For more information, see -zn server option.

Permissions 

DBA authority required

Side effects 

None

Example 

The following statement disallows new connections to the database server.

CALL sa_server_option( 'ConnsDisabled', 'ON')

SQL Anywhere Studio 9.0.2
Copyright © 1989–2004 Sybase, Inc. Portions copyright © 2001–2004 iAnywhere Solutions, Inc. All rights reserved.