Foxhound is a database monitor and schema troubleshooter for SQL Anywhere. (more...)
Contact Breck.Carter@gmail.com to participate in the Foxhound Beta Test program.Download Foxhound Version 1 Beta - a registration key will be required
Online Documentation for Foxhound Version 1 - also included in the download
Today's Foxhound FAQ: Why is LastStatement empty?
One reason the LastStatement connection property 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 does 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's probably better to live without seeing LastStatement.
This page was last updated on December 13, 2007.