Sometimes a user will call for assistance with our pb12/sqla12 application. I will remote into the server and at some point might want to display all sql being processed by the sqla engine for this user's instance of our application. What statements should I execute in isql to determine this particular user's connection id, and then what statements will display only the sql being processed for this particular user? As background, I can ask the user to, for instance, run ipconfig, and possibly other utilities, if needed, to have the information necessary to find the user's connection among, what may be many connections. Thank you.
asked 09 Mar '14, 23:28
SQL Anywhere can be configured to remember the most recently prepared statement using the -zl switch or the following statement:
There are some limitations to this method. The output of
answered 10 Mar '14, 10:04
The LastStatement property (enabled by RememberLastStatement) might help if the problem is performance related; e.g., the connection is executing one statement for a long time.
If you are bug-hunting, however, you probably want a trace of all the activity for a single connection. Mikel mentioned request logging, and that is probably what you want. You can filter the output on connection number; see the description of CALL sa_server_option( 'RequestFilterConn' , connection-id ); on this page in the 12.0.1 docs.
Do some reading about request level logging; it is both funky and powerful, where "funky" is Canadian for "almost unreadable", and "powerful" means "you can find what you want if you work hard enough". There are builtin procedures sa_get_request_* and tables satmp_request_* that may be helpful.
-- dbo.satmp_request_time (table_id 642) in ddd12 - Mar 10 2014 1:37:19PM - Print - Foxhound © 2014 RisingRoad CREATE GLOBAL TEMPORARY TABLE dbo.satmp_request_time ( req_id /* PK */ INTEGER NOT NULL, conn_id UNSIGNED INT NULL, conn_handle UNSIGNED INT NULL, stmt_num INTEGER NULL, millisecs INTEGER NOT NULL, stmt_id /* X */ INTEGER NULL, stmt LONG VARCHAR NOT NULL, prefix LONG VARCHAR NULL, isolation_level INTEGER NULL, rowcount UNSIGNED INT NULL, triggers UNSIGNED INT NULL, proc_name CHAR ( 128 ) NULL, proc_line INTEGER NULL, cursor_type LONG VARCHAR NULL, plan LONG VARCHAR NULL, start_time TIMESTAMP NULL, CONSTRAINT ASA5 PRIMARY KEY ( req_id ) ) NOT TRANSACTIONAL; -- Parents of dbo.satmp_request_time -- none -- -- Children -- none -- CREATE INDEX stmt_idx ON dbo.satmp_request_time ( stmt_id );