Using the IBM-supplied governor exit routine

The governor exit routine supplied for CICS (DSQUEGV3) controls how many rows a user can retrieve from the database. The governor exit routine supplied for TSO, ISPF, and native OS/390 (DSQUEGV1) controls how many rows a user can retrieve from the database or the processor time used running a QMF command. The governor exit routine is shipped with two predefined values for the number of rows:

A row prompt value warns users when the number of rows retrieved reaches 25,000, at which time the user sees the message shown below.
Figure 180. Message displayed when a resource limit is approaching. The n symbol in the figure represents an NLID from Table 1
```
DSQUn00 QMF governor prompt: 
Command has fetched 25,000 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
```
Important: Database activity is not suspended when a cancellation prompt is displayed. DB2 continues to fetch rows and use processor time.
A row limit value cancels data retrieval when 100 ,000 rows have been retrieved, if the user presses the Enter key in response to the message in Figure 180. When the IBM-supplied governor cancels data retrieval, the user sees the message shown in below.
Figure 181. Message displayed when a resource limit is exceeded
```
Row limit exceeded!  Your command canceled by QMF governor.
```

When running a procedure, you might get a message that your procedure was canceled, rather than the message in Figure 181. For example, if your procedure contains a command that requires the report to complete (such as ERASE), you receive the message shown below.

Figure 182. Message displayed when a procedure is canceled

Procedure canceled.

Users using the SYSTEM profile are already set up to use these default values of 25, 000 and 100, 000.

TSO, ISPF, and native OS/390 have two additional predefined values (a time limit and a time prompt value) for the time spent running a QMF command:

A time prompt value warns users when the processor time for the cycle reaches six minutes, at which time the user sees the message shown below.
Figure 183. Message displayed when a resource limit is approaching (OS/390). The n symbol in the figure represents an NLID from Table 1
```
DSQUn00 QMF governor prompt:
   Command has executed for 6 minutes
 
==> 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
```
A time limit value cancels the command when 24 minutes of processor time are used during the cycle.

Activating the default limits

Follow this procedure to set up the governor exit routine to warn a user when the number of rows retrieved from the database reaches 25, 000 and to cancel the QMF activity when the number of rows retrieved reaches 100 ,000:

Run the query shown in Figure 184 from the SQL query panel.
Figure 184. Activating default values for the IBM-supplied governor
```
UPDATE Q.RESOURCE_VIEW
SET INTVAL=0
WHERE RESOURCE_OPTION='SCOPE' AND
      RESOURCE_GROUP='SYSTEM'
```
Set a value of SYSTEM for the RESOURCE__GROUP field of the user's profile. For example, the UPDATE statements in Figure 185 activate default values for user JONES (using English QMF) and user SCHMIDT (using German QMF).
Important: Always specify a value for the TRANSLATION column, or you might change more rows in Q.PROFILES than you intend.

Figure 185. Updating a user's resource group

Base QMF (English)

German NLF

UPDATE Q.PROFILES

UPDATE Q.PROFILES

SET RESOURCE__GROUP = 'SYSTEM'

SET RESOURCE__GROUP = 'SYSTEM'

WHERE CREATOR='JONES' AND

WHERE CREATOR='SCHMIDT' AND

TRANSLATION='ENGLISH'

TRANSLATION='DEUTSCH'

Important: If you start QMF with a DSQSPRID parameter value of TSOID, the resource group name is the user ID.
Instruct users to reconnect to the database to activate the new values. This can be done with a DB2 CONNECT command, or they can end their current QMF session and begin another to activate the new resource group.

If you want to define row limits other than the defaults of 25, 000 and 100, 000, read How a governor exit routine controls resources. Then see the procedure in Defining your own resource limits.

How a governor exit routine controls resources

The governor uses two types of information to control resources.

Information about the resource limits you set for a user, defined in a resource control table called Q.RESOURCE_TABLE.
Information about the state of the user's session, which tells the governor how close the user's activity is coming to the resource limits defined for the resource group the user is in. This information is passed to the governor exit routine in the IBM-supplied control blocks DXEGOVA and DXEXCBA.

How the governor knows what the resource limits are

Each row of the IBM-supplied Q.RESOURCE_TABLE contains:

The name of a resource group (RESOURCE_GROUP), which characterizes one or more users whose activities you want to govern in the same manner.
The name of the resource (RESOURCE_OPTION) you want to limit for the group of users named in RESOURCE_GROUP.
Values (INTVAL, FLOATVAL, or CHARVAL) that define the limit for the resource option. Resource options can have integer values, floating-point values, or character values.

Table 70 shows the structure of the Q.RESOURCE_TABLE as it is shipped by IBM. Q.RESOURCE_TABLE has the index Q.RESOURCE_INDEX. Keyed columns are RESOURCE_GROUP and RESOURCE_OPTION.

If you are migrating from an older QMF release: The older QMF releases do not include Q.RESOURCE_INDEX.

The Q.RESOURCE_TABLE is shipped by IBM with a predefined resource group called SYSTEM. The SYSTEM resource group has three predefined resource options for CICS. The group has additional time options for TSO, ISPF, or native OS/390 batch. Use the CHARVAL column to indicate the limits defined in each row, as shown.

Table 70. Default resource group and options for the IBM-supplied governor exit common to all
GROUP	OPTION	INTVAL	FLOATVAL	CHARVAL
SYSTEM	SCOPE	-	-	Indicate whether governor is active
SYSTEM	ROWLIMIT	100,000	-	Cancel after fetching 100,000 rows
SYSTEM	ROWPROMPT	25,000	-	Prompt user after fetching 25,000 rows

Table 71. Options for the IBM-supplied governor exit for TSO, ISPF, or native OS/390 batch
GROUP	OPTION	INTVAL	FLOATVAL	CHARVAL
SYSTEM	TIMELIMIT	1440	-	Cancel after 24 minutes CPU
SYSTEM	TIMEPROMPT	360	-	Prompt user after 6 minutes CPU
SYSTEM	TIMECHECK	900	-	15 minutes interval between time check

Table 72. Options for the IBM-supplied governor exit for CMS
GROUP	OPTION	INTVAL	FLOATVAL	CHARVAL
SYSTEM	TIMELIMIT	3600	-	Cancel after 60 minutes
SYSTEM	TIMEPROMPT	900	-	Prompt user after 15 minutes
SYSTEM	TIMECHECK	900	-	15 minutes interval between time check

SCOPE = 0: Activates governing for a particular resource group.
Any non-zero value for SCOPE, including a null, deactivates governing for the resource group.
ROWLIMIT = 100,000: If the user decides to continue when warned, the governor exit routine cancels data retrieval activities after 100, 000 rows are retrieved. (Retrieval is for FETCH only.) ROWLIMIT is dependent on the buffer size; therefore, more than 100, 000 rows can be retrieved if the buffer holds a number of rows not divisible by 100, 000.
ROWPROMPT = 25,000: Warns the user when 25, 000 database rows have been retrieved.

The three additional options provided in TSO, and native OS/390 batch are:

TIMELIMIT = 1440: If the user decides to continue when warned, the governor exit routine cancels the command after 24 minutes of processor time have elapsed. TIMELIMIT is checked at TIMECHECK intervals; therefore, more than 24 minutes of processor time can elapse if the TIMECHECK interval is set at an interval not divisible by 24. TIMELIMIT is evaluated after a TIMECHECK interval is processed.
Processor time: Processor time refers to the jobstep time plus the SBR (Service Request Block) time.
TIMEPROMPT = 360: Warns the user when 6 minutes of processor time have elapsed. Evaluated after a TIMECHECK interval is processed.
TIMECHECK = 900: Specifies 15 minutes of real time between time checks or prompting or canceling.

IBM also supplies a view of this table, called Q.RESOURCE_VIEW, that includes all five columns of Q.RESOURCE_TABLE. Each time QMF calls the governor exit routine, QMF passes to the routine the resource control information stored in Q.RESOURCE_VIEW. The governor exit routine uses this resource information to help determine when the user reaches a resource limit.

How the governor knows when you reach a resource limit

On a call to the governor exit routine, QMF queries Q.RESOURCE_VIEW, which shows what resource limits are defined in the resource control table for the resource group to which the user belongs. To determine the resource group, QMF checks the value of the RESOURCE_GROUP field of the user's row in the Q.PROFILES table and checks Q.RESOURCE_VIEW for a matching value.

QMF uses two control blocks, DXEGOVA and DXEXCBA, to pass information to the governor exit routine. The DXEGOVA control block contains information from Q.RESOURCE_VIEW about the limits you set for each user. The DXEXCBA control block contains information about the activities the user is performing in the current QMF session, which tells the governor how close the user is coming to the resource limits.

Figure 186 shows how the governor limits use of resources.

Figure 186. How a governor exit routine works with QMF for OS/390

QMF Session points to QMF interface control blocks via Function calls. QMF
interface control blocks point to Governor Exit routine. Governor Exit routine
points to Return to QMF."

QMF calls the governor exit routine at a number of different points within the QMF session. These calls are called function calls. For more information about function calls, see Points at which QMF calls the governor.

What happens when you reach a resource limit

When the resource control information QMF passes to the governor exit routine indicates that a resource limit has been reached, the IBM-supplied governor exit routine calls the QMF cancellation service to cancel the QMF activity the user tried to perform.

If you use the default limits for number of rows, the IBM-supplied governor exit routine also displays a warning before canceling the activity, as shown in Figure 181. See Defining your own resource limits for how to activate this warning if you are not using the default values for the number of rows retrieved.

The IBM-supplied governor exit routine resets its count of the number of rows upon returning control to QMF, so that the number of rows is not cumulative across calls to the governor.

Defining your own resource limits

This section explains how to create a new resource group, for which the resource is the number of rows retrieved from the database. If you want to define resource limits other than the number of rows, you need to modify the IBM-supplied governor exit routine or write an exit routine of your own. See Modifying the IBM-supplied governor exit routine or writing your own for more information on the facilities you can use.

Use the following procedure to add a resource group to the resource control table. This procedure adds a resource group named GROUP1, where the governor prompts a user in GROUP1 when the number of rows reaches 10, 000, and cancels the user's activity when the number of rows reaches 10,000; it cancels the user's activity when the number of rows reaches 15 ,000. For TSO and native OS/390 batch, the governor also prompts a user in GROUP1 when processor time reaches 300 seconds, and cancels the user's activity when the processor time reaches 1,000 seconds. The procedure also shows an example of how to add a user to a resource group.

Run the query in Figure 187 to set the number of rows at which the user is warned of the approaching resource limit.
If you do not want to warn users when they are approaching their limit for the number of rows, skip to step 2
Figure 187. Activating prompting for row limit
```
INSERT INTO Q.RESOURCE__VIEW (RESOURCE__GROUP,RESOURCE__OPTION,INTVAL)
    VALUES('GROUP1','ROWPROMPT',10000)
```

Run the query in Figure 188 to set the number of rows at which the governor cancels the user's activity.
Figure 188. Activating cancellation of activities when user reaches row limit
```
INSERT INTO Q.RESOURCE__VIEW (RESOURCE__GROUP,RESOURCE__OPTION,INTVAL)
    VALUES('GROUP1','ROWLIMIT',15000)
```
Run the query in Figure 189 to set the processor time that elapses before the user is warned of the approaching resource limit.
If you do not want to warn users when they are approaching their limit for the time elapsed, skip to step 4.
Figure 189. Activating prompting for time limit
```
INSERT INTO Q.RESOURCE__VIEW (RESOURCE__GROUP,RESOURCE__OPTION,INTVAL)
    VALUES('GROUP1','TIMEPROMPT',300)
```

Run the query in Figure 190 to set the processor time that can elapse before the governor cancels the user's activity.
Figure 190. For TSO and native OS/390 batch: Activating cancellation of activities when user reaches time limit
```
INSERT INTO Q.RESOURCE__VIEW (RESOURCE__GROUP,RESOURCE__OPTION,INTVAL)
    VALUES('GROUP1','TIMELIMIT',1000)
```
Run the query in Figure 191 to set the real time between intervals when the governor checks the user's activity.
Figure 191. For TSO and native OS/390 batch: Activating time interval check
```
INSERT INTO Q.RESOURCE__VIEW (RESOURCE__GROUP,RESOURCE__OPTION,INTVAL)
    VALUES('GROUP1','TIMECHECK',800)
```
Run the query shown in Figure 192 to turn on governing for the GROUP1 resource group. SCOPE is a resource option that activates or deactivates governing. Each resource group in the Q.RESOURCE__TABLE must have a RESOURCE__OPTION called SCOPE, and SCOPE must have a corresponding INTVAL of zero, or the resource group is not governed. Set INTVAL to 1 to deactivate governing.
Figure 192. Turning on the governor for a particular resource group
```
INSERT INTO Q.RESOURCE__VIEW (RESOURCE__GROUP,RESOURCE__OPTION,INTVAL)
    VALUES('GROUP1','SCOPE',0)
```
Run a query similar to the one in Figure 193 to add user JONES to the GROUP1 resource group in the English QMF environment.
Figure 193. Updating a user's resource group
```
UPDATE Q.PROFILES
  SET RESOURCE__GROUP='GROUP1'
  WHERE CREATOR='JONES' AND
  TRANSLATION='ENGLISH'
```
If you are using an NLF: Use a similar query to update a user's profile in an NLF environment, but use a TRANSLATION value from Table 1.
Instruct the user whose profile you updated to end the current QMF session and start another to activate the new values. This can be done with a DB2 CONNECT command or they can end their current QMF session and begin another to activate the new values.

Creating your own resource control table

You can create your own table or rename the Q.RESOURCE_TABLE. You can also include additional columns in the table you create, if Q.RESOURCE_VIEW is the view defined in this table, and if the table includes all of the columns shown inTable 73.

Figure 194 shows an example of SQL statements you might use to create a table called MY_RESOURCES. Substitute your own table, column, and table space names in the query. Before creating a new table, ensure you erase the Q.RESOURCE_TABLE from the database, because Q.RESOURCE_VIEW is defined in this table:

DROP TABLE Q.RESOURCE_TABLE

Dropping the Q.RESOURCE__TABLE also drops Q.RESOURCE_VIEW from the database, so you need to recreate both the table and the view, as shown in Figure 194 and Figure 195. Under TSO, substitute your own table space name for SPACE1.

Figure 194. Creating a resource control table or renaming Q.RESOURCE_TABLE

CREATE TABLE MY_RESOURCES
  (GROUP_NAME    CHAR(16) NOT NULL,
   CONSTRAINT    CHAR(16) NOT NULL,
   INTEGER       INTEGER,
   FLOAT_VALUE   FLOAT,
   CHARACTER     VARCHAR(80))
IN TBSPACE1

When running QMF on OS/390, you automatically invalidate the QMF application plan when you drop the view. For this reason, you should work outside QMF when you drop and recreate the resource table and view. Choose a time when QMF is inactive, and use DB2's DB2I facility. DB2I lets you carry out the work interactively.

If you do not use the IBM-supplied table space, you must create your own. If you rebind the QMF authorization plan explicitly, you also need the BIND privilege on the plan. You can find information on the needed authority for each of your SQL commands in the DB2 UDB for OS390 SQL Reference manual.

Always recreate Q.RESOURCE_VIEW if you decide to use a table other than Q.RESOURCE_TABLE or decide to give Q.RESOURCE__TABLE a different name, because QMF queries the view, not the table, to obtain resource control information to pass to the governor exit routine.

Figure 195 shows how to redefine Q.RESOURCE_VIEW as a view on the new table, MY_RESOURCES. Substitute your own table and column names for those in the figure.

Figure 195. Redefining the Q.RESOURCE_VIEW

CREATE VIEW Q.RESOURCE_VIEW
  (RESOURCE_GROUP, RESOURCE_OPTION, INTVAL, FLOATVAL, CHARVAL)
  AS SELECT GROUPNAME, CONSTRAINT, INTEGER, FLOAT_VALUE, CHARACTER
  FROM MY_RESOURCES

After you create the view, you must grant the SELECT privilege on Q.RESOURCE_VIEW to PUBLIC. Then test the new view; you can test the view using SPUFI. Finally, rebind the QMF authorization plan.

Table 73. Structure of the Q.RESOURCE_TABLE table
Column name	Data type	Length (bytes)	Nulls allowed?	Function/values
RESOURCE__GROUP	CHAR	16	No	Contains the name of the resource group. Update the RESOURCE__GROUP field of the user's row in Q.PROFILES to activate governing for that user.
RESOURCE__OPTION	CHAR	16	No	Your own name for a resource you want to monitor.
INTVAL	INTEGER		Yes	Reflects resource limit for resource options that have integer values. For example, number of rows retrieved from the database is a resource that has an integer value.
FLOATVAL	FLOAT		Yes	Reflects resource limit for resource options that have floating point values. FLOATVAL is null for the IBM-supplied governor.
CHARVAL	VARCHAR	80	Yes	Reflects resource limit for resource options that have character values. For example, you might establish a DAY_OF_WEEK resource option and assign MONDAY to CHARVAL so that QMF users can log on to QMF only on Mondays. CHARVAL is used as a comment column in the IBM-supplied governor.

[ Previous Page | Next Page | Contents | Index ]