displaying a particular users sql commands

[Former Member]

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.

[Breck_Carter]

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 );

[former_SQLA_member1694878]

SQL Anywhere can be configured to remember the most recently prepared statement using the -zl switch or the following statement:

CALL sa_server_option( 'RememberLastStatement', 'YES' );

CALL sa_conn_info() can be used to identify the connection by IP address. With the connection number and the RememberLastStatement server setting is turned on, you could use CALL sa_conn_activity() to identify the statement.

There are some limitations to this method. The output of sa_conn_activity can be misleading in the presence of multiple prepared statements on the connection, or statement caching.

If you need more detailed information, you can turn on request logging or database tracing on a per-connection basis.