DB2 Version 10.1 for Linux, UNIX, and Windows

Example: Workload assignment when workload attributes have single values

The example in this topic shows how the data server performs workload assignment. In this example, only one value is specified for each workload connection attribute.

Assume that the following workloads exist in the catalog:

Table 1. Workloads in the catalog
Evaluation order	Workload name	APPLNAME	SYSTEM _USER	SESSION _USER	SESSION _USER GROUP	SESSION _USER ROLE	CURRENT CLIENT _USERID	CURRENT CLIENT _ACCTNG
1	REPORTS	AppA
2	INVENTORY REPORT	AppB	LYNN		ACCOUNTING	TELEMKTR
3	SALES REPORT	AppC	KATE	KATE		SALESREP
4	AUDIT REPORT	AppB			ACCOUNTING	FINANALYST
5	EXPENSE REPORT	AppA	TIM			EXPENSE APPROVER
6	AUDIT RESULT			LYNN			LYNN	Audit Group

Assume that a database connection with the following attributes is established:

Table 2. Database connection attributes
ADDRESS	APPLNAME	SYSTEM _USER	SESSION _USER	SESSION _USER GROUP	SESSION _USER ROLE	CURRENT CLIENT _USERID	CURRENT CLIENT _APPLNAME	CURRENT CLIENT _WRKSTNNAME	CURRENT CLIENT _ACCTNG
9.26.53.111	AppA	TIM	TIM	FINANCE	FINANALYST, EXPENSE APPROVER	NULL	NULL	NULL	Business account

When the first unit of work is submitted, the data server checks each workload in the catalog, starting with the first workload in the list, and processes the workloads in ascending order until it finds a workload with matching attributes. When a matching workload is found, the unit of work runs under an occurrence of that workload. When determining which workload to assign the connection to, the data server compares the connection attributes in deterministic order.

The data server first checks the REPORTS workload for a match. The REPORTS workload is first in the list.

Table 3. REPORTS workload in the catalog
Evaluation order	Workload name	ADDRESS	APPLNAME	SYSTEM _USER	SESSION _USER	SESSION _USER GROUP	SESSION _USER ROLE	CURRENT CLIENT _USERID	CURRENT CLIENT _APPLNAME	CURRENT CLIENT _WRKSTNNAME	CURRENT CLIENT _ACCTNG
1	REPORTS		AppA

The data server checks the connection attributes in the following deterministic order:

APPLNAME. The value of APPLNAME, AppA, for the database connection matches the value of APPLNAME for the REPORTS workload.
SYSTEM_USER, which is not set in the workload definition. Any value (including a null value) is considered a match.
SESSION_USER, which is not set in the workload definition. Any value is considered a match.
SESSION_USER GROUP, which is not set in the workload definition. Any value is considered a match.
SESSION_USER ROLE, which is not set in the workload definition. Any value is considered a match.
CURRENT CLIENT_USERID, which is not set in the workload definition. Any value is considered a match.
CURRENT CLIENT_APPLNAME, which is not set in the workload definition. Any value is considered a match.
CURRENT CLIENT_WRKSTNNAME, which is not set in the workload definition. Any value is considered a match.
CURRENT CLIENT_ACCTNG, which is not set in the workload definition. Any value is considered a match.

In this situation, because of the explicit and implicit matches between the connection attributes of the REPORTS workload and the information passed on the connection, the data server selects the REPORTS workload as a potential match. After selecting a workload, the data server then checks whether the session user has the USAGE privilege on the workload. Assuming that the session user TIM has the USAGE privilege on the REPORTS workload, that workload is used for the connection. If, however, TIM does not possess the USAGE privilege on the REPORTS workload, the data server continues by checking the INVENTORYREPORT workload for a match.

Assume that you want TIM to be assigned to the EXPENSEREPORT workload because that workload has additional connection attributes specified. In this situation, you would alter the evaluation order of the workloads to position EXPENSEREPORT before REPORTS in the workload list:

ALTER WORKLOAD EXPENSEREPORT POSITION AT 1

You could also use the following SQL statement to achieve the same result:

ALTER WORKLOAD EXPENSEREPORT BEFORE REPORTS

To ensure that the ALTER WORKLOAD statement takes effect, you must immediately issue a COMMIT statement after the ALTER WORKLOAD statement. The effect of the ALTER WORKLOAD statement on the catalog is as follows:

Table 4. Workloads in the catalog after repositioning the EXPENSEREPORT workload
Evaluation order	Workload name	APPLNAME	SYSTEM _USER	SESSION _USER	SESSION _USER GROUP	SESSION _USER ROLE	CURRENT CLIENT _USERID	CURRENT CLIENT _ACCTNG
1	EXPENSE REPORT	AppA	TIM			EXPENSE APPROVER
2	REPORTS	AppA
3	INVENTORY REPORT	AppB	LYNN		ACCOUNTING	TELEMKTR
4	SALES REPORT	AppC	KATE	KATE		SALESREP
5	AUDIT REPORT	AppB			ACCOUNTING	FINANALYST
6	AUDIT RESULT			LYNN			LYNN	Audit Group

If TIM does not already have the USAGE privilege on the EXPENSEREPORT workload, you must issue the following statements (the COMMIT statement ensures that the GRANT statement takes effect):

GRANT USAGE ON WORKLOAD EXPENSEREPORT TO USER TIM
COMMIT

At the beginning of the next unit of work, workload reassignment occurs, and the data server assigns the connection from TIM to the EXPENSEREPORT workload. In addition, new units of work submitted by other connections that have the same attributes are also associated with the EXPENSEREPORT workload.