Regarding concurrency MERGE x UPDATE

#1 August 16th, 2008, 12:15 PM

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

#2 August 17th, 2008, 02:25 AM

Michel Esber wrote:

Quote:

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

#3 August 17th, 2008, 06:55 PM

What plan has been chosen? In an OLTP environment you want MERGE to use

Quote:

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_REQUESTER: 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 (
'00E01838AD5A100463DB113E', '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_TIME, COLLECT_ANTERIOR = E.COLLECT_TIME,
COLLECT_NOVA =
INDATA.COLLECT_NOVA, LAST_SCAN_TIME = INDATA.LAST_SCAN_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_TIME, INDATA.COLLECT_NOVA,
INDATA.INSTALLED_DATE,
INDATA.LAST_SCAN_TIME)

Optimized Statement:
-------------------
INSERT INTO CAD.TBL_COLLECT_MACHINE_TIME AS Q15
UPDATE CAD.TBL_COLLECT_MACHINE_TIME AS Q1 SET (Q1.LAST_SCAN_TIME,
Q1.COLLECT_NOVA, Q1.COLLECT_ANTERIOR, 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.$C1)))
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 '00E01838AD5A100463DB113E', '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 =
'00E01838AD5A100463DB113E')) AS Q5,
(SELECT NULL, NULL, NULL, NULL, NULL, NULL, $INTERNAL_FUNC$(),
TIMESTAMP(Q5.$C1), 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.$C1)))
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.$C1)))
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_MACHINE_TIME
19.3859
2.08
/------+-----\
0.08 8897
NLJOIN TABLE:
CAD
( 4)
TBL_COLLECT_MACHINE_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_MACHINE_TIME
( 11) ( 13)
9.22472
4.5345e-05 4.5345e-05
1
0 0
|
| |
8897
1 1
INDEX: SYSIBM TABFNC: SYSIBM
TABFNC: SYSIBM
SQL011003151006350
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.$C0+Q14.$C3+Q14.$C4+Q14.$C1
+Q14.$C5+Q14.$C2+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_ANTERIOR
+Q15.LAST_SCAN_TIME
+Q15.INSTALLED_DATE+Q15.COLLECT_NOVA
+Q15.INSERT_TIME+Q15.COLLECT_TIME
+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.$C5+Q13.$C6+Q13.$C7+Q13.$C8
+Q13.$C9+Q13.$C10+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.LAST_SCAN_TIME
+Q1.COLLECT_NOVA
+Q1.COLLECT_ANTERIOR+Q1.INSERT_TIME
+Q1.COLLECT_TIME

17) To Operator #2

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

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

+Q14.$C7+Q14.$C0+Q14.$C3+Q14.$C4+Q14.$C1
+Q14.$C5+Q14.$C2+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_NOVA+Q5.COLLECT_TIME
+Q5.MACHINE_ID
+Q5.COLLECT_TIME+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.$C3+Q12.$C2+Q12.$C1+Q12.$C0

+Q12.$C5+Q12.$C6+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.$C5+Q13.$C6+Q13.$C7+Q13.$C8
+Q13.$C9+Q13.$C10+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.COLLECT_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_NOVA+Q5.COLLECT_TIME
+Q5.MACHINE_ID
+Q5.COLLECT_TIME+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_TIME+Q4.COLLECT_NOVA
+Q4.COLLECT_ANTERIOR+Q4.INSERT_TIME
+Q4.COLLECT_TIME

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.COLLECT_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 = '00E01838AD5A100463DB113E')

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

Predicate Text:
--------------
(Q4.MACHINE_ID = '00E01838AD5A100463DB113E')

Input Streams:
-------------
3) From Object SYSIBM.SQL011003151006350

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+Q8.$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.$C6+Q11.$C7+Q11.$C8+Q11.$C9
+Q11.COLLECT_TIME
+Q11.$C3+Q11.$C2+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.$C3+Q12.$C2+Q12.$C1+Q12.$C0

+Q12.$C5+Q12.$C6+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.$C1)))
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+Q8.$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.$C1)))
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.$C6+Q11.$C7+Q11.$C8+Q11.$C9
+Q11.COLLECT_TIME
+Q11.$C3+Q11.$C2+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: SQL011003151006350
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_MACHINE_TIME
Columns in index:
MACHINE_ID

Schema: CAD
Name: TBL_COLLECT_MACHINE_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@tomate2 /tmp ]$ db2 "runstats on table
CAD.TBL_COLLECT_MACHINE_TIME and indexes all allow read access"
DB20000I The RUNSTATS command completed successfully.
[db2inst1@tomate2 /tmp ]$
[db2inst1@tomate2 /tmp ]$
[db2inst1@tomate2 /tmp ]$ db2 "explain plan for MERGE INTO
CAD.TBL_COLLECT_MACHINE_TIME AS E USING ( VALUES
( '00E01838AD5A100463DB113E', '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_TIME,
COLLECT_ANTERIOR = E.COLLECT_TIME, COLLECT_NOVA = INDATA.COLLECT_NOVA,
LAST_SCAN_TIME = INDATA.LAST_SCAN_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_TIME, INDATA.COLLECT_NOVA, INDATA.INSTALLED_DATE,
INDATA.LAST_SCAN_TIME )"
DB20000I The SQL command completed successfully.
[db2inst1@tomate2 /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_REQUESTER: 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 (
'00E01838AD5A100463DB113E', '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_TIME, COLLECT_ANTERIOR = E.COLLECT_TIME,
COLLECT_NOVA =
INDATA.COLLECT_NOVA, LAST_SCAN_TIME = INDATA.LAST_SCAN_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_TIME, INDATA.COLLECT_NOVA,
INDATA.INSTALLED_DATE,
INDATA.LAST_SCAN_TIME)

Optimized Statement:
-------------------
INSERT INTO CAD.TBL_COLLECT_MACHINE_TIME AS Q15
UPDATE CAD.TBL_COLLECT_MACHINE_TIME AS Q1 SET (Q1.LAST_SCAN_TIME,
Q1.COLLECT_NOVA, Q1.COLLECT_ANTERIOR, 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.$C1)))
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 '00E01838AD5A100463DB113E', '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 =
'00E01838AD5A100463DB113E')) AS Q5,
(SELECT NULL, NULL, NULL, NULL, NULL, NULL, $INTERNAL_FUNC$(),
TIMESTAMP(Q5.$C1), 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.$C1)))
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.$C1)))
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_MACHINE_TIME
19.386
2.08
/------+-----\
0.08 4554
NLJOIN TABLE:
CAD
( 4)
TBL_COLLECT_MACHINE_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_MACHINE_TIME
( 11) ( 13)
9.22487
4.5345e-05 4.5345e-05
1
0 0
|
| |
4554
1 1
INDEX: SYSIBM TABFNC: SYSIBM
TABFNC: SYSIBM
SQL011003151006350
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.$C0+Q14.$C3+Q14.$C4+Q14.$C1
+Q14.$C5+Q14.$C2+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_ANTERIOR
+Q15.LAST_SCAN_TIME
+Q15.INSTALLED_DATE+Q15.COLLECT_NOVA
+Q15.INSERT_TIME+Q15.COLLECT_TIME
+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.$C5+Q13.$C6+Q13.$C7+Q13.$C8
+Q13.$C9+Q13.$C10+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.LAST_SCAN_TIME
+Q1.COLLECT_NOVA
+Q1.COLLECT_ANTERIOR+Q1.INSERT_TIME
+Q1.COLLECT_TIME

17) To Operator #2

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

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

+Q14.$C7+Q14.$C0+Q14.$C3+Q14.$C4+Q14.$C1
+Q14.$C5+Q14.$C2+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_NOVA+Q5.COLLECT_TIME
+Q5.MACHINE_ID
+Q5.COLLECT_TIME+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.$C3+Q12.$C2+Q12.$C1+Q12.$C0

+Q12.$C5+Q12.$C6+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.$C5+Q13.$C6+Q13.$C7+Q13.$C8
+Q13.$C9+Q13.$C10+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.COLLECT_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_NOVA+Q5.COLLECT_TIME
+Q5.MACHINE_ID
+Q5.COLLECT_TIME+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_TIME+Q4.COLLECT_NOVA
+Q4.COLLECT_ANTERIOR+Q4.INSERT_TIME
+Q4.COLLECT_TIME

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.COLLECT_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 = '00E01838AD5A100463DB113E')

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

Predicate Text:
--------------
(Q4.MACHINE_ID = '00E01838AD5A100463DB113E')

Input Streams:
-------------
3) From Object SYSIBM.SQL011003151006350

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+Q8.$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.$C6+Q11.$C7+Q11.$C8+Q11.$C9
+Q11.COLLECT_TIME
+Q11.$C3+Q11.$C2+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.$C3+Q12.$C2+Q12.$C1+Q12.$C0

+Q12.$C5+Q12.$C6+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.$C1)))
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+Q8.$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.$C1)))
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.$C6+Q11.$C7+Q11.$C8+Q11.$C9
+Q11.COLLECT_TIME
+Q11.$C3+Q11.$C2+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: SQL011003151006350
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_MACHINE_TIME
Columns in index:
MACHINE_ID

Schema: CAD
Name: TBL_COLLECT_MACHINE_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

#4 August 18th, 2008, 12:35 PM

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 '00E01838AD5A100463DB113E'?
These of course would collide and result in lock-waits.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

What is Bytes?

Popular Articles