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.
UID
ibm11140724