一个开发人员用toad跑了存储过程在数据库里同时有2个进程,然后把好多系统表都锁了,造成其它存储过程无法执行,求高手指点,造成锁的语句如下:
select current schema from sysibm.sysdummy1
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.B
SOURCE_NAME: SQLC2H23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2016-06-23-17.22.52.265150
EXPLAIN_REQUESTER: DB2INST1
Database Context:
----------------
Parallelism: Inter-Partition Parallelism
CPU Speed: 2.676618e-07
Comm Speed: 100
Buffer Pool size: 118880
Sort Heap size: 8192
Database Heap size: 20000
Lock List size: 50000
Maximum Lock List: 10
Average Applications: 1
Locks Available: 160000
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select current schema
from sysibm.sysdummy1
Optimized Statement:
-------------------
SELECT CURRENT SCHEMA
FROM
(SELECT 'Y'
FROM (VALUES 1) AS Q1) AS Q2
Access Plan:
-----------
Total Cost: 3.21194e-05
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
3.21194e-05
0
|
1
TABFNC: SYSIBM
GENROW
Extended Diagnostic Information:
--------------------------------
Diagnostic tables do not exist. No extended Diagnostic Information is available.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 3.21194e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 3.21194e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.40896e-05
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 0
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.0.11 : s150922
HEAPUSE : (Maximum Statement Heap Usage)
96 Pages
PREPNODE: (Prepare Node Number)
0
PREPTIME: (Statement prepare time)
64 milliseconds
STMTHEAP: (Statement heap size)
8192
Input Streams:
-------------
2) From Operator #2
Estimated number of rows: 1
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.$C0
Partition Column Names:
----------------------
+NONE
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 3.21194e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 3.21194e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.40896e-05
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 0
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
1) From Object SYSIBM.GENROW
Estimated number of rows: 1
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 0
Subquery predicate ID: Not Applicable
Partition Column Names:
----------------------
+NONE
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2009
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool
******************** EXPLAIN INSTANCE ********************
DB2_VERSION: 09.07.B
SOURCE_NAME: SQLC2H23
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2016-06-23-17.22.52.265150
EXPLAIN_REQUESTER: DB2INST1
Database Context:
----------------
Parallelism: Inter-Partition Parallelism
CPU Speed: 2.676618e-07
Comm Speed: 100
Buffer Pool size: 118880
Sort Heap size: 8192
Database Heap size: 20000
Lock List size: 50000
Maximum Lock List: 10
Average Applications: 1
Locks Available: 160000
Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability
---------------- STATEMENT 1 SECTION 201 ----------------
QUERYNO: 1
QUERYTAG: CLP
Statement Type: Select
Updatable: No
Deletable: No
Query Degree: 1
Original Statement:
------------------
select current schema
from sysibm.sysdummy1
Optimized Statement:
-------------------
SELECT CURRENT SCHEMA
FROM
(SELECT 'Y'
FROM (VALUES 1) AS Q1) AS Q2
Access Plan:
-----------
Total Cost: 3.21194e-05
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
1
TBSCAN
( 2)
3.21194e-05
0
|
1
TABFNC: SYSIBM
GENROW
Extended Diagnostic Information:
--------------------------------
Diagnostic tables do not exist. No extended Diagnostic Information is available.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 3.21194e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 3.21194e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.40896e-05
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 0
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v9.7.0.11 : s150922
HEAPUSE : (Maximum Statement Heap Usage)
96 Pages
PREPNODE: (Prepare Node Number)
0
PREPTIME: (Statement prepare time)
64 milliseconds
STMTHEAP: (Statement heap size)
8192
Input Streams:
-------------
2) From Operator #2
Estimated number of rows: 1
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.$C0
Partition Column Names:
----------------------
+NONE
2) TBSCAN: (Table Scan)
Cumulative Total Cost: 3.21194e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 3.21194e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.40896e-05
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 0
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
SHARE (CS/RS)
SCANDIR : (Scan Direction)
FORWARD
SPEED : (Assumed speed of scan, in sharing structures)
FAST
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY
THROTTLE: (Scan may be throttled, for scan sharing)
TRUE
VISIBLE : (May be included in scan sharing structures)
TRUE
WRAPPING: (Scan may start anywhere and wrap)
TRUE
Input Streams:
-------------
1) From Object SYSIBM.GENROW
Estimated number of rows: 1
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 0
Subquery predicate ID: Not Applicable
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
2) To Operator #1
Estimated number of rows: 1
Partition Map ID: -100
Partitioning: (COOR )
Coordinator Partition
Number of columns: 1
Subquery predicate ID: Not Applicable
Column Names:
------------
+Q3.$C0
Partition Column Names:
----------------------
+NONE
Objects Used in Access Plan:
---------------------------
Schema: SYSIBM
Name: SYSDUMMY1
Type: View (reference only)
Schema: SYSIBM
Name: GENROW
Type: Table Function
Time of creation:
Last statistics update:
Number of columns: 1
Number of rows: 1
Width of rows: 11
Number of buffer pool pages: -1
Distinct row values: No
Source for statistics: Single Node