Example: Workload assignment for a unit of work when multiple workloads exist

The example in this topic shows how the data server performs workload evaluation to assign the connection to an existing workload.

Assume that the following workloads are defined 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
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
1 EXPENSE REPORT AppB TIM     EXPENSE APPROVER        
2 REPORTS AppB                
3 INVENTORYREPORT AppA LYNN   ACCOUNTING TELEMKTR        
4 SALES REPORT AppC KATE KATE   SALESREP        
5 AUDIT REPORT AppA     ACCOUNTING FINANALYST        
6 AUDIT RESULT     LYNN     LYNN     Audit Group
Suppose that a database connection with the following attributes is established:
Table 2. Database connection attributes
APPLNAME
SYSTEM
_USER
SESSION
_USER
SESSION
_USER
GROUP
SESSION
_USER
ROLE
CURRENT
CLIENT
_USERID
CURRENT
CLIENT
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
AppA LYNN LYNN ACCOUNTING FINANALYST, SALESREP LYNN NULL wrkstn2 Audit group

When the first unit of work is submitted, the data server checks each workload in the catalog in ascending evaluation order and stops when it finds a workload whose connection attributes match those supplied by the connection. When it checks the workloads, the data server compares the connection attributes in deterministic order.

First, the data server checks the EXPENSEREPORT workload:
Table 3. EXPENSEREPORT workload in the catalog
Evaluation order Workload name APPLNAME
SYSTEM
_USER
SESSION
_USER
SESSION
_USER
GROUP
SESSION
_USER
ROLE
CURRENT
CLIENT
_USERID
CURRENT
CLIENT
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
1 EXPENSEREPORT AppB TIM     EXPENSE APPROVER        
Because the APPLNAME attribute in the workload definition is AppB but the APPLNAME attribute passed by the connection is AppA, no match is possible. The data server proceeds to the REPORTS workload, which is second in the list:
Table 4. REPORTS workload in the catalog
Evaluation order Workload name APPLNAME
SYSTEM
_USER
SESSION
_USER
SESSION
_USER
GROUP
SESSION
_USER
ROLE
CURRENT
CLIENT
_USERID
CURRENT
CLIENT
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
2 REPORTS AppB                
Again, the APPLNAME attribute in the workload definition is AppB, which does not match AppA. The data server proceeds to the third workload in the list, INVENTORYREPORT:
Table 5. INVENTORYREPORT workload in the catalog
Evaluation order Workload name APPLNAME
SYSTEM
_USER
SESSION
_USER
SESSION
_USER
GROUP
SESSION
_USER
ROLE
CURRENT
CLIENT
_USERID
CURRENT
CLIENT
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
3 INVENTORYREPORT AppA LYNN   ACCOUNTING TELEMKTR        
The data server checks for a match between the submitted connection attributes and the INVENTORYREPORT workload. The attributes are checked in the following order:
  1. APPLNAME. Both the workload definition and the connection have a value of AppA, so a match occurs.
  2. SYSTEM_USER. Both the workload definition and the connection have a value of LYNN, so a match occurs.
  3. SESSION_USER. The connection passed a value of LYNN. Because the SESSION_USER attribute is not set for the workload, any value, including a null value, that is passed by the connection matches.
  4. SESSION_USER GROUP. Both the workload definition and the connection have a value of ACCOUNTING, so a match occurs.
  5. SESSION_USER ROLE. The workload definition specifies the value TELEMKTR, but the connection supplied the values of FINANALYST and SALESREP. No match occurs for this attribute.
The data server stops trying to match the INVENTORYREPORT workload and the connection attributes and proceeds to the fourth workload in the list, SALESREPORT:
Table 6. SALESREPORT workload in the catalog
Evaluation order Workload name APPLNAME
SYSTEM
_USER
SESSION
_USER
SESSION
_USER
GROUP
SESSION
_USER
ROLE
CURRENT
CLIENT
_USERID
CURRENT
CLIENT
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
4 SALESREPORT AppC KATE KATE   SALESREP        
Because the APPLNAME of the SALESREPORT workload definition is AppC, no match occurs with the connection (which passed a value of AppA for APPLNAME). The data server then proceeds to the fifth workload in the list, AUDITREPORT:
Table 7. AUDITREPORT workload in the catalog
Evaluation order Workload name APPLNAME
SYSTEM
_USER
SESSION
_USER
SESSION
_USER
GROUP
SESSION
_USER
ROLE
CURRENT
CLIENT
_USERID
CURRENT
CLIENT
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
5 AUDITREPORT AppA     ACCOUNTING FINANALYST        
The data server compares the attributes of the AUDITREPORT workload and the connection in the deterministic order:
  1. APPLNAME. Both the workload definition and the connection have a value of AppA, so a match occurs.
  2. SYSTEM_USER. The connection passed a value of LYNN. Because the SYSTEM_USER attribute is not set for the workload, any value passed by the connection matches.
  3. SESSION_USER. The connection passed a value of LYNN. Because the SESSION_USER attribute is not set for the workload, any value passed by the connection matches.
  4. SESSION_USER GROUP. Both the workload and the connection have a value of ACCOUNTING for this attribute, so a match occurs.
  5. SESSION_USER ROLE. Both the workload and the connection have a value of FINANALYST for this attribute, so a match occurs.
  6. CURRENT CLIENT_USERID. Because the CURRENT CLIENT_USERID attribute is not set for the workload, any value passed by the connection matches.
  7. CURRENT CLIENT_APPLNAME. Because the CURRENT CLIENT_APPLNAME attribute is not set for the workload, any value passed by the connection matches.
  8. CURRENT CLIENT_WRKSTNNAME. Because the CURRENT CLIENT_WRKSTNNAME attribute is not set for the workload, any value passed by the connection matches.
  9. CURRENT CLIENT_ACCTNG. Because the CURRENT CLIENT_ACCTNG attribute is not set for the workload, any value passed by the connection matches.
After processing all the connection attributes and finding a matching workload, the data server checks whether the session user has the USAGE privilege on the workload. Assume that LYNN does not have the USAGE privilege on the AUDITREPORT workload. In this situation, although all of the connection attributes match, this workload is not associated with the connection. The data server proceeds to the sixth workload in the evaluation list, AUDITRESULT:
Table 8. AUDITRESULT workload in the catalog
Evaluation order Workload name APPLNAME
SYSTEM
_USER
SESSION
_USER
SESSION
_USER
GROUP
SESSION
_USER
ROLE
CURRENT
CLIENT
_USERID
CURRENT
CLIENT
_APPLNAME
CURRENT
CLIENT
_WRKSTNNAME
CURRENT
CLIENT
_ACCTNG
6 AUDITRESULT     LYNN     LYNN     Audit Group
The data server compares the attributes of the AUDITRESULT workload and the connection in the deterministic order:
  1. APPLNAME. Because the APPLNAME attribute is not set for the workload, any value passed by the connection matches.
  2. SYSTEM_USER. Because the SYSTEM_USER attribute is not set for the workload, any value passed by the connection matches.
  3. SESSION_USER. Both the workload and the connection have a value of LYNN for this attribute, so a match occurs.
  4. SESSION_USER GROUP. Because the SESSION_USER GROUP attribute is not set for the workload, any value passed by the connection matches.
  5. SESSION_USER ROLE. Because the SESSION_USER ROLE attribute is not set for the workload, any value passed by the connection matches.
  6. CURRENT CLIENT_USERID. Both the workload and the connection have a value of LYNN for this attribute, so a match occurs.
  7. CURRENT CLIENT_APPLNAME. Because the CURRENT CLIENT_APPLNAME attribute is not set for the workload, any value passed by the connection matches.
  8. CURRENT CLIENT_WRKSTNNAME. Because the CURRENT CLIENT_WRKSTNNAME attribute is not set for the workload, any value passed by the connection matches.
  9. CURRENT CLIENT_ACCTNG. Both the workload and the connection have a value of Audit Group for this attribute, so a match occurs.

After processing all of the connection attributes and finding a matching workload, the data server checks whether the session user has the USAGE privilege on the workload. In this situation, assume that the session user LYNN has the USAGE privilege on the AUDITRESULT workload. Because all of the connection attributes match and the session user has the USAGE privilege, the connection is assigned to the AUDITRESULT workload.