Answer: Turn on the capturing of the most recently-prepared SQL statement for each connection:

• Specify the -zl server command line option, or

• call sa_server_option() to set RememberLastStatement to 'YES'.

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's may be better to live without seeing Last Statement.