Priority Settings in db2

 

Hi DBAs, how are you? Let’s talk about how to reserve resources for the db2 utilities so they do not compete (directly) with user transactions by setting the priority. Enjoy.

Priority of execution

& util_impact_lim

This parameter allows the database administrator (DBA) to limit the degradation of a utility throttled in the workload for performance.

This type of configuration is done at the bank manager level and applies to:

 

  • Database server with local clients
  • Database server with local and remote clients
  • Partitioned database server with local and remote clients

This parameter is configurable online, that is, it is not necessary to perform a restart database because its propagation is IMEDIATE (sooo good). This parameter can be varied from 1 to 100 and the standard adopted by IBM is 10, where the unit of measure is the percentage of impact allowed in the workload.

If performance degradation is limited, the DBA can then run on-line utilities during critical periods of production, and be assured that the performance impact on production work will be within acceptable limits.

For example, if a DBA specifies util_impact_lim as “10” you can expect that running a backup will have no impact on the workload greater than 10%.

If util_impact_lim is set to 100, all connections will be throttled because all machine features are reserved for utilities. In this case, the utilities may have an arbitrary (and undesirable) impact on the workload.

If util_impact_lim is set to a value that is less than 100, it is possible to invoke the utilities in the “throttled mode” where you will tell the DBMS what the execution priority of the utility is. To run in throttled mode, a utility must also be invoked with a nonzero priority.

 

Recommendation

Most users will benefit from creating util_impact_lim for a low value (for example, between 1 and 10).

A strangled utility usually take longer to complete than an unthrottled utility. If you find that a utility is running for an excessively long time, increase the value of util_impact_lim or disable optimization altogether by setting util_impact_lim to 100.

Do you see any errors? Do you have any suggestions? COMMENT THE POST !!!

 

Thiago E. de Albuquerque
DBA DB2 LUW in Vila Velha -ES, Brazil
“In practice, the theories are other …”

Linkedin

Thiago Escodino
Author: Thiago Escodino

Hello everyone... My name is Thiago Escodino and I am IBM DB2 database administrator since 2012. I have knowledge in IBM DataStudio, PowerDesigner, linux etc.

Leave a Reply

Your email address will not be published. Required fields are marked *