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.

Figure 1. Processing for predictive governing
Begin figure description. A conditional flow chart that shows how DB2 uses predictive governing. This concept is described in the following paragraph. End figure description.

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.
  • Start of changeIf 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.End of change
  • Start of changeIf 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.End of change

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.

Important: Do not use deferred prepare for applications that use parameter markers and that are predictively governed at the server side.

Procedure

To specify predictive governing:

Specify any of the following values in the RLFFUNC column of a resource limit table:
'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.

Table 1. Predictive governing example
RLFFUNC AUTHID RLFCOLLN RLFPKG RLFASUWARN RLFASUERR RLF_CATEGORY_B
7 (blank) COLL1 C1PKG1 900 1500 Y
7 (blank) COLL2 C2PKG1 900 1500 W
The rows in the resource limit table for this example cause DB2 to act as follows for all dynamic SELECT, INSERT, UPDATE, MERGE, TRUNCATE, or DELETE statements in the packages that are listed in this table (C1PKG1 and C2PKG1):
  • Start of changeStatements in cost category A that are predicted to be less than or equal to 900 SUs are executed. End of change
  • Start of changeStatements in cost category A that are predicted to be greater than 900 and less than or equal to 1500 SUs receive a +495 SQLCODE.End of change
  • Statements in cost category A that are predicted to be greater than 1500 SUs receive SQLCODE -495, and the statement is not executed.