Work identification by origin with workloads

Workloads identify incoming work based on its source so that it can later be monitored or managed by Db2® workload management independently of all other work. The source is determined using the attributes of the database connection under which the work is submitted.

The attributes of a connection are first evaluated when the connection is established and the connection is matched to a workload definition. This relationship between a connection and a specific workload definition is called a workload occurrence. If any of the attributes change during the life of that connection, the workload assignment is reevaluated at the start of the next unit of work following the change. If a new workload definition is found to be a better match for the connection at this time, the old workload occurrence (for the previously assigned workload) ends and a new occurrence starts for the newly assigned workload definition. Although each connection is assigned to one and only one workload at a time, there can be multiple connections (workload occurrences) assigned to the same workload definition simultaneously. For more information, see Workload assignment.

For example, to assign all connections created by the application Accounts to a workload REPORTING, which maps the activities under those connections to run in the Marketing service class, issue a CREATE WORKLOAD statement such as the following:
CREATE WORKLOAD REPORTING APPLNAME('Accounts') SERVICE CLASS Marketing
This creates the following workload:
Figure 1. The REPORTING workload
The REPORTING workload
Then grant the USAGE privilege on the REPORTING workload to PUBLIC:
GRANT USAGE ON WORKLOAD REPORTING TO PUBLIC
To assign all activities created by the application Accounts under the connections that belong to the session user group Deptmgr to the SUMMARY workload, which maps the activities to the HumanResources service class, issue a statement such as the following:
CREATE WORKLOAD SUMMARY SESSION_USER_GROUP('Deptmgr') APPLNAME('Accounts')
SERVICE CLASS HumanResources
This creates the following workload:
Figure 2. The SUMMARY workload
The SUMMARY workload
Then grant the USAGE privilege on the SUMMARY workload to PUBLIC:
GRANT USAGE ON WORKLOAD SUMMARY TO PUBLIC

You can view your workload definitions by querying the SYSCAT.WORKLOADS view, and you can view the connection attributes that you specified for each workload by querying the SYSCAT.WORKLOADCONNATTR view. You can view who is authorized to use a workload by querying the SYSCAT.WORKLOADAUTH view. To see what workload occurrences exist on the system at any point in time, use the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES or MON_GET_WORKLOAD table function.

SYSDEFAULTUSERWORKLOAD is the default workload. Any connection that is not assigned to a custom-defined workload during workload evaluation is assigned to this default workload, which ensures that all database connections are associated with a workload. Work assigned to the default workload SYSDEFAULTUSERWORKLOAD is executed in the SYSDEFAULTUSERCLASS service class by default.

Supported database connection attributes

You must specify at least one database connection attribute in the workload definition, and each connection attribute can have one or more values. If you do not specify a value for a specific connection attribute in the workload definition, the data server does not examine that attribute during workload evaluation.
Table 1. Connection attributes in a workload definition
Connection attribute Description
Address The actual communication address used by the client to communicate with the database server. The only protocol supported is TCP/IP. The address must be an IPv4 address, an IPv6 address, or a secure domain name.
Application name The name of the application running at the client, as known to the data server. The application name is equivalent to the value shown in the Application name field in the system monitor output. See the appl_name monitor element for more information.
System authorization ID The authorization ID of the user who connected to the database, as set in the SYSTEM_USER special register. You can change the value of SYSTEM_USER by connecting as a user with a different authorization ID.
Session authorization ID The authorization ID that is used for the current session of the application, as set in the SESSION_USER special register. You can change the value of SESSION_USER by using a trusted context or the SET SESSION AUTHORIZATION statement.
Group of session authorization ID The groups to which the current session user belongs.
Role of session authorization ID The roles granted to the current session user.
Client user ID The client user ID from the client information as set in the CURRENT CLIENT_USERID (or CLIENT USERID) special register. You can change the value of the client user ID by using one of the defined mechanisms provided by specific Db2 clients, the sqleseti (set client information) API, or the WLM_SET_CLIENT_INFO procedure.
Client application name The application name from the client information as set in the CURRENT CLIENT_APPLNAME (or CLIENT APPLNAME) special register. You can change the value of the client application name by using one of the defined mechanisms provided by specific Db2 clients, the sqleseti API, or the WLM_SET_CLIENT_INFO procedure.
Client workstation name The workstation name from the client information as set in the CURRENT CLIENT_WRKSTNNAME (or CLIENT WRKSTNNAME) special register. You can change the value of the client workstation name by using one of the defined mechanisms provided by specific Db2 clients, the sqleseti API, or the WLM_SET_CLIENT_INFO procedure.
Client accounting string The accounting string from the client information as set in the CURRENT CLIENT_ACCTNG (or CLIENT ACCTNG) special register. You can change the value of the client accounting string by using the sqleseti API or the WLM_SET_CLIENT_INFO procedure.

Use of wild cards in connection attributes

Some connection attributes support the specification of an asterisk (*) as a wild card in the CREATE WORKLOAD and ALTER WORKLOAD statements. You can use wild cards in situations where a connection attribute can take on several similar values, which can be matched by a regular expression using wild cards, without defining connection attributes for each of the possible values.

The wild card asterisk (*) matches zero or more characters. If you need to match an asterisk, use a double asterisk (**) to specify the asterisk as a literal character.

For example: If you have several accounts receivable applications (accrec01, accrec02 ... accrec15) that you all want to belong to the same workload for equal treatment by Db2 workload manager, define the CURRENT CLIENT_APPLNAME('accrec*') connection attribute to match all of these applications when you create or alter your workload. Similarly, an acc*rec accounts receivable application (a name that includes an asterisk character) is matched by the CURRENT CLIENT_APPLNAME('acc**rec') connection attribute.

The following workload connection attributes support the use of wild cards:

  • APPLNAME
  • CURRENT CLIENT_ACCTNG
  • CURRENT CLIENT_APPLNAME
  • CURRENT CLIENT_USERID
  • CURRENT CLIENT_WRKSTNNAME

Set client information to identify requests

By default, many application servers set up connections with the same information and pass the same client information, if any, for all client requests that they handle. Some products, such as WebSphere® and Cognos®, provide a facility to push down unique information about each request through the client information fields. This information uniquely identifies the end-user request within Db2. Most other products provide a way for the application server to be customized so that unique client information can be sent to Db2 prior to the start of processing an end-user request.

Specifying unique client attributes from the application server enables the specialized treatment of requests within Db2 and the assignment of requests from different clients to different workloads (and to different service classes).

Connection attribute evaluation order

As you analyze the usage characteristics of your environment, you can use the CREATE WORKLOAD statement to create your own workloads and map them to specific service classes. When you create the workload, you define both the values that are used to evaluate the connection attributes during workload assignment and the order in which the workload is evaluated relative to other workloads. Because more than one workload can match incoming connection attributes, being able to change the evaluation order enables you to determine which matching workload is chosen. Whether or not the session user has the USAGE privilege on the workload also determines which matching workload is chosen. For more information, see Workload assignment.

The following figure shows multiple requests being evaluated against workloads in the order A, B, C, and D, then assigned to specific workloads and executed in the applicable service class. Requests that cannot be matched to an existing workload are matched to the SYSDEFAULTUSERWORKLOAD workload. For information about the types of activities that run in the default maintenance class and default system class, see Default service superclasses and subclasses.

Figure 3. Service classes and workloads
Service classes and workloads