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. Start of changePredictive governing applies only to dynamic SQL statements.End of change

Start of change

Before you begin

  • Set the RLFENABLE subsystem parameter to an appropriate value for the types of SQL statements that you want to limit.

  • Modify your applications to check for the +495 and -495 SQLCODEs that predictive governing might generate after a PREPARE statement executes.

    If your requester uses deferred prepare, the presence of parameter markers determines when the application receives the +495 SQLCODE. When parameter markers are present, Db2 cannot do PREPARE, OPEN, and FETCH processing in one message. If SQLCODE +495 is returned, no OPEN or FETCH processing occurs until your application requests it.

    • If there are parameter markers, the +495 is returned on the OPEN (not the PREPARE).
    • If there are no parameter markers, the +495 is returned on the PREPARE.

    Normally with deferred prepare, the PREPARE, OPEN, and first FETCH of the data are returned to the requester. For a predictive governor warning of +495, you would ideally like to have the option to choose beforehand whether you want the OPEN and FETCH of the data to occur. For down-level requesters, you do not have this option.

End of change

About this task

Start of changePredictive governing applies only to dynamic SQL statements.End of change

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.
  • 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.

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):
  • 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.