SQLROWSREAD threshold

The SQLROWSREAD threshold specifies the maximum number of rows that a DML activity may read on any member. Use this threshold to detect and control activities that are reading an excessive number of rows.

Class
Activity
Definition domain
Database, service superclass, service subclass, work action, workload and statement
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 SQLROWSRETURNED threshold in that it controls the maximum number of rows read during query evaluation, 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 SQLROWSREAD 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.

Example

The following example creates an SQLROWSREAD threshold TH1 for the database domain with a member enforcement scope. This threshold stops the execution of any activity that reads more than 5 000 000 rows during query evaluation, which the threshold checks for at 10-second intervals. You can use this threshold to ensure that no queries on the system read an unreasonable number of rows, which can negatively impact other work running on the system. 

CREATE THRESHOLD TH1 FOR DATABASE ACTIVITIES
  ENFORCEMENT MEMBER
  WHEN SQLROWSREAD > 5000000 CHECKING EVERY 10 SECONDS
  STOP EXECUTION;