473,735 Members | 11,866 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Regarding concurrency MERGE x UPDATE

Hi all,

DB2 V8 LUW FP 15

There is a table T (ID varchar (24), ABC timestamp). ID is PK.

Our application needs to frequently update T with a new value for ABC.

update T set ABC=? where ID = ?

However, a condition was found where the application was trying to
update T without inserting a row first. We decided to change the
simple Update statement to a MERGE.

MERGE into T using
(
values ('xyz','a timestamp')
) as indata (ID, ABC)
ON (T.ID = indata.ID)
when matched then update set ABC = indata.abc
when not matched then insert (ID,ABC) values (indata.ID, indata.ABC)

We solved the first problem, but it seems like we have introduced a
concurrency problem using Merge. All concurrent applications seems
slower when trying to update table T. I have taken snapshots and
several merges run at the same time. We did not have this issue
running Updates.

What performance penalty should I expect when using merge instead of
update ? I know merge runs on CS isolation, but is there anything I am
missing here ?

Thanks,

Michel
Aug 16 '08 #1
3 3964
Michel Esber wrote:
Hi all,

DB2 V8 LUW FP 15

There is a table T (ID varchar (24), ABC timestamp). ID is PK.

Our application needs to frequently update T with a new value for ABC.

update T set ABC=? where ID = ?

However, a condition was found where the application was trying to
update T without inserting a row first. We decided to change the
simple Update statement to a MERGE.

MERGE into T using
(
values ('xyz','a timestamp')
) as indata (ID, ABC)
ON (T.ID = indata.ID)
when matched then update set ABC = indata.abc
when not matched then insert (ID,ABC) values (indata.ID, indata.ABC)

We solved the first problem, but it seems like we have introduced a
concurrency problem using Merge. All concurrent applications seems
slower when trying to update table T. I have taken snapshots and
several merges run at the same time. We did not have this issue
running Updates.

What performance penalty should I expect when using merge instead of
update ? I know merge runs on CS isolation, but is there anything I am
missing here ?
What plan has been chosen? In an OLTP environment you want MERGE to use
a nested loop.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 17 '08 #2
What plan has been chosen? In an OLTP environment you want MERGE to use
a nested loop.
Here is the complete plan:

*************** ***** EXPLAIN INSTANCE *************** *****

DB2_VERSION: 08.02.8
SOURCE_NAME: SQLC2E07
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2008-08-17-13.39.54.252932
EXPLAIN_REQUEST ER: DB2INST1

Database Context:
----------------
Parallelism: None
CPU Speed: 3.778754e-07
Comm Speed: 0
Buffer Pool size: 139500
Sort Heap size: 2048
Database Heap size: 10000
Lock List size: 2048
Maximum Lock List: 15
Average Applications: 50
Locks Available: 31334

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Unrecognized
Updatable: Not Applicable
Deletable: Not Applicable
Query Degree: 1

Original Statement:
------------------
MERGE INTO CAD.TBL_COLLECT _MACHINE_TIME AS E USING (VALUES (
'00E01838AD5A10 0463DB113E', '2008-08-17-14.01.00', CURRENT
TIMESTAMP ,
'Y' , CURRENT TIMESTAMP , CURRENT TIMESTAMP)) INDATA
(MACHINE_ID,
COLLECT_TIME, INSERT_TIME, COLLECT_NOVA, INSTALLED_DATE,
LAST_SCAN_TIME) ON (E.MACHINE_ID = INDATA.MACHINE_ ID)
WHEN MATCHED AND E.COLLECT_TIME < INDATA.COLLECT_ TIME
THEN
UPDATE SET COLLECT_TIME = INDATA.COLLECT_ TIME, INSERT_TIME =
INDATA.INSERT_T IME, COLLECT_ANTERIO R = E.COLLECT_TIME,
COLLECT_NOVA =
INDATA.COLLECT_ NOVA, LAST_SCAN_TIME = INDATA.LAST_SCA N_TIME
WHEN NOT MATCHED
THEN
INSERT (MACHINE_ID, COLLECT_TIME, INSERT_TIME, COLLECT_NOVA,
INSTALLED_DATE,
LAST_SCAN_TIME) VALUES (INDATA.MACHINE _ID,
INDATA.COLLECT_ TIME,
INDATA.INSERT_T IME, INDATA.COLLECT_ NOVA,
INDATA.INSTALLE D_DATE,
INDATA.LAST_SCA N_TIME)
Optimized Statement:
-------------------
INSERT INTO CAD.TBL_COLLECT _MACHINE_TIME AS Q15
UPDATE CAD.TBL_COLLECT _MACHINE_TIME AS Q1 SET (Q1.LAST_SCAN_T IME,
Q1.COLLECT_NOVA , Q1.COLLECT_ANTE RIOR, Q1.INSERT_TIME,
Q1.COLLECT_TIME ) =
SELECT Q12.$C9, Q12.$C8, Q12.$C7, Q12.$C6, Q12.$C5, Q12.$C0,
Q12.$C1,
CURRENT TIMESTAMP, CURRENT TIMESTAMP, Q12.$C4,
CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 < TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END
FROM
(SELECT Q4.COLLECT_TIME , Q3.$C1, Q3.$C3, Q3.$C0
FROM
(SELECT '00E01838AD5A10 0463DB113E', '2008-08-17-14.01.00',
CURRENT
TIMESTAMP, 'Y', CURRENT TIMESTAMP, CURRENT TIMESTAMP
FROM (VALUES 1) AS Q2) AS Q3 LEFT OUTER JOIN
CAD.TBL_COLLECT _MACHINE_TIME AS Q4 ON (Q4.MACHINE_ID =
'00E01838AD5A10 0463DB113E')) AS Q5,
(SELECT NULL, NULL, NULL, NULL, NULL, NULL, $INTERNAL_FUNC$ (),
TIMESTAMP(Q5.$C 1), CURRENT TIMESTAMP, Q5.$C3
FROM
(SELECT 1
FROM (VALUES 1) AS Q6) AS Q7
WHERE (CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 < TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 2)
UNION ALL
SELECT $INTERNAL_FUNC$ (), Q5.$C4, Q5.$C0, NULL, NULL, NULL,
NULL, NULL
FROM
(SELECT 1
FROM (VALUES 1) AS Q9) AS Q10
WHERE (CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 < TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 1)) AS Q12

Access Plan:
-----------
Total Cost: 28.7908
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
0.08
INSERT
( 2)
28.7908
3.08
/------+-----\
0.08
8897
UPDATE TABLE:
CAD
( 3)
TBL_COLLECT_MAC HINE_TIME
19.3859
2.08
/------+-----\
0.08 8897
NLJOIN TABLE:
CAD
( 4)
TBL_COLLECT_MAC HINE_TIME
18.6333
2
/---------------------+--------------------\
1
0.08
NLJOIN
UNION
( 5)
( 9)
18.6304
0.00273038
2 0
/--------+-------\ /------
+-----\
1 1
0.04 0.04
TBSCAN FETCH
FILTER FILTER
( 6) ( 7)
( 10) ( 12)
4.5345e-05 18.6303
0.00119031 0.00119031
0 2
0 0
| /------+-----\
| |
1 1 8897
1 1
TABFNC: SYSIBM IXSCAN TABLE: CAD
TBSCAN TBSCAN
GENROW ( 8) TBL_COLLECT_MAC HINE_TIME
( 11) ( 13)
9.22472
4.5345e-05 4.5345e-05
1
0 0
|
| |
8897
1 1
INDEX: SYSIBM TABFNC: SYSIBM
TABFNC: SYSIBM
SQL011003151006 350
GENROW GENROW


Extended Diagnostic Information:
--------------------------------

No extended Diagnostic Information for this statment.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 28.7908
Cumulative CPU Cost: 102596
Cumulative I/O Cost: 3.08
Cumulative Re-Total Cost: 10.1675
Cumulative Re-CPU Cost: 41135.1
Cumulative Re-I/O Cost: 1.08
Cumulative First Row Cost: 28.7908
Estimated Bufferpool Buffers: 3.08

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.2.136 : special_19546
HEAPUSE : (Maximum Statement Heap Usage)
116 Pages
STMTHEAP: (Statement heap size)
4096

Input Streams:
-------------
19) From Operator #2

Estimated number of rows: 0.08
Number of columns: 0
Subquery predicate ID: Not
Applicable
2) INSERT: (Insert)
Cumulative Total Cost: 28.7908
Cumulative CPU Cost: 102596
Cumulative I/O Cost: 3.08
Cumulative Re-Total Cost: 10.1675
Cumulative Re-CPU Cost: 41135.1
Cumulative Re-I/O Cost: 1.08
Cumulative First Row Cost: 28.7908
Estimated Bufferpool Buffers: 3.08

Input Streams:
-------------
17) From Operator #3

Estimated number of rows: 0.08
Number of columns: 8
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q14.$C7+Q14.$C 0+Q14.$C3+Q14.$ C4+Q14.$C1
+Q14.$C5+Q14.$C 2+Q14.$C6
Output Streams:
--------------
18) To Object CAD.TBL_COLLECT _MACHINE_TIME

Estimated number of rows: 8897
Number of columns: 7
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q15.COLLECT_AN TERIOR
+Q15.LAST_SCAN_ TIME
+Q15.INSTALLED_ DATE+Q15.COLLEC T_NOVA
+Q15.INSERT_TIM E+Q15.COLLECT_T IME
+Q15.MACHINE_ID

19) To Operator #1

Estimated number of rows: 0.08
Number of columns: 0
Subquery predicate ID: Not
Applicable
3) UPDATE: (Update)
Cumulative Total Cost: 19.3859
Cumulative CPU Cost: 89596.1
Cumulative I/O Cost: 2.08
Cumulative Re-Total Cost: 0.762632
Cumulative Re-CPU Cost: 28135.1
Cumulative Re-I/O Cost: 0.08
Cumulative First Row Cost: 19.3859
Estimated Bufferpool Buffers: 2.08

Input Streams:
-------------
15) From Operator #4

Estimated number of rows: 0.08
Number of columns: 13
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q13.$C12+Q13.$ C0+Q13.$C1+Q13. $C2+Q13.$C3

+Q13.$C4+Q13.$C 5+Q13.$C6+Q13.$ C7+Q13.$C8
+Q13.$C9+Q13.$C 10+Q13.$C11
Output Streams:
--------------
16) To Object CAD.TBL_COLLECT _MACHINE_TIME

Estimated number of rows: 8897
Number of columns: 6
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q1.$RID$+Q1.LA ST_SCAN_TIME
+Q1.COLLECT_NOV A
+Q1.COLLECT_ANT ERIOR+Q1.INSERT _TIME
+Q1.COLLECT_TIM E

17) To Operator #2

Estimated number of rows: 0.08
Number of columns: 8
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q14.$C7+Q14.$C 0+Q14.$C3+Q14.$ C4+Q14.$C1
+Q14.$C5+Q14.$C 2+Q14.$C6
4) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 18.6333
Cumulative CPU Cost: 88075.5
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.010057
Cumulative Re-CPU Cost: 26614.5
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 18.6333
Estimated Bufferpool Buffers: 2

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE

Input Streams:
-------------
7) From Operator #5

Estimated number of rows: 1
Number of columns: 5
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q5.COLLECT_NOV A+Q5.COLLECT_TI ME
+Q5.MACHINE_ID
+Q5.COLLECT_TIM E+Q5.$C4

14) From Operator #9

Estimated number of rows: 0.08
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q12.$C4+Q12.$C 3+Q12.$C2+Q12.$ C1+Q12.$C0

+Q12.$C5+Q12.$C 6+Q12.$C7+Q12.$ C8+Q12.$C9
Output Streams:
--------------
15) To Operator #3

Estimated number of rows: 0.08
Number of columns: 13
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q13.$C12+Q13.$ C0+Q13.$C1+Q13. $C2+Q13.$C3

+Q13.$C4+Q13.$C 5+Q13.$C6+Q13.$ C7+Q13.$C8
+Q13.$C9+Q13.$C 10+Q13.$C11
5) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 18.6304
Cumulative CPU Cost: 80369.3
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.007145
Cumulative Re-CPU Cost: 18908.3
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 18.63
Estimated Bufferpool Buffers: 2

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
JN INPUT: (Join input leg)
OUTER
OUTERJN : (Outer Join type)
LEFT

Input Streams:
-------------
2) From Operator #6

Estimated number of rows: 1
Number of columns: 3
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q3.$C3+Q3.$C1+ Q3.$C0

6) From Operator #7

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.$RID$+Q4.CO LLECT_TIME
Output Streams:
--------------
7) To Operator #4

Estimated number of rows: 1
Number of columns: 5
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q5.COLLECT_NOV A+Q5.COLLECT_TI ME
+Q5.MACHINE_ID
+Q5.COLLECT_TIM E+Q5.$C4
6) TBSCAN: (Table Scan)
Cumulative Total Cost: 4.5345e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 4.5345e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 3.40088e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
1) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
2) To Operator #5

Estimated number of rows: 1
Number of columns: 3
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q3.$C3+Q3.$C1+ Q3.$C0
7) FETCH : (Fetch)
Cumulative Total Cost: 18.6303
Cumulative CPU Cost: 80249.3
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00709965
Cumulative Re-CPU Cost: 18788.3
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 18.63
Estimated Bufferpool Buffers: 3

Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAXPAGES: (Maximum pages for prefetch)
1
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
EXCLUSIVE
TABLOCK : (Table Lock intent)
INTENT EXCLUSIVE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
4) From Operator #8

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.MACHINE_ID( A)+Q4.$RID$

5) From Object CAD.TBL_COLLECT _MACHINE_TIME

Estimated number of rows: 8897
Number of columns: 5
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.LAST_SCAN_T IME+Q4.COLLECT_ NOVA
+Q4.COLLECT_ANT ERIOR+Q4.INSERT _TIME
+Q4.COLLECT_TIM E
Output Streams:
--------------
6) To Operator #5

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.$RID$+Q4.CO LLECT_TIME
8) IXSCAN: (Index Scan)
Cumulative Total Cost: 9.22472
Cumulative CPU Cost: 65429.3
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00641192
Cumulative Re-CPU Cost: 16968.3
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 9.22472
Estimated Bufferpool Buffers: 2

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
EXCLUSIVE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT EXCLUSIVE

Predicates:
----------
4) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000112397

Predicate Text:
--------------
(Q4.MACHINE_ID = '00E01838AD5A10 0463DB113E')

4) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000112397

Predicate Text:
--------------
(Q4.MACHINE_ID = '00E01838AD5A10 0463DB113E')
Input Streams:
-------------
3) From Object SYSIBM.SQL01100 3151006350

Estimated number of rows: 8897
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.MACHINE_ID( A)+Q4.$RID$
Output Streams:
--------------
4) To Operator #7

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.MACHINE_ID( A)+Q4.$RID$
9) UNION : (Union)
Cumulative Total Cost: 0.00273038
Cumulative CPU Cost: 7225.6
--More--Executing Connect Reset -- Connect Reset was Successful.
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00273038
Cumulative Re-CPU Cost: 7225.6
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00266259
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
INNER

Input Streams:
-------------
10) From Operator #10

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q8.$C0+Q8.$C1+ Q8.$C2+Q8.$C3+Q 8.$C4
+Q8.MACHINE_ID
+Q8.$C7+Q8.$C6+ Q8.$C8+Q8.$C5

13) From Operator #12

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q11.$C5+Q11.$C 6+Q11.$C7+Q11.$ C8+Q11.$C9
+Q11.COLLECT_TI ME
+Q11.$C3+Q11.$C 2+Q11.$C1
+Q11.$C0
Output Streams:
--------------
14) To Operator #4

Estimated number of rows: 0.08
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q12.$C4+Q12.$C 3+Q12.$C2+Q12.$ C1+Q12.$C0

+Q12.$C5+Q12.$C 6+Q12.$C7+Q12.$ C8+Q12.$C9
10) FILTER: (Filter)
Cumulative Total Cost: 0.00119031
Cumulative CPU Cost: 3150
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00119031
Cumulative Re-CPU Cost: 3150
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00119031
Estimated Bufferpool Buffers: 0

Predicates:
----------
6) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(
CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 <
TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 2)
Input Streams:
-------------
9) From Operator #11

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
10) To Operator #9

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q8.$C0+Q8.$C1+ Q8.$C2+Q8.$C3+Q 8.$C4
+Q8.MACHINE_ID
+Q8.$C7+Q8.$C6+ Q8.$C8+Q8.$C5
11) TBSCAN: (Table Scan)
Cumulative Total Cost: 4.5345e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 4.5345e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 3.40088e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
8) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
9) To Operator #10

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
12) FILTER: (Filter)
Cumulative Total Cost: 0.00119031
Cumulative CPU Cost: 3150
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00119031
Cumulative Re-CPU Cost: 3150
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00119031
Estimated Bufferpool Buffers: 0

Predicates:
----------
8) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(
CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 <
TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 1)
Input Streams:
-------------
12) From Operator #13

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
13) To Operator #9

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q11.$C5+Q11.$C 6+Q11.$C7+Q11.$ C8+Q11.$C9
+Q11.COLLECT_TI ME
+Q11.$C3+Q11.$C 2+Q11.$C1
+Q11.$C0
13) TBSCAN: (Table Scan)
Cumulative Total Cost: 4.5345e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 4.5345e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 3.40088e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
11) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
12) To Operator #12

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Objects Used in Access Plan:
---------------------------

Schema: SYSIBM
Name: SQL011003151006 350
Type: Index
Time of creation:
2001-10-03-15.10.06.339708
Last statistics update:
2008-05-03-04.31.42.454971
Number of columns: 1
Number of rows: 8897
Width of rows: -1
Number of buffer pool pages: 51
Distinct row values: Yes
Tablespace name: CADIDX
Tablespace overhead: 9.000000
Tablespace transfer rate: 0.200000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Index clustering statistic: 100.000000
Index leaf pages: 50
Index tree levels: 2
Index full key cardinality: 8897
Index first key cardinality: 8897
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 0
Index page density: 0
Index avg sequential pages: 0
Index avg gap between sequences:0
Index avg random pages: 0
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 8897
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: CAD
Base Table Name:
TBL_COLLECT_MAC HINE_TIME
Columns in index:
MACHINE_ID

Schema: CAD
Name: TBL_COLLECT_MAC HINE_TIME
Type: Table
Time of creation:
2001-10-03-15.10.04.108739
Last statistics update:
2008-05-03-04.31.42.454971
Number of columns: 7
Number of rows: 8897
Width of rows: 88
Number of buffer pool pages: 51
Distinct row values: No
Tablespace name:
CAD
Tablespace overhead: 9.000000
Tablespace transfer rate: 0.400000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1

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
[db2inst1@tomate 2 /tmp ]$ db2 "runstats on table
CAD.TBL_COLLECT _MACHINE_TIME and indexes all allow read access"
DB20000I The RUNSTATS command completed successfully.
[db2inst1@tomate 2 /tmp ]$
[db2inst1@tomate 2 /tmp ]$
[db2inst1@tomate 2 /tmp ]$ db2 "explain plan for MERGE INTO
CAD.TBL_COLLECT _MACHINE_TIME AS E USING ( VALUES
( '00E01838AD5A10 0463DB113E', '2008-08-17-14.01.00', CURRENT
TIMESTAMP , 'Y' , CURRENT TIMESTAMP , CURRENT TIMESTAMP ) ) INDATA
( MACHINE_ID, COLLECT_TIME, INSERT_TIME, COLLECT_NOVA, INSTALLED_DATE,
LAST_SCAN_TIME ) ON ( E.MACHINE_ID = INDATA.MACHINE_ ID ) WHEN MATCHED
AND E.COLLECT_TIME < INDATA.COLLECT_ TIME THEN UPDATE SET COLLECT_TIME
= INDATA.COLLECT_ TIME, INSERT_TIME = INDATA.INSERT_T IME,
COLLECT_ANTERIO R = E.COLLECT_TIME, COLLECT_NOVA = INDATA.COLLECT_ NOVA,
LAST_SCAN_TIME = INDATA.LAST_SCA N_TIME WHEN NOT MATCHED THEN INSERT
( MACHINE_ID, COLLECT_TIME, INSERT_TIME, COLLECT_NOVA, INSTALLED_DATE,
LAST_SCAN_TIME ) VALUES ( INDATA.MACHINE_ ID, INDATA.COLLECT_ TIME,
INDATA.INSERT_T IME, INDATA.COLLECT_ NOVA, INDATA.INSTALLE D_DATE,
INDATA.LAST_SCA N_TIME )"
DB20000I The SQL command completed successfully.
[db2inst1@tomate 2 /tmp ]$ db2exfmt | more
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

Enter Database Name ==Connect to Database Successful.
Enter up to 26 character Explain timestamp (Default -1) ==Enter up
to 8 character source name (SOURCE_NAME, Default %) ==Enter source
schema (SOURCE_SCHEMA, Default %) ==Enter section number (0 for all,
Default 0) ==Enter outfile name. Default is to terminal ==>
Connecting to the Database.
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp.
1991, 2002
Licensed Material - Program Property of IBM
IBM DATABASE 2 Explain Table Format Tool

*************** ***** EXPLAIN INSTANCE *************** *****

DB2_VERSION: 08.02.8
SOURCE_NAME: SQLC2E07
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2008-08-17-13.41.51.757463
EXPLAIN_REQUEST ER: DB2INST1

Database Context:
----------------
Parallelism: None
CPU Speed: 3.778754e-07
Comm Speed: 0
Buffer Pool size: 139500
Sort Heap size: 2048
Database Heap size: 10000
Lock List size: 2048
Maximum Lock List: 15
Average Applications: 50
Locks Available: 31334

Package Context:
---------------
SQL Type: Dynamic
Optimization Level: 5
Blocking: Block All Cursors
Isolation Level: Cursor Stability

---------------- STATEMENT 1 SECTION 203 ----------------
QUERYNO: 1
QUERYTAG:
Statement Type: Unrecognized
Updatable: Not Applicable
Deletable: Not Applicable
Query Degree: 1

Original Statement:
------------------
MERGE INTO CAD.TBL_COLLECT _MACHINE_TIME AS E USING (VALUES (
'00E01838AD5A10 0463DB113E', '2008-08-17-14.01.00', CURRENT
TIMESTAMP ,
'Y' , CURRENT TIMESTAMP , CURRENT TIMESTAMP)) INDATA
(MACHINE_ID,
COLLECT_TIME, INSERT_TIME, COLLECT_NOVA, INSTALLED_DATE,
LAST_SCAN_TIME) ON (E.MACHINE_ID = INDATA.MACHINE_ ID)
WHEN MATCHED AND E.COLLECT_TIME < INDATA.COLLECT_ TIME
THEN
UPDATE SET COLLECT_TIME = INDATA.COLLECT_ TIME, INSERT_TIME =
INDATA.INSERT_T IME, COLLECT_ANTERIO R = E.COLLECT_TIME,
COLLECT_NOVA =
INDATA.COLLECT_ NOVA, LAST_SCAN_TIME = INDATA.LAST_SCA N_TIME
WHEN NOT MATCHED
THEN
INSERT (MACHINE_ID, COLLECT_TIME, INSERT_TIME, COLLECT_NOVA,
INSTALLED_DATE,
LAST_SCAN_TIME) VALUES (INDATA.MACHINE _ID,
INDATA.COLLECT_ TIME,
INDATA.INSERT_T IME, INDATA.COLLECT_ NOVA,
INDATA.INSTALLE D_DATE,
INDATA.LAST_SCA N_TIME)
Optimized Statement:
-------------------
INSERT INTO CAD.TBL_COLLECT _MACHINE_TIME AS Q15
UPDATE CAD.TBL_COLLECT _MACHINE_TIME AS Q1 SET (Q1.LAST_SCAN_T IME,
Q1.COLLECT_NOVA , Q1.COLLECT_ANTE RIOR, Q1.INSERT_TIME,
Q1.COLLECT_TIME ) =
SELECT Q12.$C9, Q12.$C8, Q12.$C7, Q12.$C6, Q12.$C5, Q12.$C0,
Q12.$C1,
CURRENT TIMESTAMP, CURRENT TIMESTAMP, Q12.$C4,
CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 < TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END
FROM
(SELECT Q4.COLLECT_TIME , Q3.$C1, Q3.$C3, Q3.$C0
FROM
(SELECT '00E01838AD5A10 0463DB113E', '2008-08-17-14.01.00',
CURRENT
TIMESTAMP, 'Y', CURRENT TIMESTAMP, CURRENT TIMESTAMP
FROM (VALUES 1) AS Q2) AS Q3 LEFT OUTER JOIN
CAD.TBL_COLLECT _MACHINE_TIME AS Q4 ON (Q4.MACHINE_ID =
'00E01838AD5A10 0463DB113E')) AS Q5,
(SELECT NULL, NULL, NULL, NULL, NULL, NULL, $INTERNAL_FUNC$ (),
TIMESTAMP(Q5.$C 1), CURRENT TIMESTAMP, Q5.$C3
FROM
(SELECT 1
FROM (VALUES 1) AS Q6) AS Q7
WHERE (CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 < TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 2)
UNION ALL
SELECT $INTERNAL_FUNC$ (), Q5.$C4, Q5.$C0, NULL, NULL, NULL,
NULL, NULL
FROM
(SELECT 1
FROM (VALUES 1) AS Q9) AS Q10
WHERE (CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 < TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 1)) AS Q12

Access Plan:
-----------
Total Cost: 28.7909
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
|
0.08
INSERT
( 2)
28.7909
3.08
/-----+-----\
0.08
4554
UPDATE TABLE:
CAD
( 3)
TBL_COLLECT_MAC HINE_TIME
19.386
2.08
/------+-----\
0.08 4554
NLJOIN TABLE:
CAD
( 4)
TBL_COLLECT_MAC HINE_TIME
18.6334
2
/---------------------+--------------------\
1
0.08
NLJOIN
UNION
( 5)
( 9)
18.6305
0.00273038
2 0
/--------+-------\ /------
+-----\
1 1
0.04 0.04
TBSCAN FETCH
FILTER FILTER
( 6) ( 7)
( 10) ( 12)
4.5345e-05 18.6305
0.00119031 0.00119031
0 2
0 0
| /------+-----\
| |
1 1 4554
1 1
TABFNC: SYSIBM IXSCAN TABLE: CAD
TBSCAN TBSCAN
GENROW ( 8) TBL_COLLECT_MAC HINE_TIME
( 11) ( 13)
9.22487
4.5345e-05 4.5345e-05
1
0 0
|
| |
4554
1 1
INDEX: SYSIBM TABFNC: SYSIBM
TABFNC: SYSIBM
SQL011003151006 350
GENROW GENROW


Extended Diagnostic Information:
--------------------------------

No extended Diagnostic Information for this statment.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 28.7909
Cumulative CPU Cost: 102971
Cumulative I/O Cost: 3.08
Cumulative Re-Total Cost: 10.1677
Cumulative Re-CPU Cost: 41509.6
Cumulative Re-I/O Cost: 1.08
Cumulative First Row Cost: 28.7909
Estimated Bufferpool Buffers: 3.08

Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.2.136 : special_19546
HEAPUSE : (Maximum Statement Heap Usage)
116 Pages
STMTHEAP: (Statement heap size)
4096

Input Streams:
-------------
19) From Operator #2

Estimated number of rows: 0.08
Number of columns: 0
Subquery predicate ID: Not
Applicable
2) INSERT: (Insert)
Cumulative Total Cost: 28.7909
Cumulative CPU Cost: 102971
Cumulative I/O Cost: 3.08
Cumulative Re-Total Cost: 10.1677
Cumulative Re-CPU Cost: 41509.6
Cumulative Re-I/O Cost: 1.08
Cumulative First Row Cost: 28.7909
Estimated Bufferpool Buffers: 3.08

Input Streams:
-------------
17) From Operator #3

Estimated number of rows: 0.08
Number of columns: 8
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q14.$C7+Q14.$C 0+Q14.$C3+Q14.$ C4+Q14.$C1
+Q14.$C5+Q14.$C 2+Q14.$C6
Output Streams:
--------------
18) To Object CAD.TBL_COLLECT _MACHINE_TIME

Estimated number of rows: 4554
Number of columns: 7
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q15.COLLECT_AN TERIOR
+Q15.LAST_SCAN_ TIME
+Q15.INSTALLED_ DATE+Q15.COLLEC T_NOVA
+Q15.INSERT_TIM E+Q15.COLLECT_T IME
+Q15.MACHINE_ID

19) To Operator #1

Estimated number of rows: 0.08
Number of columns: 0
Subquery predicate ID: Not
Applicable
3) UPDATE: (Update)
Cumulative Total Cost: 19.386
Cumulative CPU Cost: 89970.6
Cumulative I/O Cost: 2.08
Cumulative Re-Total Cost: 0.762773
Cumulative Re-CPU Cost: 28509.6
Cumulative Re-I/O Cost: 0.08
Cumulative First Row Cost: 19.386
Estimated Bufferpool Buffers: 2.08

Input Streams:
-------------
15) From Operator #4

Estimated number of rows: 0.08
Number of columns: 13
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q13.$C12+Q13.$ C0+Q13.$C1+Q13. $C2+Q13.$C3

+Q13.$C4+Q13.$C 5+Q13.$C6+Q13.$ C7+Q13.$C8
+Q13.$C9+Q13.$C 10+Q13.$C11
Output Streams:
--------------
16) To Object CAD.TBL_COLLECT _MACHINE_TIME

Estimated number of rows: 4554
Number of columns: 6
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q1.$RID$+Q1.LA ST_SCAN_TIME
+Q1.COLLECT_NOV A
+Q1.COLLECT_ANT ERIOR+Q1.INSERT _TIME
+Q1.COLLECT_TIM E

17) To Operator #2

Estimated number of rows: 0.08
Number of columns: 8
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q14.$C7+Q14.$C 0+Q14.$C3+Q14.$ C4+Q14.$C1
+Q14.$C5+Q14.$C 2+Q14.$C6
4) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 18.6334
Cumulative CPU Cost: 88450
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.0101985
Cumulative Re-CPU Cost: 26989
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 18.6334
Estimated Bufferpool Buffers: 2

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE

Input Streams:
-------------
7) From Operator #5

Estimated number of rows: 1
Number of columns: 5
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q5.COLLECT_NOV A+Q5.COLLECT_TI ME
+Q5.MACHINE_ID
+Q5.COLLECT_TIM E+Q5.$C4

14) From Operator #9

Estimated number of rows: 0.08
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q12.$C4+Q12.$C 3+Q12.$C2+Q12.$ C1+Q12.$C0

+Q12.$C5+Q12.$C 6+Q12.$C7+Q12.$ C8+Q12.$C9
Output Streams:
--------------
15) To Operator #3

Estimated number of rows: 0.08
Number of columns: 13
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q13.$C12+Q13.$ C0+Q13.$C1+Q13. $C2+Q13.$C3

+Q13.$C4+Q13.$C 5+Q13.$C6+Q13.$ C7+Q13.$C8
+Q13.$C9+Q13.$C 10+Q13.$C11
5) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 18.6305
Cumulative CPU Cost: 80743.8
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00728651
Cumulative Re-CPU Cost: 19282.8
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 18.6302
Estimated Bufferpool Buffers: 2

Arguments:
---------
EARLYOUT: (Early Out flag)
NONE
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
JN INPUT: (Join input leg)
OUTER
OUTERJN : (Outer Join type)
LEFT

Input Streams:
-------------
2) From Operator #6

Estimated number of rows: 1
Number of columns: 3
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q3.$C3+Q3.$C1+ Q3.$C0

6) From Operator #7

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.$RID$+Q4.CO LLECT_TIME
Output Streams:
--------------
7) To Operator #4

Estimated number of rows: 1
Number of columns: 5
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q5.COLLECT_NOV A+Q5.COLLECT_TI ME
+Q5.MACHINE_ID
+Q5.COLLECT_TIM E+Q5.$C4
6) TBSCAN: (Table Scan)
Cumulative Total Cost: 4.5345e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 4.5345e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 3.40088e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
OUTER
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
1) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
2) To Operator #5

Estimated number of rows: 1
Number of columns: 3
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q3.$C3+Q3.$C1+ Q3.$C0
7) FETCH : (Fetch)
Cumulative Total Cost: 18.6305
Cumulative CPU Cost: 80623.8
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00724117
Cumulative Re-CPU Cost: 19162.8
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 18.6301
Estimated Bufferpool Buffers: 3

Arguments:
---------
JN INPUT: (Join input leg)
INNER
MAXPAGES: (Maximum pages for prefetch)
1
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
EXCLUSIVE
TABLOCK : (Table Lock intent)
INTENT EXCLUSIVE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
4) From Operator #8

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.MACHINE_ID( A)+Q4.$RID$

5) From Object CAD.TBL_COLLECT _MACHINE_TIME

Estimated number of rows: 4554
Number of columns: 5
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.LAST_SCAN_T IME+Q4.COLLECT_ NOVA
+Q4.COLLECT_ANT ERIOR+Q4.INSERT _TIME
+Q4.COLLECT_TIM E
Output Streams:
--------------
6) To Operator #5

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.$RID$+Q4.CO LLECT_TIME
8) IXSCAN: (Index Scan)
Cumulative Total Cost: 9.22487
Cumulative CPU Cost: 65803.8
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 0.00655343
Cumulative Re-CPU Cost: 17342.8
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 9.22487
Estimated Bufferpool Buffers: 2

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
EXCLUSIVE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT EXCLUSIVE

Predicates:
----------
4) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000219587

Predicate Text:
--------------
(Q4.MACHINE_ID = '00E01838AD5A10 0463DB113E')

4) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.000219587

Predicate Text:
--------------
(Q4.MACHINE_ID = '00E01838AD5A10 0463DB113E')
Input Streams:
-------------
3) From Object SYSIBM.SQL01100 3151006350

Estimated number of rows: 4554
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.MACHINE_ID( A)+Q4.$RID$
Output Streams:
--------------
4) To Operator #7

Estimated number of rows: 1
Number of columns: 2
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q4.MACHINE_ID( A)+Q4.$RID$
9) UNION : (Union)
Cumulative Total Cost: 0.00273038
Cumulative CPU Cost: 7225.6
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00273038
Cumulative Re-CPU Cost: 7225.6
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00266259
Estimated Bufferpool Buffers: 0

Arguments:
---------
JN INPUT: (Join input leg)
INNER

Input Streams:
-------------
10) From Operator #10

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q8.$C0+Q8.$C1+ Q8.$C2+Q8.$C3+Q 8.$C4
+Q8.MACHINE_ID
+Q8.$C7+Q8.$C6+ Q8.$C8+Q8.$C5

13) From Operator #12

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q11.$C5+Q11.$C 6+Q11.$C7+Q11.$ C8+Q11.$C9
+Q11.COLLECT_TI ME
+Q11.$C3+Q11.$C 2+Q11.$C1
+Q11.$C0
Output Streams:
--------------
14) To Operator #4

Estimated number of rows: 0.08
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q12.$C4+Q12.$C 3+Q12.$C2+Q12.$ C1+Q12.$C0

+Q12.$C5+Q12.$C 6+Q12.$C7+Q12.$ C8+Q12.$C9
10) FILTER: (Filter)
Cumulative Total Cost: 0.00119031
Cumulative CPU Cost: 3150
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00119031
Cumulative Re-CPU Cost: 3150
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00119031
Estimated Bufferpool Buffers: 0

Predicates:
----------
6) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(
CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 <
TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 2)
Input Streams:
-------------
9) From Operator #11

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
10) To Operator #9

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------
+Q8.$C0+Q8.$C1+ Q8.$C2+Q8.$C3+Q 8.$C4
+Q8.MACHINE_ID
+Q8.$C7+Q8.$C6+ Q8.$C8+Q8.$C5
11) TBSCAN: (Table Scan)
Cumulative Total Cost: 4.5345e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 4.5345e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 3.40088e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
8) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
9) To Operator #10

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
12) FILTER: (Filter)
Cumulative Total Cost: 0.00119031
Cumulative CPU Cost: 3150
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 0.00119031
Cumulative Re-CPU Cost: 3150
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 0.00119031
--More--Executing Connect Reset -- Connect Reset was Successful.
Estimated Bufferpool Buffers: 0

Predicates:
----------
8) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04

Predicate Text:
--------------
(
CASE
WHEN (Q5.$C4 IS NOT NULL AND (Q5.$C0 <
TIMESTAMP(Q5.$C 1)))
THEN 1
WHEN Q5.$C4 IS NULL
THEN 2
ELSE 0 END = 1)
Input Streams:
-------------
12) From Operator #13

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
13) To Operator #9

Estimated number of rows: 0.04
Number of columns: 10
Subquery predicate ID: Not
Applicable

Column Names:
------------

+Q11.$C5+Q11.$C 6+Q11.$C7+Q11.$ C8+Q11.$C9
+Q11.COLLECT_TI ME
+Q11.$C3+Q11.$C 2+Q11.$C1
+Q11.$C0
13) TBSCAN: (Table Scan)
Cumulative Total Cost: 4.5345e-05
Cumulative CPU Cost: 120
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 4.5345e-05
Cumulative Re-CPU Cost: 120
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 3.40088e-05
Estimated Bufferpool Buffers: 0

Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
TBISOLVL: (Table access Isolation Level)
CURSOR STABILITY

Input Streams:
-------------
11) From Object SYSIBM.GENROW

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Output Streams:
--------------
12) To Operator #12

Estimated number of rows: 1
Number of columns: 0
Subquery predicate ID: Not
Applicable
Objects Used in Access Plan:
---------------------------

Schema: SYSIBM
Name: SQL011003151006 350
Type: Index
Time of creation:
2001-10-03-15.10.06.339708
Last statistics update:
2008-08-17-13.41.47.557449
Number of columns: 1
Number of rows: 4554
Width of rows: -1
Number of buffer pool pages: 52
Distinct row values: Yes
Tablespace name: CADIDX
Tablespace overhead: 9.000000
Tablespace transfer rate: 0.200000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Index clustering statistic: 92.000000
Index leaf pages: 53
Index tree levels: 2
Index full key cardinality: 4554
Index first key cardinality: 4554
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 0
Index page density: 0
Index avg sequential pages: 0
Index avg gap between sequences:0
Index avg random pages: 0
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 4754
Index deleted RID count: 200
Index empty leaf pages: 0
Base Table Schema: CAD
Base Table Name:
TBL_COLLECT_MAC HINE_TIME
Columns in index:
MACHINE_ID

Schema: CAD
Name: TBL_COLLECT_MAC HINE_TIME
Type: Table
Time of creation:
2001-10-03-15.10.04.108739
Last statistics update:
2008-08-17-13.41.47.557449
Number of columns: 7
Number of rows: 4554
Width of rows: 88
Number of buffer pool pages: 52
Distinct row values: No
Tablespace name:
CAD
Tablespace overhead: 9.000000
Tablespace transfer rate: 0.400000
Source for statistics: Single Node
Prefetch page count: 128
Container extent page count: 32
Table overflow record count: 0
Table Active Blocks: -1

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
Aug 17 '08 #3
The plan looks good to me with a single row iscan-fetch.
I do see that you have an additional match predicate
(E.COLLECT_TIME < INDATA.COLLECT_ TIME).
Could it be that you have concurrent connections all going after the
same row '00E01838AD5A10 0463DB113E'?
These of course would collide and result in lock-waits.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Aug 18 '08 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
2293
by: Charlie Williams | last post by:
I am having difficulty performing updates and deletions on an Access database using the Update() method of the OleDBDataAdapter. I can insert rows without a problem, but I get a concurrency exception when trying to update or delete. I am quite sure that no concurrency conflicts actually exist. Is there a reason why the data adapter I am using may have a different row version that the database that it got its data from?
25
2428
by: nick | last post by:
I'm having trouble updating from a datagrid. It's says "Concurrency violation: the UpdateCommand affected 0 records", though I can't see how it's related to "concurrency". I can insert and delete with no problem. Here's the code that's causing the problem: public void UpdateDataSource(Dancers.allbookings ChangedRows) { try { if ((ChangedRows != null))
4
3099
by: Robert Schuldenfrei | last post by:
Dear NG, I was about to "improve" concurrency checking with a Timestamp when I discovered that my current code is not working. After about a day of beating my head against the wall, I am turning to the NG in hopes that someone can spot what I am doing wrong. Key to this technique working is the SQL UPDATE statement. It is designed to fail
5
31422
by: Vayse | last post by:
In my save code, most of items save fine. But sometimes I get a concurrency violation message. "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records." It happens on the same records each time - once this happens, it never saves ok again. The immediate window also has states: A first chance exception of type 'System.Data.DBConcurrencyException' occurred in System.Data.dll
4
1558
by: Bob | last post by:
While testing my my program I came up with a consistency exception. My program consists of three datagridviews, One called dgvPostes which is the parent grid and its two children,one called dgvPlans and the other dgvTanks. What happens is as follows. I will either create or edit a record in the datagridview dgvPlans and call the Updatedb procedure (code below). The first save works OK. Then when that is done, on the same record I will try...
7
1771
by: William E Voorhees | last post by:
I'm updating an Access database in a windows multi-user environment. I'm using disconnected data I read data from an Access Data table to a data object I update the data object from a windows form I save the data from the data object to the Access Data table using a data adapter as follows:
3
1429
by: John | last post by:
Hi I have a vs 2003 winform data app. All the data access code has been generated using the data adapter wizard and then pasted into the app. The problem I have is that I am getting a data concurrency error on mydataadapter.update() method. I know that there is no data concurrency problem as I am the only user testing the app. Obviously the error is misleading. What can I do from here to fix this problem? Thanks
1
2053
by: =?Utf-8?B?Qi4gQ2hlcm5pY2s=?= | last post by:
(If I'm overlooking anything, please let me know.) First, my only concern is updating single records in a Detailsview using an ObjectDataSource. The target table has a timestamp field. Assume a single primary key. Create your xsd. Drag the table onto the xsd. Then manually edit the Update statement to simplify it. Essentially 'Update <tableset blah = blah Where (PK=@PK) and (ts=@ts)'. (Get rid of all that auto-generated...
5
1849
by: John | last post by:
Hi I have developed the following logic to handle db concurrency violations. I just wonder if someone can tell me if it is correct or if I need a different approach.Would love to know how pros handle it. Thanks Regards
0
8962
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8785
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9463
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9200
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8201
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6747
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4559
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4822
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3273
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.