DB2 10.5 for Linux, UNIX, and Windows

DB2 pureScale Feature performance enhancement by using explicit hierarchical locking

With explicit hierarchical locking (EHL), table locks supersede row locks or page locks. EHL reduces network traffic to the caching facility (CF) in a DB2® pureScale® environment.

EHL makes parent-child relationship between resources that are known to the internal resource lock manager. This type of locking avoids global locking use when no resources are used among the different components of DB2 database server. EHL avoids most communication and data sharing memory usage for data tables, partitioned tables, and partitioned indexes. EHL helps improve performance by removing CF communication.

EHL is critical for the following types of workloads, as they are able to take advantage of this optimization:

Grid deployments, where each application has affinities to a single member and where most of its data access is only for these particular applications. In a database grid environment, a DB2 pureScale cluster has multiple databases but any single database is accessed only by a single member.
Partitioned or partitionable workloads where work are directed such that certain tables are only accessed by a single member. Directed access workloads where applications from one member do not access overlapping tables with another member.
One member configurations, or batch window workloads which use only a single member. A system is set up to have nightly batch processing with almost no OLTP activity. For these systems, all batch work on a table is sometimes performed by a single connection.

DB2 database sever automatically detects workloads in DB2 pureScale environments that benefit from EHL to help improve performance. EHL can be turned on using the opt_direct_wrkld database configuration parameter.