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
How can I stay current on what fix packs are available for each Db2 release, what Hiper APARs might be out there, and if there are any security vulnerabilities that I should know about?

Things to consider when considering Db2 Native Encryption

An old Db2 Easter Egg: Setting the default isolation value for dynamic SQL