The QMF Governor

The QMF Governor

Because QMF uses dynamic SQL, the RLF can be used to govern QMF resource use. To control the usage of QMF, a row would be inserted specifying the following:

• A blank AUTHID (so the limit applies to all users)

• The QMF plan name in the PLANNAME column (for example, for QMF V6 this is most likely QMF610 or something similar)

• The resource limit in ASUTIME

If necessary, multiple rows could be inserted with varying resource limits for different authids.

However, the QMF Governor can govern QMF use independently from DB2 and SQL use. The QMF Governor provides the capability to prompt users or to cancel threads based on excessive resource use. Resource use is either a CPU time limit or a limit based on the number of rows retrieved by a single query.

The operation of the QMF Governor is controlled by rows inserted into a QMF control table named Q.RESOURCE_TABLE . DDL to create this table is shown in the following SQL statement:

 CREATE TABLE Q.RESOURCE_TABLE (RESOURCE_GROUP     CHAR(16)  NOT NULL ,  RESOURCE_OPTION    CHAR(16)  NOT NULL ,  INTVAL             INTEGER,  FLOATVAL           FLOAT,  CHARVAL            VARCHAR(80) ) IN DSQDBCTL.DSQTSGOV ; 

Values inserted into the first three columns of this table control QMF resource governing . The IBM-supplied QMF Governor does not use the last two columns , FLOATVAL and CHARVAL .

The following list shows the values that can be supplied for the RESOURCE_OPTION column, indicating the types of QMF governing available:

SCOPE	Sets the overall QMF resource-governing environment. If a row has RESOURCE_OPTION set to SCOPE , and the row contains a value of in the INTVAL column, governing is enabled. Any other value disables the QMF Governor.
TIMEPROMPT	Sets the amount of CPU time that can be incurred before prompting users to cancel or continue. If INTVAL is , less than , or null, prompting does not occur.
TIMELIMIT	Sets the amount of CPU time that can be incurred before canceling. This is an unconditional cancellation, without a prompt. The INTVAL specified for TIMELIMIT should always be greater than the corresponding TIMEPROMPT value. If INTVAL is , less than , or null, cancellation does not occur.
TIMECHECK	Sets the amount of time that must elapse before performing CPU time checks as specified by TIMEPROMPT and TIMELIMIT . If INTVAL is , less than , or null, time checking does not occur, regardless of the TIMEPROMPT and TIMELIMIT settings.
ROWPROMPT	Sets the maximum number of rows that can be retrieved before prompting the user to cancel or continue. If INTVAL is , less than , or null, prompting does not occur.
ROWLIMIT	Sets the maximum number of rows that can be retrieved before canceling. This is an unconditional cancellation, without a prompt. The INTVAL specified for TIMELIMIT should always be greater than the corresponding TIMEPROMPT value. If INTVAL is , less than , or null, cancellation does not occur.

When the QMF Governor is set to prompt when reaching a particular threshold, the users are told the amount of CPU time consumed and the number of rows retrieved. This prompt looks like the following:

 DSQUE00 QMF governor prompt: Command has run for  nnnnnn  seconds of CPU times and fetched  mmmmmm  rows of data. ==> To continue QMF command press the "ENTER" key. ==> To cancel QMF command type "CANCEL" then press the "ENTER" key. ==> To turn off prompting type "NOPROMPT" then press the "ENTER" key. 

Users have the choice to continue or cancel their request. Users can request also that additional prompting be disabled. If the request is continued and prompting is not disabled, subsequent prompts are displayed as the limits are reached. Additionally, the QMF Governor might cancel a request if additional limits are met.

TUNING STRATEGY

Use the QMF Governor at least to prompt users when thresholds are bypassed. This enables users to police their own requests . At a minimum, also set a high systemwide cancellation time in case users choose the NOPROMPT option. You can set this with the QMF Governor or the RLF for the QMF plan.