Changes in monitoring tables

In the monitoring tables monSysStatement, monSysPlanText, and monSysSQLText, the values of the columns BatchID, ContextID, ProcedureID, and PlanID have been modified in Adaptive Server versions 15.0.3 and later, and two new columns have been added to monSysStatement to enhance analyzing the procedure execution.

BatchID – values are now consistent across all SQL statements within the batch. In previous versions, when a statement that used deferred compilation or execute immediate occurred within a batch or stored procedure, the value of the BatchID was increased.
ContextID – now displays a unique identifier for each execution context within a batch. An execution context is the unique identifier given to a stored procedure or trigger each time it is executed in a batch. This number is zero-based, and is reset with each batch executed by the client application.

For example, if a stored procedure is called multiple times in a batch, each time it is executed it gets a new contextID. All the rows for statements within the stored procedure during this execution, which are reported in the monSysStatement table, are given the same contextID value.
monSysSQLText – duplicate entries are no longer created in the monSysSQLText table for queries that execute with deferred compilation or execute immediate. The DBID, ProcedureID, and PlanID columns are no longer presented as 0; these columns’populations are now set correctly.
MonSysPlanText – The DBID, ProcedureID, and PlanID columns are no longer presented as 0 for ad hoc queries or queries that execute with deferred compilation or execute immediate; these columns are now set correctly.
monSysStatement has two new columns, ProcNestLevel and StatementNumber.
- ProcNestLevel – indicates the procedure nesting level at which a statement executes. This column is zero-based.
- StatementNumber – provides a sequencer number for the statements within a batch, in the order in which they are executed. Since rows are inserted into monSysStatement when they complete, execute statements appear after the rows for the stored procedures they invoked. Use the StatementNumber column to order rows in monSysStatement to display statements in the order in which they were executed.