Connections not mapping to the right workload? Don't forget to check the USAGE Privilege!

A common question I get is about connections not associating with the expected DB2 workload definition when first using DB2 workload management.

Sometimes this is caused by incorrect connection attribute values in the workload definition (e.g. spelling or case errors) which can be quickly cleared up by looking at the actual values seen by DB2 from the WLM_GET_SERVICE_CLASS_WORKLOAD_OCCURRENCES table function.

Other times, there doesn't appear to be any mistake in the values but it is still simply not working. Well,  the other major cause of workload mishaps is the relatively silent USAGE privilege required for a connection to use a DB2 workload. This privilege is part of the algorithm used to determine if a connection should be associated with a workload definition but it is not always clear in the CREATE WORKLOAD documentation that it exists and the you need to explicitly grant this privilege to the appropriate authorization ID in order for the workload mapping to consider the workload definition.

In the vast majority of cases, a simple grant to PUBLIC allows all incoming connections to consider this workload. In fact, the OPM tool injects these automatically for you in any DDL script it produces.

Situations where this may not be appropriate include restrictive databases (i.e. where no PUBLIC grants are permitted by edict) or cases where a connection could be associated with more than one workload but there is a desire to prevent them from accessing some of the workload definitions (e.g. because those workloads have more access to database resources etc and are reserved for a specific subset of people).

Hope this helps someone avoid some frustration in the future!

Recent Stories
Verson 3.0.4 of IBM Graphical WLM tool now available in developerWorks

A quick summary of available Db2 controls for system resources

Managing resource consumption for multiple databases under the same DB2 instance