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_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
