|
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:
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?
|