50 DB2 Nuggets #41 Tech Tip - Want to know more on SQL4728W & what Agent Priority to be set ?

Body

SQL4728W :

A priority setting was assigned to a service class that is higher than the priority setting of the default system service class SYSDEFAULTSYSTEMCLASS and this might negatively impact performance.

To ensure that system work can take precedence over user work, the priority settings of the default system service class SYSDEFAULTSYSTEMCLASS should always be higher than the priorities that were set for all other service classes. Failure to assign higher priority settings to the default system service class can result in a negative impact on performance because system-type activities run in the default system service class.

User Response : Raise the priority setting of the default system service class, or lower the priority setting of other service classes that have a higher priority setting than the default system service class.

Looking for what priority to be set for ETL jobs when you encounter warning messages SQL4728W ?

This sqlcode says that it is adjusting the agent priority of a service class higher than the one used for the system work.

Now if you would like to know what agent priority to be used, then we need to ensure that SYSDEFAULTSYSTEMCLASS is always the highest priority.

Also it is important to know why the agent priority is being used unless you are on a platform other then AIX or LINUX and on a release below 10.1

Agent priority and OS WLM integration are orthogonal ways to provide differentiation to work in different service classes, they are not connected to each other. In fact, DB2 will ignore agent priority settings if you are using OS WLM integration.

Agent priority biases CPU processing which can have definite benefits if the competing workloads are CPU-heavy. But it does not explicitly allocate or manage resources between them. So we cannot put a cap on consumption by any of the competing workloads and  if the workloads are not equally "hungry" for CPU, agent priority will appear to be ineffective in those cases where the lower priority work makes more requests to consume CPU than the higher priority work.

So, if we have work of equal CPU usage (e.g.both have the same "appetite") and we just want to bias the system versus explicitly throttle or control CPU consumption, agent priority will work effectively.

Also we could look at imposing concurrency controls on  ad hoc and standard reports (perhaps different ones for each) in order to limit their consumption .
In Db2 V 10.1 this would then be expanded to also consider DB2 WLM CPU Dispatcher.