Combining reactive and predictive governing

You can limit resource usage for certain SQL statements reactively during their execution, and you detect certain SQL statements that are likely to use too many resources predictively before they execute. Start of changePredictive governing applies only to dynamic SQL statements.End of change

Example

Resource limits apply only to the following types of SQL statements:

  • SELECT
  • INSERT
  • UPDATE
  • MERGE
  • TRUNCATE
  • DELETE

You can specify resource limits for dynamic SQL statements and static SQL statements. Resource limits apply to SQL statement regardless of whether they are issued locally or remotely. The resource limit facility does not apply to primary or secondary authorization IDs that have installation SYSADM or installation SYSOPR authority.

To use both reactive and predictive resource limits in combination, the resource limit table must contain at least two separate rows, as shown in the following table. If the processing cost estimate is in cost category B and you decide to run the statement, you can use a resource limit table to terminate the statement after a certain amount of processor time.

Table 1. Combining reactive and predictive governing
RLFFUNC AUTHID RLFPKG ASUTIME RLFASUWARN RLFASUERR RLF_CATEGORY_B
7 USER1 PKG2 0 800 1000 W
2 USER1 PKG2 1100 0 0 (blank)

The rows in the RLST resource limit table for this example cause Db2 to act as follows for a dynamic SQL statement that runs under the package named PKG2:

Predictive mode
  • If the statement is in COST_CATEGORY A and the cost estimate is greater than 1000 SUs, USER1 receives SQLCODE -495 and the statement is not executed.
  • If the statement is in COST_CATEGORY A and the cost estimate is greater than 800 SUs but less than 1000 SUs, USER1 receives SQLCODE +495.
  • If the statement is in COST_CATEGORY B, USER1 receives SQLCODE +495.
Reactive mode
In either of the following cases, a statement is limited to 1100 SUs:
  • The cost estimate for a statement in COST_CATEGORY A is less than 800 SUs
  • The cost estimate for a COST_CATEGORY A is greater than 800 and less than 1000 or is in COST_CATEGORY B and the user chooses to execute the statement