When you start the governor, you specify the name of the configuration file that contains the rules to be used to govern applications running against the database. The governor acts based on these rules.
If your requirements for governing the database change, you can edit the configuration file without stopping the governor. Each governor daemon will detect that the file has changed, and reread it.
You must create the configuration file in a directory that is mounted across all the database nodes, because the governor daemon on each node must be able to read the same configuration file.
The configuration file consists of rules and comments. Most entries can be specified in uppercase, lowercase, or mixed case characters. The exception is applname which is case sensitive.
You delimit comments within the { } braces. The rules include:
Each rule in the file must be followed by a semicolon (;).
The following rules specify the database being monitored, and the interval at which the daemon wakes up after working through its loop of activities (which are described in The Governor Daemon). Each of these rules are only specified once in the file.
| Note: | This option is not available in Windows NT or OS/2 environments. |
If a short connect session occurs entirely within the account interval, no log record is written. When log records are written, they contain CPU statistics that reflect CPU usage since the previous log record for the connection. If the governor is stopped then restarted, CPU usage may be reflected in two log records; these can be identified through the application IDs in the log records. For more information about governor log files, see Governor Log Files.
You combine the following rule clauses to form a rule (that is, the full rule is followed by a semicolon, and not each individual clause). The clauses specify the time during which the rule applies, the limit on resource that can be used, and, optionally, specific users or applications and any action for the governor to take if a limit specified in the rule is exceeded. The clauses can only be specified once in a rule, but can be specified in more than one rule. The clauses must be specified in the order shown. In the description that follows, a [ ] indicates an optional clause.
The time period must be specified in the following format time hh:mm hh:mm, for example, time 8:00 18:00. If this clause is not specified, the rule is valid 24 hours a day.
Multiple application names must be separated by a comma (,), for example, applname db2bp, batch, geneprog. If this clause does not appear in a rule, the rule applies to all application names.
Notes:
Application names specified in the governor configuration file are truncated to 20 characters to match their internal representation.
| Note: | This option is not available in Windows NT or OS/2 environments. |
| Note: | If you used the sqlmon (Database System Monitor Switch) API to deactivate the unit of work switch, this will affect the ability of the governor to govern applications based on the unit of work elapsed time. The governor uses the monitor to collect information about the system. If you turn off the switches in the database manager configuration file, then it is turned off for the entire instance, and governor will no longer receive this information. |
| Note: | This limit is not the same as rowssel. The difference is that rowsread is the count of the number of rows that had to be read in order to return the result set. The number of rows read includes reads of the catalog tables by the engine and may be diminished when indices are used. |
| Note: | If a limit is exceeded and the action clause is not specified, the governor reduces the priority of agents working for the application by 10. |
For this parameter to be effective:
The governor enforces its schedule by setting priorities for the agents working on the applications, using query cost estimates from the DB2 internal query compiler. If the class option is specified, all applications chosen by the rule are scheduled among themselves only. If this option is not specified, the governor uses one or more classes, with scheduling done within each class.
Within each class, how an application is prioritized is based on:
Applications that are not covered by any schedule run with the highest authority.
| Note: | If you used the sqlmon (Database System Monitor Switch) API to deactivate the statement switch, this will affect the ability of the governor to govern applications based on the statement elapsed time. The governor uses the monitor to collect information about the system. If you turn off the switches in the database manager configuration file, then it is turned off for the entire instance, and governor will no longer receive this information. |
The schedule action can:
For instance, if 12 applications (three short, five medium, and six long) are running at the same time, they may all have poor response times because they are splitting the CPU. The database administrator can set up two groups, medium-length applications and long-length applications. Using priorities, the governor permits all the short applications to run, and ensures that at most three medium and three long applications run simultaneously. To achieve this, the governor configuration file contains one rule for medium-length applications, and another rule for long applications. The following example shows a portion of a governor configuration file that illustrates this point:
desc "Group together medium applications in 1 schedule class" applname medq1, medq2, medq3, medq4, medq5 setlimit cpu -1 action schedule class; desc "Group together long applications in 1 schedule class" applname longq1, longq2, longq3, longq4, longq5, longq6 setlimit cpu -1 action schedule class;
If one group is running a large number of applications, the administrator can ensure that other groups are still able to obtain reasonable response times for their applications. For instance, in a case involving three departments (Finance, Inventory, and Planning), all the Finance users could be put into one group, all the Inventory users could be put into a second, and all the Planning users could be put into a third group. The processing power would be split more or less evenly among the three departments. The following example shows a portion of a governor configuration file that illustrates this point:
desc "Group together Finance department users" authid tom, dick, harry, mo, larry, curly setlimit cpu -1 action schedule class; desc "Group together Inventory department users" authid pat, chris, jack, jill setlimit cpu -1 action schedule class; desc "Group together Planning department users" authid tara, dianne, henrietta, maureen, linda, candy setlimit cpu -1 action schedule class;
If the class option is not included with the action, the governor creates its own classes based on how many applications fall under the schedule action, and puts applications into different classes based on the DB2 query compiler's cost estimate for the query the application is running. The administrator can choose to have all applications scheduled by not qualifying which applications are chosen. That is, no applname or authid clauses are supplied, and the setlimit clause causes no restrictions.
| Note: | If a limit is exceeded and the action clause is not specified, the governor reduces the priority of agents working for the application. |
If more than one rule applies to an application, the rule that is closest to the end of the configuration file is applied to the application. An exception occurs if -1 is specified for a clause in a rule. In this situation, the value specified for the clause in the subsequent rule can only override the value previously specified for the same clause: other clauses in the previous rule are still operative. For example, one rule indicates that the priority of an application is to be decreased if its elapsed time is greater than 1 hour, or if it selects more than 100 000 rows (that is, rowssel 100000 uowtime 3600). A subsequent rule indicates that the same application can have unlimited elapsed time (that is, uowtime -1). In this situation, if the application runs for more than 1 hour, its priority won't be changed (that is, uowtime -1 overrides uowtime 3600), but if it selects more than 100 000 rows, its priority will be lowered (as rowssel 100000 is still valid).
Figure 90 shows an example of a configuration file.
Figure 90. Example Governor Configuration File
{ Wake up once a second, the database name is ibmsampl
do accounting every 30 minutes. }
interval 1; dbname ibmsampl; account 30;
desc "CPU restrictions apply 24 hours a day to everyone"
setlimit cpu 600 rowssel 1000000 rowsread 5000000;
desc "Allow no UOW to run for more than an hour"
setlimit uowtime 3600 action force;
desc 'Slow down a subset of applications'
applname jointA, jointB, jointC, quryA
setlimit cpu 3 locks 1000 rowssel 500 rowsread 5000;
desc "Have governor prioritize these 6 long apps in 1 class"
applname longq1, longq2, longq3, longq4, longq5, longq6
setlimit cpu -1
action schedule class;
desc "Schedule all applications run by the planning dept"
authid planid1, planid2, planid3, planid4, planid5
setlimit cpu -1
action schedule;
desc "Schedule all CPU hogs in one class which will control consumption"
setlimit cpu 3600
action schedule class;
desc "Slow down the use of db2 CLP by the novice user"
authid novice
applname db2bp.exe
setlimit cpu 5 locks 100 rowssel 250;
desc "During day hours do not let anyone run for more than 10 seconds"
time 8:30 17:00 setlimit cpu 10 action force;
desc "Allow users doing performance tuning to run some of
their applications during lunch hour"
time 12:00 13:00 authid ming, geoffrey, john, bill
applname tpcc1, tpcc2, tpcA, tpvG setlimit cpu 600 rowssel 120000 action force;
desc "Some people should not be limited -- database administrator
and a few others. As this is the last specification in the
file, it will override what came before."
authid gene, hershel, janet setlimit cpu -1 locks -1 rowssel -1 uowtime -1;
desc "Increase the priority of an important application so it always
completes quickly"
applname V1app setlimit cpu 1 locks 1 rowssel 1 action priority -20;