sa_server_option system procedure

Function

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

Syntax

sa_server_option ( option_name, option_value )

Permissions

DBA authority required. Users without DBA authority must be granted EXECUTE permission to run the stored procedure.

Description

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

You can reset these options:

Option name	Values	Default
Disable_connections	ON or OFF	OFF
IQMsgMaxSize	Integer 0 – 2047 (inclusive) in megabytes	0
IQMsgNumFiles	Integer 0 – 64 (inclusive)	0
Liveness_timeout	Integer, in seconds	120
Main_Cache_Cemory_MB	1 – 4294967295 (2³² -1)	32MB
Temp_Cache_Memory_MB	1 – 4294967295 (2³² -1)	24MB
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.

IQMsgMaxSize Limits the maximum size of the message log. IQMsgMaxSize is an integer 0-2047 (inclusive), in megabytes. The default value is 0, which specifies that there is no limit on message log size. IQMsgMaxSize corresponds to the -iqmsgsz server switch and takes precedence over the value of -iqmsgsz.

A IQMsgMaxSize value n greater than 0 means that the message log can grow up to n megabytes in size. For example, the following statement limits the size of the message log to 50MB:

CALL sa_server_option('IQMsgMaxSize','50')

A -iqmsgsz value n greater than 0 means that the message log can grow up to n megabytes in size. For example, the following command limits the size of the message log to 100MB:

start_iq -n iqdemo iqdemo.db ... <other options> ... -iqmsgsz 100

For information on the -iqmsgsz server startup switch, see “Starting the database server” in Chapter 1, “Running the Database Server” of the Utility Guide.

For information on message log management, see “Message logging” in Chapter 1, “Overview of Sybase IQ System Administration” of the System Administration Guide: Volume 1.

IQMsgNumFiles Specifies the number of archives of the old message log maintained by the server. The value of IQMsgNumFiles takes effect only if the IQMsgMaxSize server property or the -iqmsgsz server startup switch is non-zero. IQMsgNumFiles corresponds to the -iqmsgnum server switch and takes precedence over the value of -iqmsgnum.

IQMsgNumFiles is an integer 0-64 (inclusive). The default value is 0, which means that messages are wrapped in the main message log file.

A IQMsgNumFiles value n greater than 0 means that the server maintains n message log archives. For example, the following statement specifies that the server maintain 5 archives of the message log:

CALL sa_server_option('IQMsgNumFiles','5')

For information on the -iqmsgnum server startup switch, see “Starting the database server” in Chapter 1, “Running the Database Server” of the Utility Guide.

For information on message log management, see “Message logging” in Chapter 1, “Overview of Sybase IQ System Administration” of the System Administration Guide: Volume 1.

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 information on the -tl command line option, see “Limiting inactive connections” in Chapter 2, “Running Sybase IQ” in System Administration Guide: Volume 1.

main_cache_memory_mb Changes the default of the main shared memory buffer cache dynamically. This option can be set on a running server but cannot change cache size on a running database. If two databases need to run with different cache sizes, set the option before starting each database. If the cache size is set using the -iqmc server startup switch, Sybase IQ uses the specified value for all databases started on that server unless sa_server_option specifies a new value. For more information, see “Buffer caches and physical memory” in Chapter 4, “Managing System Resources,” in the Performance and Tuning 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 SQL Anywhere Server – SQL Reference.

For more information about viewing procedure profiling information in Sybase Central, see “Profiling database procedures” in the 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 on quitting_time, see the -tq server option in Chapter 1, “Running the Database Server” in the 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 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 The logging options can be ALL, SQL, NONE, HOSTVARS, PLAN, PROCEDURES, and TRIGGERS, separated by “+”. 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.

SQL includes basic SQL statement related requests. ALL includes SQL requests, plus other requests, which can significantly increase the size of the log. ALL also enables recording of host variable values. If TRIGGERS is specified, all stored procedure statements (including those in triggers) are recorded in the request log.

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 Utility Guide.

See “-zr level” on page 30 in the Utility Guide for a list of requests that are logged by SQL request-level logging. See “Logging server requests” in Chapter 14, “Troubleshooting Hints” of System Administration Guide: Volume 1 for more information on using request logging. See also “Request logging” in SQL Anywhere Server – SQL Usage > Monitoring and Improving Database Performance > Improving database performance > Other diagnostic tools and techniques.

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 )

temp_cache_memory_mb Changes the default size of the temporary shared memory buffer cache dynamically. This option can be set on a running server but cannot change cache size on a running database. If two databases need to run with different cache sizes, set the option before starting each database. If the cache size is set using the -iqtc server startup switch, Sybase IQ uses the specified value for all databases started on that server unless sa_server_option specifies a new value. For more information, see “Buffer caches and physical memory” in Chapter 4, “Managing System Resources,” in the Performance and Tuning Guide.

Examples

The following statement disallows new connections to the database server.

call sa_server_option( 'disable_connections', 'ON')

The following statement changes the size of the main shared memory buffer cache:

call sa_server_option( 'main_cache_memory_mb', '200')

You must restart the database for the new size to take effect.