IBM Support

Why there are exclusive locks on system tables after changing value of INTRA_PARALLEL

Technical Blog Post


Abstract

Why there are exclusive locks on system tables after changing value of INTRA_PARALLEL

Body

The Problem:

After changing INTRA_PARALLEL from YES to NO and restarting the server, execlusive locks are observed, like:

lock_mode                lock_type  schema.table       #locks
-------------------------------------------------------------
Intention Exclusive Lock TABLE_LOCK SYSIBM.SYSPLAN          1
Intention Exclusive Lock TABLE_LOCK SYSIBM.SYSPLANDEP       1
Intention Exclusive Lock TABLE_LOCK SYSIBM.SYSSECTION       1
Exclusive Lock           ROW_LOCK   SYSIBM.SYSPLANDEP      12
Exclusive Lock           ROW_LOCK   SYSIBM.SYSSECTION       8
Exclusive Lock           ROW_LOCK   SYSIBM.SYSPLAN          1

In some cases, this would cause lock contention and impacts performance. What's the reason?


The Answer:

Changing the parameter value intra_parallel resulted in implicit packages rebound at the first invoking if no explicit happened before that. Note that this is expected behavior. Refer to:
http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.1.0/com.ibm.db2.luw.admin.config.doc/doc/r0000146.html

No matter it is implicit or explicit, rebind of packages caused exclusive locks on  system tables such as SYSIBM.SYSPLAN, SYSIBM.SYSSECTION, SYSIBM.SYSPLANDEP. This is also expected behavior.

So, considering you changed INTRA_PARALLEL from YES to NO, and you have an application which running a long transaction, it calls a package which was bound with DEGREE=ANY and INTRA_PARALLEL=Y, so implicit rebind happens and exclusive locks on system tables are acquired and never be released until it commit the long transaction, so lock wait happens if another application calls the same package.

This behavior can be reproduced easily:

db2 update db cfg using DFT_DEGREE ANY    (set this to ANY just ensure DEGREE=ANY in new created package)
db2 update dbm cfg using INTRA_PARALLEL yes
db2stop force
db2start
 
$ db2  "CREATE OR REPLACE PROCEDURE LOAD_DB2T ()
      LANGUAGE SQL
    BEGIN
      DECLARE v1 varchar(10) DEFAULT '11223344'; --
      DECLARE v2 BIGINT DEFAULT 0;--
      DECLARE cs1 CURSOR FOR SELECT C1,C2 FROM DB2T; --
      open cs1; --
      FETCH cs1 INTO v1,v2; --
      CLOSE cs1; --
    END"
 
db2 "select substr(PKGSCHEMA,1,20), substr(PKGNAME,1,20), valid, CREATE_TIME,EXPLICIT_BIND_TIME,LAST_BIND_TIME,ALTER_TIME,DEGREE,INTRA_PARALLEL,VALIDATE from syscat.packages where PKGSCHEMA='DB2V101'"
 
1                    2                    VALID CREATE_TIME                EXPLICIT_BIND_TIME         LAST_BIND_TIME ALTER_TIME                 DEGREE INTRA_PARALLEL VALIDATE
-------------------- -------------------- ----- -------------------------- -------------------------- -------------------------- -------------------------- ------ -------------- --------
DB2V101              P1721355027          Y     2016-02-22-15.18.56.806779 2016-02-22-15.18.56.806779 2016-02-22-15.18.56.806779 2016-02-22-15.18.56.806779 ANY    Y              B
 
  1 record(s) selected.
 
db2 update db cfg using DFT_DEGREE 1
db2 update dbm cfg using INTRA_PARALLEL no
db2stop force
db2start

 

$ db2 "select substr(PKGSCHEMA,1,20), substr(PKGNAME,1,20), valid,CREATE_TIME,EXPLICIT_BIND_TIME,LAST_BIND_TIME,ALTER_TIME,DEGREE,INTRA_PARALLEL,VALIDATE from syscat.packages where PKGSCHEMA='DB2V101'"
 
1                    2                    VALID CREATE_TIME                EXPLICIT_BIND_TIME         LAST_BIND_TIME ALTER_TIME                 DEGREE INTRA_PARALLEL VALIDATE
-------------------- -------------------- ----- -------------------------- -------------------------- -------------------------- -------------------------- ------ -------------- --------
DB2V101              P1721355027          Y     2016-02-22-15.18.56.806779 2016-02-22-15.18.56.806779 2016-02-22-15.18.56.806779 2016-02-22-15.18.56.806779 ANY    Y              B
 
  1 record(s) selected.

$ db2 +c "call LOAD_DB2T()"
 
  Return Status = 0
 
$ db2pd -d sample -lock show detail
 
Database Member 0 -- Database SAMPLE -- Active -- Up 0 days 00:01:12 -- Date 2016-02-22-15.22.34.723734
 
Locks:
Address            TranHdl    Lockname                   Type       Mode Sts Owner      Dur HoldCount  Att        ReleaseFlg rrIID
0x07000000712D4200 3          0000000E00000000000C001752 RowLock    ..X  G   3          1   0          0x00200020 0x40000000 0 0000000E00000000000C001752 SQLP_RECORD (obj={0;14}, rid=d(0;12;23), x00000000000C0017)
...
0x07000000712D3F80 3          0000000F000000000042000D52 RowLock    ..X  G   3          2   0          0x00200020 0x40000000 0 0000000F000000000042000D52 SQLP_RECORD (obj={0;15}, rid=d(0;66;13), x000000000042000D)
...
0x07000000712D4400 3          0000000E00000000000C001552 RowLock    ..X  G   3          1   0          0x00200008 0x40000000 0 0000000E00000000000C001552 SQLP_RECORD (obj={0;14}, rid=d(0;12;21), x00000000000C0015)
...


$ db2 "select substr(PKGSCHEMA,1,20), substr(PKGNAME,1,20), valid,CREATE_TIME,EXPLICIT_BIND_TIME,LAST_BIND_TIME,ALTER_TIME,DEGREE,INTRA_PARALLEL,VALIDATE from syscat.packages where PKGSCHEMA='DB2V101'"
 
1                    2                    VALID CREATE_TIME                EXPLICIT_BIND_TIME         LAST_BIND_TIME ALTER_TIME                 DEGREE INTRA_PARALLEL VALIDATE
-------------------- -------------------- ----- -------------------------- -------------------------- -------------------------- -------------------------- ------ -------------- --------
DB2V101              P1721355027          Y     2016-02-22-15.18.56.806779 2016-02-22-15.18.56.806779 2016-02-22-15.22.28.931920 2016-02-22-15.22.28.931920 ANY    F              B
 
  1 record(s) selected.
 
==> implicit rebind happened as LAST_BIND_TIME changed.
==> we can see the package was implicitly rebind on 2016-02-22-15.22.28.931920 is with DEGREE=ANY and INTRA_PARALLEL=F
 
The solution/preventive:

To prevent implicit packages rebound from impacting performance, rebind the packages explicitly after changing INTRA_PARALLEL.

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140724