Limiting resources for SQL statements predictively
You can use the resource limit facility for predictive governing to avoid wasting processing resources by giving you the ability to prevent a SQL statement from running when it appears likely to exceed processing limits. In reactive governing, those resources are already used before the query is stopped.
About this task
The following figure provides an overview of how predictive governing works.

Resource limits apply only to the following types of SQL statements:
- SELECT
- INSERT
- UPDATE
- MERGE
- TRUNCATE
- DELETE
At prepare time for a dynamic SQL statement, DB2® searches the active resource limit table to determine if the processor cost estimate exceeds the error or warning threshold that you set in the RLFASUWARN and RLFASUERR columns of the resource limit table. DB2 compares the cost estimate for a statement to the thresholds that you set, and the following actions occur:
- If the cost estimate is in cost category A and the error threshold is exceeded, DB2 returns a -495 SQLCODE to the application, and the statement is not prepared or run.
- If the estimate is in cost category A and the warning threshold is exceeded, a +495 SQLCODE is returned at prepare time. The prepare is completed, and the application or user decides whether to run the statement.
If the estimate is in cost category B, DB2 takes the action that you specify in the RLF_CATEGORY_B column; that is, it either prepares and executes the statement, does not prepare or execute the statement, or returns a warning SQLCODE, which lets the application decide what to do.
If the estimate is in cost category B and the value in the RLF_CATEGORY_B column is 'W', a +495 SQLCODE is returned at prepare time. The prepare is completed, and the application or user decides whether to run the statement.
If SQLCODE +495 is returned to a down-level DRDA requester, OPEN processing continues but the first block of data is not returned with the OPEN. Thus, if your application does not continue with the query, you have already incurred the performance cost of OPEN processing.
For enabled requesters, if your application does not defer the prepare, SQLCODE +495 is returned to the requester and OPEN processing does not occur.
If your application does defer prepare processing, the application receives the +495 at its usual time (OPEN or PREPARE). If you have parameter markers with deferred prepare, you receive the +495 at OPEN time as you normally do. However, an additional message is exchanged.
Procedure
To specify predictive governing:
- '7'
- Govern by package name (RLST)
- '9'
- Govern by client information (RLMT)
Example
The following table is an RLST with two rows that use predictive governing.
RLFFUNC | AUTHID | RLFCOLLN | RLFPKG | RLFASUWARN | RLFASUERR | RLF_CATEGORY_B |
---|---|---|---|---|---|---|
7 | (blank) | COLL1 | C1PKG1 | 900 | 1500 | Y |
7 | (blank) | COLL2 | C2PKG1 | 900 | 1500 | W |
Statements in cost category A that are predicted to be less than or equal to 900 SUs are executed.
Statements in cost category A that are predicted to be greater than 900 and less than or equal to 1500 SUs receive a +495 SQLCODE.
- Statements in cost category A that are predicted to be greater than 1500 SUs receive SQLCODE -495, and the statement is not executed.