Monitoring cursor statements

Adaptive Server uses the monCachedStatement table to monitor cursors. The StmtType column in monCachedStatement indicates the types of queries in the statement cache. The values for StmtType are:

1 – batch statement
2 – cursor statement
3 – dynamic statement

You must set the enable functionality group configuration parameter to 1 to monitor cursor statements.

This example shows the contents of monCachedStatement (including the SSQLID for new_cursor), and then uses the show_cached_text function to show the SQL text for new_cursor:

select InstanceID, SSQLID, Hashkey, UseCount, StmtType
from monCachedStatement

InstanceID   SSQLID       Hashkey       UseCount      StmtType ----------  -----------  -----------   -----------  ----------
          0    329111220   1108036110             0           2
          0    345111277   1663781964             1           1

select show_cached_text(329111220)
--------------------------------------
select id from sysroles

See Chapter 3, “Monitoring Tables,” in the Reference Manual: Tables and the Performance and Tuning Series: Monitoring Tables.