Explicit Hierarchical Locking eliminates data sharing (CF communication) cost for tables, range partitioned tables and partitioned indexes that are accessed only by a single member.

You can enable EHL by updating the database configuration parameter opt_direct_wrkld.

UPDATE DB CFG FOR <dbname> USING opt_direct_wrkld YES;

This parameter can be set to AUTOMATIC, and when it is AUTOMATIC, EHL will be activated when both cf_gbp_sz and cf_lock_sz are set to AUTOMATIC.

EHL States:

  • SHARED: Full shared across all the members in the cluster
  • BECOMING_NOT_SHARED: Transitioning from SHARED to NOT_SHARED
  • NOT_SHARED: All access is limited to one member in the cluster
  • BECOMING_SHARED: Transitioning from NOT_SHARED to SHARED

EHL State Changes:

Entering into NOT_SHARED state

  • An INSERT, UPDATE, DELETE and SELECT operation on a table
  • A data partition in a range partition table can enter NOT_SHARED state while the logical table is unaffected
  • Index can enter this state when a non-partitioned index is created

Exiting NOT_SHARED state

  • Table access from other member or members
  • Drop table or database deactivation
  • Partition ATTACH/DETACH operation on a partitioned table

Locking Behavior During NOT_SHARED to SHARED State Change:

The table lock is held in super exclusive mode (Z) until all the page locks and row locks are registered in Global Lock Manager (GLM) and all the changed pages are written to Group Buffer pool (GBP).

Monitoring Elements:

  • data_sharing_state – We have already discussed
  • data_sharing_state_change_time – Timestamp of the last state change
  • data_sharing_remote_lockwait_count – Number of times application waited on a table transition from NOT_SHARED to SHARED
  • data_sharing_remote_lockwait_time – Application wait time in milliseconds on a table during the transition from NOT_SHARED to SHARED

EHL Monitoring Table Functions:

  • SYSPROC.MON_GET_TABLE ()
  • SYSPROC.MON_GET_DATABASE()
  • SYSPROC.MON_GET_APPL_LOCKWAIT()
SELECT   VARCHAR(TABNAME,40) AS TABNAME,
         MEMBER,
         DATA_SHARING_STATE AS DS_STATE,
         DATA_SHARING_STATE_CHANGE_TIME AS DSC_TIME,
         DATA_SHARING_REMOTE_LOCKWAIT_COUNT AS DSRL_COUNT,
         DATA_SHARING_REMOTE_LOCKWAIT_TIME AS DSRL_TIME_MS
FROM TABLE(MON_GET_TABLE('<SCHEMA>','<TABLE>',-2)) 
ORDER BY MEMBER ASC;