Combining reactive and predictive governing

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

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

Resource limits apply only to dynamic SQL statements. Resource limits apply to SQL statement regardless of whether they are issued locally or remotely. The resource limit facility does not control static SQL statements regardless of whether they are issued locally or remotely, and no limits 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