In order to see the query being executed, last statement recording must be turned on

[Former Member]

Hi

I set up SQL Anywhere monitor and i get mails with long running queries. But they do not contain statement.

The database is started with the -zl option. How can i set this to be on on all connections?

A query has been running longer than the 10 s threshold. A query issued by connection 'SQL_DBC_1ca0cfc0' (ID 10) has been running for 47 s. In order to see the query being executed, last statement recording must be turned on. To turn on last statement recording, execute the following in DBISQL: CALL sa_server_option( 'RememberLastStatement', 'ON' ). Alternatively, last statement recording can be turned on using the -zl dbsrv12 server option.

The message: To solve this problem, try closing the connection that issued this query. Check the query to make sure that it is not unnecessarily costly (for example, using a JOIN without a WHERE clause).

A long running query may also be symptomatic of a database server under a heavy workload. Check that the database server is not currently performing cache- or CPU-intensive queries, and that the number of connections is within acceptable limits.

[VolkerBarth]

Is that a question or just a report?

The database is started with the -zl option. How can i set this to be on on all connections?

That is not necessary, it is set for all connections - cf. the command line's description:

Turns on capturing of the most recently-prepared SQL statement for each connection to databases on the server.

[Breck_Carter]

Here's what Foxhound says about Last Statement; some of the discussion may apply to the SQL Anywhere Monitor (which is a different product)...

The Last Statement: line displays the last SQL statement received 
from the client application on this connection, as of the point 
this sample was recorded:

...

Specify the -zl server command line option when starting the target database.

- or -

Call sa_server_option() on the target database:

CALL sa_server_option ( 'Remember_last_statement', 'ON'  );  -- Version 8
CALL sa_server_option ( 'RememberLastStatement',   'ON'  );  -- Version 9
CALL sa_server_option ( 'RememberLastStatement',   'YES' );  -- Versions 10, 11, 12, 16

If that doesn't work, try turning off client statement caching on 
the target database:

SET TEMPORARY OPTION MAX_CLIENT_STATEMENTS_CACHED = '0'; 

- or -

SET OPTION PUBLIC.MAX_CLIENT_STATEMENTS_CACHED = '0';

The Last Statement line may not appear for a variety of reasons, 
and when it does appear it may not contain the statement that is 
currently executing.

Here's the full story:

The CONNECTION_PROPERTY ( 'LastStatement' ) function call is used 
to obtain the value.

The SQL Anywhere documentation says that call "Returns the most recently 
prepared SQL statement for the current connection."

The Last Statement line will not appear when CONNECTION_PROPERTY ( 'LastStatement' ) 
returns an empty value, and that can happen

when the RememberLastStatement server option is not set to 'Yes' (see above),

when a cached statement is reused because the MAX_CLIENT_STATEMENTS_CACHED option 
is not set to '0' for the connection (see above), and

when a prepared statement has finished executing and it is dropped.

The Last Statement line may show a statement that is different from the one 
currently executing; that can happen

when a client application calls a stored procedure that contains a long-running 
query, thus causing the Last Statement line to show the CALL even though it's the 
query that is currently executing, and

when an application prepares multiple statements before executing any of them 
with the result that "the most recently prepared SQL statement" may not the 
one currently executing.

The Last Statement line may or may not apply to the same query as the Last 
Plan Text line. The Last Statement line shows what came from the client 
application, whereas Last Plan Text applies to the last query run by the 
server whether it came from the client or came from within a stored procedure.

[Former Member]

A question, since something must be missing since i did not see the query even when i added -zl. My guess is that this is because MAX_CLIENT_STATEMENTS_CACHED was set to 10. I set it to 0 and will see if it works.