SQLROWSREADINSC threshold
The in-service-class SQLROWSREADINSC threshold specifies the maximum number of rows that a DML activity can read on a particular member while running in a specific service subclass. Use this threshold to detect and control activities that are reading an excessive number of rows.
- Class
- Activity
- Definition domain
- Service subclass
- Enforcement scope
- Member
- Tracked work
- See the information later in this topic
- Queuing
- No
- Unit
- Number of rows
- Predictive or reactive
- Reactive
This threshold differs from the SQLROWSREAD threshold in that it controls the number of rows read only from the time that an activity enters a specific service subclass, not the total number of rows read during the lifetime of the activity. This threshold also differs from the SQLROWSRETURNED threshold in that it controls the maximum number of rows read during query evaluation in the current service subclass, not the number of rows returned to a client application from the data server.
Index accesses are not counted toward the total number of rows read. If an access plan uses only indexes during query evaluation, the SQLROWSREADINSC threshold will not be violated.
This threshold is evaluated at user-configurable time intervals; if the interval is greater than the amount of time it takes to exceed the number of rows read, it is possible for the number of rows read for an activity on a member to exceed the threshold boundary, before the violation is detected.
Activities tracked by this threshold are as follows:
- Coordinator activities of type DML and corresponding subagent work such as subsection execution.
- Nested DML activities that are derived from user applications. However, DML activities that are issued by the data server, such as internal SQL statements, are unaffected by this threshold. IMPORT, EXPORT, and other CLP commands are considered to be user logic; therefore, activities that are invoked from within IMPORT, EXPORT, and other CLP commands are subject to thresholds.
You can use the REMAP ACTIVITY action to control activities by remapping them to a service subclass with different resource assignments.
Examples
The following example creates two service subclasses, A1 and A2, under a superclass A, with a single in-service-class SQLROWSREADINSC threshold that remaps activities between subclasses after 10 000 rows have been read in service subclass A1 during query evaluation. An event monitor record is logged.
CREATE SERVICE CLASS A;
CREATE SERVICE CLASS A1 UNDER A;
CREATE SERVICE CLASS A2 UNDER A;
CREATE THRESHOLD T1 FOR SERVICE CLASS A1 UNDER A
ACTIVITIES ENFORCEMENT MEMBER
WHEN SQLROWSREADINSC > 10000 REMAP ACTIVITY TO A2
LOG EVENT MONITOR RECORD;