Use cases for Explicit Hierarchical Locking (EHL)

Explicit hierarchical locking (EHL) for the IBM® Db2® pureScale® Feature is designed to improve performance by avoiding CF communications for tables that are accessed from only one member. When EHL is enabled, if only one member accesses a data table, partitioned table, or partitioned index then the table transition to the NOT_SHARED state.

Transitioning to this state is an ideal scenario for data tables, partitioned tables, or partitioned indexes. Grid deployments, partitioned or partitionable workloads with directed access, or batch window workloads that use only a single member are access patterns that can benefit from using EHL. The Db2 database server automatically detects these access patterns and transition applicable tables into the NOT_SHARED state.

Example 1: When the following sample SQL statements are issued, the Db2 database server detects that tables tab1, tab2, tab3, and tab4 are accessed by only one member and would benefit from EHL. These tables transition into the NOT_SHARED state.
Member 1:
db2 -v "select * from tab1"
db2 -v "delete from tab3 where col1 > 100"

Member 2:
db2 -v "insert into tab2 values (20,20)"
db2 -v "select * from tab4"

To ensure that tables remain in the NOT_SHARED state, tune your applications or use EHL for workloads where only a single member accesses a data table, partitioned table, or partitioned index.

Example 2: In the following example, the Db2 database server detects that the EHL optimization does not apply. Multiple members are all attempting to access the same table tab1. The table does not transition to the NOT_SHARED state. 
Member 1:
db2 -v "select * from tab1"

Member 2:
db2 -v "insert into tab1 values (20,20)"

Member 1:
db2 -v "delete from tab1 where col1 > 100"

Member 2:
db2 -v "select * from tab1"

Use MON_GET_TABLE to monitor whether or not tables transition the NOT_SHARED state.

Example 3: Running extent reclaim on a remote member. Assume there is a table named T5_01A, which is currently in the NOT_SHARED state on Member 1:
  • M1: db2 "connect to testdb"
  • M1: db2 "select count(*) from T5_01A"
  • M1: db2 "select data_sharing_state from table(mon_get_table(null, null, -1)) where tabname='T5_01A'"

DATA_SHARING_STATE
-------------------
NOT_SHARED

Extent reclaim is started at Member 0. The operation will need to move an extent which belongs to the T5_01A table.

  • M0: db2 "alter tablespace TS_01A reduce max"

As a result, the table will become shared.

  • M1: db2 "select data_sharing_state from table(mon_get_table(null, null, -1)) where tabname='T5_01A'"

DATA_SHARING_STATE
-------------------
SHARED
Example 4: Running extent reclaim locally. Assume there is a table named T5_01A, which is currently in the NOT_SHARED state on Member 1:
  • M1: db2 "connect to testdb"
  • M1: db2 "select count(*) from T5_01A"
  • M1: db2 "select data_sharing_state from table(mon_get_table(null, null, -1)) where tabname='T5_01A'"

DATA_SHARING_STATE
-------------------
NOT_SHARED

Extent reclaim is started at Member 1. The operation will need to move an extent which belongs to the T5_01A table.

  • M1: db2 "alter tablespace TS_01A reduce max"

The table state will remain unchanged.

  • M1: db2 "select data_sharing_state from table(mon_get_table(null, null, -1)) where tabname='T5_01A'"

DATA_SHARING_STATE
-------------------
NOT_SHARED