Example: Workload assignment

At the beginning of the first unit of work after a database connection is established, the data server assigns the connection to a workload by evaluating the connection attributes of each workload that is enabled.

Workload reevaluation occurs at the beginning of each unit of work if the value of a connection attribute or the workload definition itself changes during the unit of work.

The following figure shows a workload assignment. Users in the Marketing group who submit queries through AppA are assigned to the APPAQUERIES workload. They are not assigned to the PAYROLL workload, even though PAYROLL is positioned before APPAQUERIES, because the definition of workload PAYROLL specifies the SESSION_USER GROUP keyword as Finance. Users in the Finance group who submit queries through AppA are assigned to the FINANCE workload. They are not assigned to the PAYROLL workload, even though it is more specific and specifies both AppA and Finance in its definition, because the FINANCE workload is positioned before the PAYROLL workload. Users in the Marketing group who submit queries through AppB are assigned to the SYSDEFAULTUSERWORKLOAD workload, because none of the connection attributes specified in the FINANCE, PAYROLL, or APPAQUERIES workload definitions match the AppB application or Marketing group.

Figure 1. Example of workload assignment

1 In the preceding figure, the CREATE WORKLOAD statements are as follows:

CREATE WORKLOAD PAYROLL APPLNAME ('AppA') SESSION_USER GROUP ('FINANCE')
SERVICE CLASS SC1

CREATE WORKLOAD APPAQUERIES APPLNAME('AppA') POSITION LAST
SERVICE CLASS SC2

CREATE WORKLOAD FINANCE SESSION_USER GROUP ('FINANCE') SERVICE CLASS SC1
POSITION BEFORE PAYROLL

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).

The following figure shows an example of a three-tier environment where queries are submitted by different user applications, (marketing.exe, auditing.exe, and reporting.exe), through an application server that establishes a connection to the database using the session user APPUSER. Three workloads are defined: one for queries submitted by marketing.exe, one for queries submitted by reporting.exe, and one for the rest of the queries. As shown in the figure, to assign queries submitted by marketing.exe to the MARKETING workload, the application server calls the sqleseti API to set the value of the CURRENT CLIENT_APPLNAME special register to marketing.exe. Similarly, to assign queries submitted by reporting.exe to the REPORTING workload, the server calls sqleseti to set the value of the CURRENT CLIENT_APPLNAME special register to reporting.exe. Note that in the figure, when the server calls sqleseti to set the CURRENT CLIENT_USERID special register to Lidia (with nothing else changing; that is, the client application name is still set to reporting.exe), no workload reassignment occurs because there is no workload defined specifically with the CURRENT CLIENT_USERID set to Lidia.

Figure 2. Example of workload assignment in a three-tier environment

The following statements are used to define the workloads specified in box 1 in the previous figure:

CREATE WORKLOAD MARKETING SESSION_USER ('APPUSER')
CURRENT CLIENT_APPLNAME ('marketing.exe') SERVICE CLASS SC2
POSITION AT 1

CREATE WORKLOAD REPORTING SESSION USER ('APPUSER')
CURRENT CLIENT_APPLNAME ('reporting.exe') SERVICE CLASS SC4
POSITION AFTER MARKETING

CREATE WORKLOAD APPSERV SESSION_USER ('APPUSER')
SERVICE CLASS SC1