For isolating some types of problems, especially problems with queries, logging server requests is helpful. You can enable request-level logging in two ways:
By setting the -zr command-line option when you start the server.
By calling the sa_server_option stored procedure, which overrides the current setting of the -zr command-line option.
Server requests are logged in the server log file *.srvlog. The -zr server startup option enables request-level logging of operations and sets the type of requests to log (ALL, NONE, or SQL). The -zo option redirects request-level logging information to a file separate from the regular log file and -zs limits the size of this file.
You can enable and disable request-level logging without restarting the IQ server using the sa_server_option stored procedure. The following commands enable request-level logging of a limited set of requests and redirect the output to the file sqllog.txt:
call sa_server_option('request_level_logging','SQL');
call sa_server_option('request_level_log_file',
'sqllog.txt');
The following command disables request-level logging:
call sa_server_option('request_level_log_file','');
To view the current settings for the SQL log file and logging level, execute the following statement:
select property('RequestLogFile'), property('RequestLogging');
To match .iqmsg log and the -zr SQL log entries using connection information, see “Correlating connection information”.
The following output shows the limited requests that are logged, when the server is started with the -zr SQL option. In this example, the user connects to the asiqdemo database, executes the command
SELECT * FROM customer
then disconnects. In the server log, each line is preceded by a datetime stamp, which has been removed from this sample output.
REQUEST conn: 1182240 CONNECT
DONE conn: 1182240 CONNECT Conn=1330880449
REQUEST conn: 1182240 STMT_EXECUTE_IMM "SET TEMPORARY OPTION CONNECTION_AUTHENTICATION='Company=MyComp;Application=DBTools;Signature=4f13d14f3'"
DONE conn: 1182240 STMT_EXECUTE_IMM
REQUEST conn: 1182240 STMT_PREPARE "SELECT user, db_property('IQStore') FROM SYS.DUMMY"
DONE conn: 1182240 STMT_PREPARE Stmt=65536
REQUEST conn: 1182240 STMT_EXECUTE Stmt=65536
DONE conn: 1182240 STMT_EXECUTE
REQUEST conn: 1182240 STMT_DROP Stmt=65536
DONE conn: 1182240 STMT_DROP
REQUEST conn: 1182240 STMT_PREPARE "SELECT default_collation FROM SYS.SYSINFO"
DONE conn: 1182240 STMT_PREPARE Stmt=65537
REQUEST conn: 1182240 STMT_EXECUTE Stmt=65537
DONE conn: 1182240 STMT_EXECUTE
REQUEST conn: 1182240 STMT_DROP Stmt=65537
DONE conn: 1182240 STMT_DROP
REQUEST conn: 1182240 STMT_PREPARE "SELECT connection_property('charset') FROM SYS.DUMMY"
DONE conn: 1182240 STMT_PREPARE Stmt=65538
REQUEST conn: 1182240 STMT_EXECUTE Stmt=65538
DONE conn: 1182240 STMT_EXECUTE
REQUEST conn: 1182240 STMT_DROP Stmt=65538
DONE conn: 1182240 STMT_DROP
REQUEST conn: 1182240 STMT_PREPARE "select @@servername"
DONE conn: 1182240 STMT_PREPARE Stmt=65539
REQUEST conn: 1182240 STMT_EXECUTE Stmt=65539
DONE conn: 1182240 STMT_EXECUTE
REQUEST conn: 1182240 STMT_DROP Stmt=65539
DONE conn: 1182240 STMT_DROP
REQUEST conn: 1182240 STMT_PREPARE "SELECT CURRENT DATABASE FROM DUMMY"
DONE conn: 1182240 STMT_PREPARE Stmt=65540
REQUEST conn: 1182240 CURSOR_OPEN Stmt=65540
DONE conn: 1182240 CURSOR_OPEN Crsr=65541
REQUEST conn: 1182240 CURSOR_CLOSE Crsr=65541
DONE conn: 1182240 CURSOR_CLOSE
REQUEST conn: 1182240 STMT_DROP Stmt=65540
DONE conn: 1182240 STMT_DROP
REQUEST conn: 1182240 STMT_PREPARE "select * from customer"
DONE conn: 1182240 STMT_PREPARE Stmt=65542
REQUEST conn: 1182240 CURSOR_OPEN Stmt=65542
DONE conn: 1182240 CURSOR_OPEN Crsr=65543
REQUEST conn: 1182240 CURSOR_EXPLAIN Crsr=65543
DONE conn: 1182240 CURSOR_EXPLAIN
REQUEST conn: 1182240 CURSOR_CLOSE Crsr=65543
DONE conn: 1182240 CURSOR_CLOSE
REQUEST conn: 1182240 STMT_DROP Stmt=65542
DONE conn: 1182240 STMT_DROP
REQUEST conn: 1182240 DISCONNECT
DONE conn: 1182240 DISCONNECT
For more information on request-level logging, see the section “Server command-line options” in Chapter 1, “Running the Database Server” of the Sybase IQ Utility Guide and “sa_server_option system procedure” in Chapter 9, “System Procedures”of the Sybase IQ Reference Manual.
