Can I get a list of prepared statements for a connection?

[reimer_pods]

Whe currently have a case where a Java program exceeds the limit set by the max_statement_count option. We'd like to find out which statements are currently in use by the single connection the program uses.

Is there any way to get that information(tools, API, sa_... procedure, system table ...)?

[Former Member · Accepted solution]

Okay, I am assuming the JAVA application is one that you have the source to and are able to build. If the application is a third party app or one that you cannot build, then disregard the rest of this answer. I am also assuming that you are running on Windows. If so, then here is a brute force approach:

1) Temporarily change the application to use the iAnywhere JDBC driver not the SQL Anywhere JDBC driver. 2) Temporarily modify your application so that as soon as it detects the resource governor error, it immediately closes the single connection and exits. 3) Before starting your application, turn on ODBC tracing. 4) Run your application until it hits the resource governor error and exits. Note I realize this step could take a very long time (especially with ODBC tracing turned on).

Once the app exits, turn off ODBC tracing and have a look at the trace log. Look for the resource governor error. At the time the application issues the connection close, the iAnywhere JDBC driver will explicitly call SQLFreeStmt(SQL_CLOSE) for every statement that is still open. Hence, you should see a bunch of SQLFreeStmt calls following the resource governor error in the ODBC trace. You can then use the statement handle within each SQLFreeStmt(SQL_CLOSE) along with the portion of the ODBC trace prior to the resource governor error to figure out which statements are still open. If you used Connection.prepareStatement() then there should be a SQLPrepareW call in the ODBC trace with the particular statement handle; and if you used Connection.createStatement() then there should be a SQLExecDirectW call with the particular statement handle. For each SQLPrepareW and SQLExecDirectW call that you identify as belonging to a statement handle that was still open at the time the connection was closed, look at the "WCHAR" argument to see what the SQL statement associated with the statement is.

As I said before, the above is an extremely tedious and brute force method but given that you have received no other suggestions, this might be your only choice.

[reimer_pods · Accepted solution]

Many thanks, Karim. The culprit is one of our own applications, so we could modify it accordingly. I talked to one of the developers and he thinks they'll give it a try.

[Former Member]

Have you tried the application profiler? It should be able to give you the set of prepare requests executed by your application.

[reimer_pods]

Thanks for the suggestion. Unfortunately I don't see how I can isolate the statements that have not been closed out of hundreds or thousands that were prepared.

[reimer_pods]

Rethinking the case brought up another idea: I could try creating a request log and scan it for non-matching pairs of PREPARE and CLOSE (or DROP_STMT) entries. As this requires at least a little programming I think I put it on hold until we've tried the approach proposed by Kharim.

[Former Member]

I'm not aware of any JDBC method that can give you the information you need. Perhaps there is something in the server that can provide the information but I am not aware of anything like that; so I will leave it up to others to answer that part of your question.

I do wonder though if you really are hitting the statement count limit. Are you using jConnect or the SQL Anywhere/iAnywhere JDBC driver? If you are using the SA/iAnywhere JDBC driver, and your application does not explicitly close statements, then you may simply be hitting the statement count limit because the garbage collector has not kicked in yet. Check your application and make sure you are explicitly calling the close() method whenever you are done with a statement. Letting a statement handle go out of scope is fine but the lag on the GC could be quite long in that case. If you cannot verify whether or not statements are being explicitly closed, then try issuing a System.gc() call at regular intervals and see if that does the trick.

Otherwise, as I said, I am not sure there is a JDBC method that will get you the specific information you are looking for; but perhaps someone knows of an engine feature that you can use.

[reimer_pods]

The application is rather complex, so checking the source for unclosed statements would be a lengthy task. My intention was getting some hints where to start the search. If I could see which statements are open at the point where the resource governor jumps in it might give us a clue.
As I stated in my question, we'd appreciated any tool to get that information, not only JDBC methods.