Governor rule clauses
Each rule in the governor configuration file is made up of clauses that specify the conditions for applying the rule and the action that results if the rule evaluates to true.
The rule clauses must be specified in the order shown.
Optional beginning clauses
- Specifies a description for the rule. The description must be enclosed by either single or double quotation marks.
- Specifies the time period during which the rule is to be evaluated.
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 evaluated 24 hours a day.
- Specifies one or more authorization IDs under which the application
is executing. Multiple authorization IDs must be separated by a comma
(,); for example:
authid gene, michael, james. If this clause is not specified, the rule applies to all authorization IDs.
- Specifies the name of the executable or object file that is connected
to the database. Multiple application names must be separated by a
comma (,); for example:
applname db2bp, batch, geneprog. If this clause is not specified, the rule applies to all application names.Note:
- Application names are case sensitive.
- The database manager truncates all application names to 20 characters. You should ensure that the application that you want to govern is uniquely identified by the first 20 characters of its application name. Application names specified in the governor configuration file are truncated to 20 characters to match their internal representation.
- Specifies one or more limits for the governor to check. The limits
must be -1 or greater than 0 (for example,
cpu -1 locks 1000 rowssel 10000). At least one limit must be specified, and any limit that is not specified in a rule statement is not limited by that rule. The governor can check the following limits:
- cpu n
- Specifies the number of CPU seconds that can be consumed by an application. If you specify -1, the application's CPU usage is not limited.
- idle n
- Specifies the number of idle seconds that are allowed for a connection.
If you specify -1, the connection's idle time is not limited.Note: Some database utilities, such as backup and restore, establish a connection to the database and then perform work through engine dispatchable units (EDUs) that are not visible to the governor. These database connections appear to be idle and might exceed the idle time limit. To prevent the governor from taking action against these utilities, specify -1 for them through the authorization ID that invoked them. For example, to prevent the governor from taking action against utilities that are running under authorization ID DB2SYS, specify
authid DB2SYS setlimit idle -1.
- locks n
- Specifies the number of locks that an application can hold. If you specify -1, the number of locks held by the application is not limited.
- rowsread n
- Specifies the number of rows that an application can select. If
you specify -1, the number of rows the application can select is not
limited. The maximum value that can be specified is 4 294 967 298.Note: This limit is not the same as
rowssel. The difference is that
rowsreadis the number of rows that must be read to return the result set. This number includes engine reads of the catalog tables and can be reduced when indexes are used.
- rowssel n
- Specifies the number of rows that can be returned to an application. This value is non-zero only at the coordinator database partition. If you specify -1, the number of rows that can be returned is not limited. The maximum value that can be specified is 4 294 967 298.
- uowtime n
- Specifies the number of seconds that can elapse from the time
that a unit of work (UOW) first becomes active. If you specify -1,
the elapsed time is not limited.Note: If you used the sqlmon API to deactivate the unit of work monitor switch or the timestamp monitor 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 a unit of work (UOW) of the application has been started before the Governor starts, then the Governor will not govern that UOW.
- Specifies the action that is to be taken if one or more specified
limits is exceeded. If a limit is exceeded and the
actionclause is not specified, the governor reduces the priority of agents working for the application by 10.
that an application is forced if the
setlimitis exceeded on any partition where the application is running.Note: In partitioned database environments, a local application snapshot is used to collect information for
setlimitevaluation, instead of a global snapshot. If the governor evaluates a
setlimiton an application's remote partition and determines a limit is exceeded and the force action is performed, the application terminates on all database partitions. For example, if a rule has a "idle 30"
setlimitand a remote subagent is idle for 40 seconds, the force action terminates the application on all partitions.
- Specifies that an application is forced only if
setlimitis exceeded on the application's coordinating partition.
In partitioned database environments, a local application snapshot is used to collect information for
setlimitevaluation, instead of a global snapshot. The
forcecoordaction occurs only if
setlimitvalues are exceeded on the application's coordinating partition. For example, if a rule has a
setlimitof "idle 30" and a coordinating agent is idle for 40 seconds, then the
forcecoordaction terminates the application on all partitions. However, no action is performed when only a remote subagent is idle for 40 seconds.
- nice n
- Specifies a change to the
relative priority of agents working for the application. Valid values range from -20 to +20 on Linux® and UNIX, and from -1
to 6 on Windows platforms.
- On Linux and UNIX, the
agentpri database manager configuration parameter must be set to the default
value; otherwise, it overrides the
- On Windows platforms, the agentpri
database manager configuration parameter and the
nicevalue can be used together.
You can use the governor to control the priority of applications that run in the default user service superclass, SYSDEFAULTUSERCLASS. If you use the governor to lower the priority of an application that runs in this service superclass, the agent disassociates itself from its outbound correlator (if it is associated with one) and sets its relative priority according to the agent priority specified by the governor. You cannot use the governor to alter the priority of agents in user-defined service superclasses and subclasses. Instead, you must use the agent priority setting for the service superclass or subclass to control applications that run in these service classes. You can, however, use the governor to force connections in any service class.Note: On AIX® systems, the instance owner must have the CAP_NUMA_ATTACH capability to raise the relative priority of agents working for the application. To grant this capability, logon as root and run the following command:
chuser capabilities=CAP_NUMA_ATTACH,CAP_PROPAGATE <userid>
- On Linux and UNIX, the agentpri database manager configuration parameter must be set to the default value; otherwise, it overrides the
- schedule [class]
- Scheduling improves the priorities of agents working on applications.
The goal is to minimize the average response time while maintaining
fairness across all applications.The governor chooses the top applications for scheduling on the basis of the following criteria:
- The application holding the greatest number of locks (an attempt to reduce the number of lock waits)
- The oldest application
- The application with the shortest estimated remaining run time (an attempt to allow as many short-lived statements as possible to complete during the interval)
The top three applications in each criterion are given higher priorities than all other applications. That is, the top application in each criterion group is given the highest priority, the next highest application is given the second highest priority, and the third-highest application is given the third highest priority. If a single application is ranked in the top three for more than one criterion, it is given the appropriate priority for the criterion in which it ranked highest, and the next highest application is given the next highest priority for the other criteria. For example, if application A holds the most locks but has the third shortest estimated remaining run time, it is given the highest priority for the first criterion. The fourth ranked application with the shortest estimated remaining run time is given the third highest priority for that criterion.
The applications that are selected by this governor rule are divided up into three classes. For each class, the governor chooses nine applications, which are the top three applications from each class, based on the criteria described previously. If you specify the
classoption, all applications that are selected by this rule are considered to be a single class, and nine applications are chosen and given higher priorities as described previously.
If an application is selected in more than one governor rule, it is governed by the last rule in which is it selected.Note: If you used the sqlmon 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, they are turned off for the entire instance, and the governor no longer receives this information.The schedule action can:
- Ensure that applications in different groups get time, without
all applications splitting time evenly. For example, if 14 applications
(three short, five medium, and six long) are running at the same time,
they might 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;
- Ensure that each of several user groups (for example, organizational
departments) gets equal prioritization. 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 example, 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 group,
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;
- Let the governor schedule all applications.
classoption is not specified, the governor creates its own classes based on how many active applications fall under the schedule action, and puts applications into different classes based on the 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, by not specifying