Foxhound 2.0 FAQ
Foxhound 2.0 FAQ Home      Foxhound Home      RisingRoad           Breck.Carter@gmail.com     


Question: How do I get something to show up in the Last Statement column?

Answer: When starting a SQL Anywhere Version 8 or later target database, turn on the capturing of the most recently-prepared SQL statement for each connection:

  • Specify the -zl server command line option when starting the engine, or
  • call sa_server_option() while the database is running:

    CALL sa_server_option ( 'Remember_last_statement', 'ON'  );  -- Version 8
    CALL sa_server_option ( 'RememberLastStatement',   'ON'  );  -- Version 9
    CALL sa_server_option ( 'RememberLastStatement',   'YES' );  -- Version 10
    CALL sa_server_option ( 'RememberLastStatement',   'YES' );  -- Version 11
    CALL sa_server_option ( 'RememberLastStatement',   'YES' );  -- Version 12
If that doesn't work, try this (see the explanation below):
SET TEMPORARY OPTION MAX_CLIENT_STATEMENTS_CACHED = '0';
or this:
SET OPTION PUBLIC.MAX_CLIENT_STATEMENTS_CACHED = '0';

The Last Statement column may be empty after ISQL displays the result set from a SELECT statement, even if 'RememberLastStatement' is turned on.


Another reason the Last Statement column may be empty is that SQL Anywhere is reusing a previously-prepared SQL statement. This may happen even if the application code is using dynamic SQL; here is an example:

A PowerBuilder 10.5 application repeatedly executes the following embedded SQL statement written in PowerScript, using an ODBC connection to a SQL Anywhere 10.0.1 database:

UPDATE inventory
   SET item_count = item_count + 1
 WHERE item_id = :ll_pkey
 USING itr_sql;

Each execution provides a different value for the ll_pkey host variable (420001, 680001, 350001, ...) but otherwise the SQL remains the same. Nothing shows up in the LastStatement connection property even though dbsrv10 -zl (capture most recently-prepared SQL statement) is specified.

Request-level logging shows that the statement is prepared twice, but after the second PREPARE it starts doing CACHED_DROP_STMT and VALIDATE_STMT operations... no more DROP_STMT or PREPARE operations. This has the side-effect of causing the original SQL statement to no longer show up in the LastStatement connection property.

=,<,21,PREPARE,update inventory SET item_count =item_count + 1 WHERE item_id =? 
+1,>,21,PREPARE,65548
=,<,22,COMMIT
=,<,21,EXEC,65548
=,H,21,0,int,420001  
=,>,21,EXEC
+1,<,21,COMMIT
=,>.,21
+1,<,21,DROP_STMT,65548
=,>,21,DROP_STMT

=,<,21,PREPARE,update inventory SET item_count =item_count + 1 WHERE item_id =? 
=,>,21,PREPARE,65549
=,<,21,EXEC,65549
=,H,21,0,int,680001  
=,>,21,EXEC
=,<,21,COMMIT
=,>.,21
=,<,21,CACHED_DROP_STMT,65549

=,>,21
=,<,21,VALIDATE_STMT,65549
=,>,21
=,<,21,EXEC,65549
=,H,21,0,int,350001  
=,>,21,EXEC
=,<,21,COMMIT
+1,>.,21
=,<,21,CACHED_DROP_STMT,65549

=,>,21
=,<,21,VALIDATE_STMT,65549
=,>,21
=,<,21,EXEC,65549
+1,H,21,0,int,10001  
=,>,21,EXEC
=,<,21,COMMIT
+1,>.,21
=,<,21,CACHED_DROP_STMT,65549

... and so on.

This optimization isn't done by PowerBuilder, it's done by SQL Anywhere itself. It is a new feature in SQL Anywhere Version 10, called client statement caching, and it is done by these client interfaces: ODBC, OLE DB, ADO.NET, embedded SQL and the iAnywhere JDBC driver. It is not done for Open Client, jConnect, or HTTP connections.

A really crude workaround is to use PowerBuilder's own EXECUTE IMMEDIATE feature to stop this optimization, which in turn will force the SQL to show up in the LastStatement connection property.

A better workaround is to just turn off the client statement caching feature:

   SET TEMPORARY OPTION MAX_CLIENT_STATEMENTS_CACHED = '0'; -- 0 to 100, default 10

These workarounds have a dark side: the performance penalty that results from turning off the optimization. In the long run, it may be better to live without seeing Last Statement.

See also...
How do I get something to show up in the Total Waits, Waiting Time columns?


This page was last updated on January 17, 2012. Foxhound 2.0 FAQ Home      Foxhound Home      RisingRoad      Breck.Carter@gmail.com