The ETL application loaded around 3000 rows in 14 seconds in a Development
database while it took 2 hours to load in a UAT database.
UAT db is partitioned.
Dev db is not partitioned.
the application looks for existing rows in the table...if they already
exist then it updates otherwise inserts them.
The table is pretty large, around 6.5 million rows.
Due to RI, it is looking up into parent tables which possibly reside of
different nodes.
This issue has increased the batch window from 2 hrs to 4 hrs and this is
certainly not acceptable.
How can I get rid of this performance issue?
Cheers,
San.
10  4247 
shsandeep wrote:
The ETL application loaded around 3000 rows in 14 seconds in a Development
database while it took 2 hours to load in a UAT database.
UAT db is partitioned.
Dev db is not partitioned.
You should make your dev system partitioned as well. 2 logical
partitions would do.
the application looks for existing rows in the table...if they already
exist then it updates otherwise inserts them.
The table is pretty large, around 6.5 million rows.
Due to RI, it is looking up into parent tables which possibly reside of
different nodes.
Show us the statements you run and the plans (db2exfmt output).
How big are the parent tables? Often parent tables are small and should
be replicated on all partitions.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference http://www.iiug.org/waiug/present/Fo...Forum2006.html
Serge Rielau wrote:
Show us the statements you run and the plans (db2exfmt output).
How big are the parent tables? Often parent tables are small and should
be replicated on all partitions.
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
If the parent table is too large to replicate on each partition, and
you are using natural keys, then you may be able to make the
partitioning key the same on the parent and the child table. This will
make sure that joins between parent and child do not require
cross-partition I/O (a key performance issue when using DPF).
shsandeep wrote:
The ETL application loaded around 3000 rows in 14 seconds in a Development
database while it took 2 hours to load in a UAT database.
UAT db is partitioned.
Dev db is not partitioned.
the application looks for existing rows in the table...if they already
exist then it updates otherwise inserts them.
The table is pretty large, around 6.5 million rows.
Due to RI, it is looking up into parent tables which possibly reside of
different nodes.
This issue has increased the batch window from 2 hrs to 4 hrs and this is
certainly not acceptable.
How can I get rid of this performance issue?
Cheers,
San.
One side note. In my experience, an occassional failed update is much
cheaper than checking to see if the record exists first before trying to
update. Since the update will typically key on the primary key column(s)
and use an index the update will either succeed or instantly fail with a
very cheap index lookup. If the majority of the records being processed
will result in an update then it is very expensive to do the SELECT to check
for existence first.
Similarly, a failed insert is very expensive as the typically the data
record is written then the indexes are updated. Only when the primary key
index key add is attempted will the duplication be detected and then the
record add and any other index key updates have to be rolled back. Still,
if the vast majority of records processed will result in an insert this may
still be cheaper than the thousands of failed updates.
My rule of thumb is if 75% of the records processed will result in an insert
try the insert first and update if the insert fails. Otherwise try the
update first and insert if that fails. In neither case should there be a
SELECT to verify the prior existence of the row. If the weighting between
inserts and updates tends to vary over time the task can be dynamic about
whether to try inserts of updates first using some threshhold. So say if
the last two operations were inserts try insert first next time, etc.
This is not directly related to your question, however, following these
suggestions will tend to improve performance of these types of tasks
significantly and I have found that, at least in Informix servers and I have
not reason to see why DB2 would behave differently, the improvements are
noticably greater for partitioned (fragmented in IDS) tables unless that
partitioning is based directly on the primary key. It's worth testing anyway.
Art S. Kagel
Art S. Kagel wrote:
shsandeep wrote:
>The ETL application loaded around 3000 rows in 14 seconds in a
Development
database while it took 2 hours to load in a UAT database.
UAT db is partitioned.
Dev db is not partitioned.
the application looks for existing rows in the table...if they already
exist then it updates otherwise inserts them.
The table is pretty large, around 6.5 million rows.
Due to RI, it is looking up into parent tables which possibly reside of
different nodes.
This issue has increased the batch window from 2 hrs to 4 hrs and this is
certainly not acceptable.
How can I get rid of this performance issue?
Cheers,
San.
One side note. In my experience, an occassional failed update is much
cheaper than checking to see if the record exists first before trying to
update. Since the update will typically key on the primary key
column(s) and use an index the update will either succeed or instantly
fail with a very cheap index lookup. If the majority of the records
being processed will result in an update then it is very expensive to do
the SELECT to check for existence first.
Similarly, a failed insert is very expensive as the typically the data
record is written then the indexes are updated. Only when the primary
key index key add is attempted will the duplication be detected and then
the record add and any other index key updates have to be rolled back.
Still, if the vast majority of records processed will result in an
insert this may still be cheaper than the thousands of failed updates.
My rule of thumb is if 75% of the records processed will result in an
insert try the insert first and update if the insert fails. Otherwise
try the update first and insert if that fails. In neither case should
there be a SELECT to verify the prior existence of the row. If the
weighting between inserts and updates tends to vary over time the task
can be dynamic about whether to try inserts of updates first using some
threshhold. So say if the last two operations were inserts try insert
first next time, etc.
This is not directly related to your question, however, following these
suggestions will tend to improve performance of these types of tasks
significantly and I have found that, at least in Informix servers and I
have not reason to see why DB2 would behave differently, the
improvements are noticably greater for partitioned (fragmented in IDS)
tables unless that partitioning is based directly on the primary key.
It's worth testing anyway.
Art's logic applies.
In addition DB2 supports the MERGE statement.
But let's climb that hill when we get there....
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference http://www.iiug.org/waiug/present/Fo...Forum2006.html
I have attached the 'db2expln' output below. Was unable to get db2exfmt
working.
Just confirmed the insert strategy from the developers - they try to
insert first and if it fails, then they update it.
Actually, this table had a self referencing constraint which ate up a
major chunk of the exectution time (figured this from the Visual Explain).
Once this self referential constraint was removed, it ran fine. Is there
anything different that needs to be done to deal with self referential
constraints??
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = Yes
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"EDWSAS"
SQL Statement:
INSERT INTO EDWUTST.TCN_CLIENT(CLN_HI_ID, CLN_NK_ID, CLN_STRT_DT,
CLN_END_DT, SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM,
PARENT_CLN_NK_ID, CLN_TYP, CLN_SECUR_NUM, BR_HI_ID,
PARENT_CLN_HI_ID, TRAN_TS, TRAN_USR_ID, LOAD_ID, COMP_CDE,
LOCN_ADDR_NK_ID, MAIL_ADDR_NK_ID, LOCN_ADDR_HI_ID,
MAIL_ADDR_HI_ID, LAST_UPDT_LOAD_ID)VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Section Code Page = 1252
Estimated Cost = 77.245018
Estimated Cardinality = 0.333333
Coordinator Subsection - Main Processing:
Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 1, 2, 3
Distribute Subsection #4
| Broadcast to Node List
| | Nodes = 1, 2, 3
Distribute Subsection #2
| Directed by Hash
| | #Columns = 1
| | Partition Map ID = 3, Nodegroup = PDPG, #Nodes = 1
Distribute Subsection #1
| Directed to Single Node
| | Node Number = 0
Subsection #1:
Access Table Queue ID = q1 #Columns = 0
Left Outer Nested Loop Join
| Access Table Name = EDWUTST.TGE_SOURCE_SYSTEM ID = 7,29
| | Index Scan: Name = EDWUTST.XGE_SOURCE_SYSTEM ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: SRCE_SYS_NK_ID (Ascending)
| | #Columns = 1
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
Left Outer Nested Loop Join
| Access Table Name = EDWUTST.TGE_COMPANY ID = 7,23
| | Index Scan: Name = EDWUTST.XGE_COMPANY ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: COMP_CDE (Ascending)
| | #Columns = 1
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
Left Outer Nested Loop Join
| Access Table Name = EDWUTST.TGE_BRANCH ID = 7,21
| | Index Scan: Name = EDWUTST.XGE_BRANCH ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: BR_HI_ID (Ascending)
| | #Columns = 1
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
Subsection #2:
Insert: Table Name = EDWUTST.TCN_CLIENT ID = 3,14
Left Outer Nested Loop Join
| Piped Inner
| Access Table Queue ID = q2 #Columns = 1
Residual Predicate(s)
| #Predicates = 3
Left Outer Nested Loop Join
| Piped Inner
| Access Table Queue ID = q3 #Columns = 1
Residual Predicate(s)
| #Predicates = 3
Insert Into Asynchronous Table Queue ID = q1
| Broadcast to All Nodes of Subsection 1
| Rows Can Overflow to Temporary Table
Subsection #3:
Access Table Name = EDWUTST.TAP_ADDRESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADDRESS ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ADDR_HI_ID (Ascending)
| #Columns = 1
| Fully Qualified Unique Key
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Read Stability
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q2
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q2
Subsection #4:
Access Table Name = EDWUTST.TAP_ADDRESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADDRESS ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ADDR_HI_ID (Ascending)
| #Columns = 1
| Fully Qualified Unique Key
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Read Stability
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q3
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q3
End of section
I have attached the 'db2expln' output below. Was unable to get db2exfmt
working.
Just confirmed the insert strategy from the developers - they try to
insert first and if it fails, then they update it.
Actually, this table had a self referencing constraint which ate up a
major chunk of the exectution time (figured this from the Visual Explain).
Once this self referential constraint was removed, it ran fine. Is there
anything different that needs to be done to deal with self referential
constraints??
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
DB2 Universal Database Version 8.1, 5622-044 (c) Copyright IBM Corp. 1991,
2002
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
Isolation Level = Cursor Stability
Blocking = Block Unambiguous Cursors
Query Optimization Class = 5
Partition Parallel = Yes
Intra-Partition Parallel = No
SQL Path = "SYSIBM", "SYSFUN", "SYSPROC",
"EDWSAS"
SQL Statement:
INSERT INTO EDWUTST.TCN_CLIENT(CLN_HI_ID, CLN_NK_ID, CLN_STRT_DT,
CLN_END_DT, SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM,
PARENT_CLN_NK_ID, CLN_TYP, CLN_SECUR_NUM, BR_HI_ID,
PARENT_CLN_HI_ID, TRAN_TS, TRAN_USR_ID, LOAD_ID, COMP_CDE,
LOCN_ADDR_NK_ID, MAIL_ADDR_NK_ID, LOCN_ADDR_HI_ID,
MAIL_ADDR_HI_ID, LAST_UPDT_LOAD_ID)VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Section Code Page = 1252
Estimated Cost = 77.245018
Estimated Cardinality = 0.333333
Coordinator Subsection - Main Processing:
Distribute Subsection #3
| Broadcast to Node List
| | Nodes = 1, 2, 3
Distribute Subsection #4
| Broadcast to Node List
| | Nodes = 1, 2, 3
Distribute Subsection #2
| Directed by Hash
| | #Columns = 1
| | Partition Map ID = 3, Nodegroup = PDPG, #Nodes = 1
Distribute Subsection #1
| Directed to Single Node
| | Node Number = 0
Subsection #1:
Access Table Queue ID = q1 #Columns = 0
Left Outer Nested Loop Join
| Access Table Name = EDWUTST.TGE_SOURCE_SYSTEM ID = 7,29
| | Index Scan: Name = EDWUTST.XGE_SOURCE_SYSTEM ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: SRCE_SYS_NK_ID (Ascending)
| | #Columns = 1
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
Left Outer Nested Loop Join
| Access Table Name = EDWUTST.TGE_COMPANY ID = 7,23
| | Index Scan: Name = EDWUTST.XGE_COMPANY ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: COMP_CDE (Ascending)
| | #Columns = 1
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
Left Outer Nested Loop Join
| Access Table Name = EDWUTST.TGE_BRANCH ID = 7,21
| | Index Scan: Name = EDWUTST.XGE_BRANCH ID = 1
| | | Regular Index (Not Clustered)
| | | Index Columns:
| | | | 1: BR_HI_ID (Ascending)
| | #Columns = 1
| | Single Record
| | Fully Qualified Unique Key
| | #Key Columns = 1
| | | Start Key: Inclusive Value
| | | | | 1: ?
| | | Stop Key: Inclusive Value
| | | | | 1: ?
| | Index-Only Access
| | Index Prefetch: None
| | Lock Intents
| | | Table: Intent Share
| | | Row : Next Key Share
Residual Predicate(s)
| #Predicates = 3
Subsection #2:
Insert: Table Name = EDWUTST.TCN_CLIENT ID = 3,14
Left Outer Nested Loop Join
| Piped Inner
| Access Table Queue ID = q2 #Columns = 1
Residual Predicate(s)
| #Predicates = 3
Left Outer Nested Loop Join
| Piped Inner
| Access Table Queue ID = q3 #Columns = 1
Residual Predicate(s)
| #Predicates = 3
Insert Into Asynchronous Table Queue ID = q1
| Broadcast to All Nodes of Subsection 1
| Rows Can Overflow to Temporary Table
Subsection #3:
Access Table Name = EDWUTST.TAP_ADDRESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADDRESS ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ADDR_HI_ID (Ascending)
| #Columns = 1
| Fully Qualified Unique Key
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Read Stability
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q2
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q2
Subsection #4:
Access Table Name = EDWUTST.TAP_ADDRESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADDRESS ID = 1
| | Regular Index (Not Clustered)
| | Index Columns:
| | | 1: ADDR_HI_ID (Ascending)
| #Columns = 1
| Fully Qualified Unique Key
| #Key Columns = 1
| | Start Key: Inclusive Value
| | | | 1: ?
| | Stop Key: Inclusive Value
| | | | 1: ?
| Index-Only Access
| Index Prefetch: None
| Isolation Level: Read Stability
| Lock Intents
| | Table: Intent Share
| | Row : Next Key Share
| Sargable Index Predicate(s)
| | Insert Into Asynchronous Table Queue ID = q3
| | | Hash to Specific Node
| | | Rows Can Overflow to Temporary Tables
Insert Into Asynchronous Table Queue Completion ID = q3
End of section
I put in the wrong db2expln output.....
The one posted earlier is without the self referential constraint.
I'll enforce the self referential constraint again and send the 'db2expln'
output soon.
Cheers,
San.
*grmbl*db2expln*sh***
cd sqllib/misc
db2 -tvf EXPLAIN.DDL
db2 explain plan for INSERT INTO EDWUTST.TCN_CLIENT(CLN_HI_ID,
CLN_NK_ID, CLN_STRT_DT,
CLN_END_DT, SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM,
PARENT_CLN_NK_ID, CLN_TYP, CLN_SECUR_NUM, BR_HI_ID,
PARENT_CLN_HI_ID, TRAN_TS, TRAN_USR_ID, LOAD_ID, COMP_CDE,
LOCN_ADDR_NK_ID, MAIL_ADDR_NK_ID, LOCN_ADDR_HI_ID,
MAIL_ADDR_HI_ID, LAST_UPDT_LOAD_ID)VALUES (?, ?, ?, ?, ?, ?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
db2exfmt -d <db-o myplan.exfmt -1
Now THAT's a plan.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference http://www.iiug.org/waiug/present/Fo...Forum2006.html
Pretty long one....here you go....
edwsas@iceadm002:/userdata/home/d2idwh/sqllib/miscdb2exfmt -d UTSTWH -1
| 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
Connect to Database Successful.
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.4
SOURCE_NAME: SQLC2E07
SOURCE_SCHEMA: NULLID
SOURCE_VERSION:
EXPLAIN_TIME: 2006-11-03-13.10.30.150825
EXPLAIN_REQUESTER: EDWSAS
Database Context:
----------------
Parallelism: Inter-Partition Parallelism
CPU Speed: 2.361721e-07
Comm Speed: 100
Buffer Pool size: 5000
Sort Heap size: 256
Database Heap size: 3000
Lock List size: 1024
Maximum Lock List: 10
Average Applications: 1
Locks Available: 6553
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: Insert
Updatable: Not Applicable
Deletable: Not Applicable
Query Degree: 1
Original Statement:
------------------
INSERT INTO EDWUTST.TCN_CLIENT(CLN_HI_ID, CLN_NK_ID,
CLN_STRT_DT,CLN_END_DT,
SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM, PARENT_CLN_NK_ID, CLN_TYP,
CLN_SECUR_NUM, BR_HI_ID, PARENT_CLN_HI_ID, TRAN_TS, TRAN_USR_ID,
LOAD_ID, COMP_CDE,LOCN_ADDR_NK_ID, MAIL_ADDR_NK_ID,
LOCN_ADDR_HI_ID,
MAIL_ADDR_HI_ID, LAST_UPDT_LOAD_ID)VALUES (?, ?, ?, ?, ?, ?,?, ?,
?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Optimized Statement:
-------------------
$WITH CONTEXT$($CONSTRAINT$(EDWUTST.TCN_CLIENT.SQL060719 121710550),
$CONSTRAINT$(EDWUTST.TCN_CLIENT.SQL060719121710520 ),
$CONSTRAINT$(EDWUTST.TCN_CLIENT.SQL060719121710500 ),
$CONSTRAINT$(EDWUTST.TCN_CLIENT.SQL060719121710470 ),
$CONSTRAINT$(EDWUTST.TCN_CLIENT.SQL060719121710450 ))
INSERT INTO EDWUTST.TCN_CLIENT AS Q7
SELECT :?, :?, :?, :?, :?, :?, :?, :?, :?, :?, :?, :?, :?, :?, :?, :?,
:?,
:?, :?, :?, :?
FROM (VALUES 1) AS Q5
Access Plan:
-----------
Total Cost: 77.245
Query Degree: 1
Rows
RETURN
( 1)
Cost
I/O
|
0.04
FILTER
( 2)
77.245
6
|
1
NLJOIN
( 3)
77.2444
6
/---+---\
1 1
FILTER IXSCAN
( 4) ( 21)
64.381 12.8634
5 1
| |
1 1910
NLJOIN INDEX: EDWUTST
( 5) XGE_BRANCH
64.3804
5
/---+--\
1 1
FILTER IXSCAN
( 6) ( 20)
64.3706 0.00983232
5 0
| |
1 7
NLJOIN INDEX: EDWUTST
( 7) XGE_COMPANY
64.37
5
/----+---\
1 1
BTQ IXSCAN
( 8) ( 19)
64.3604 0.00956025
5 0
| |
0.333333 1
FILTER INDEX: EDWUTST
( 9) XGE_SOURCE_SYSTE
64.3378
5
|
0.333333
NLJOIN
( 10)
64.3375
5
/---------+--------\
0.333333 1
FILTER DTQ
( 11) ( 17)
38.5954 25.7421
3 2
| |
0.333333 0.333333
NLJOIN IXSCAN
( 12) ( 18)
38.5952 25.7171
3 2
/--------+-------\ |
0.333333 1 2.26896e+06
INSERT DTQ INDEX: EDWUTST
( 13) ( 15) XAP_ADDRESS
12.8531 25.7421
1 2
/----+----\ |
0.333333 2.21438e+06 0.333333
TBSCAN TABLE: EDWUTST IXSCAN
( 14) TCN_CLIENT ( 16)
2.36172e-05 25.7171
0 2
| |
1 2.26896e+06
TABFNC: SYSIBM INDEX: EDWUTST
GENROW XAP_ADDRESS
Extended Diagnostic Information:
--------------------------------
No extended Diagnostic Information for this statment.
Plan Details:
-------------
1) RETURN: (Return Result)
Cumulative Total Cost: 77.245
Cumulative CPU Cost: 614046
Cumulative I/O Cost: 6
Cumulative Re-Total Cost: 12.865
Cumulative Re-CPU Cost: 63680.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 77.245
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 6
Arguments:
---------
BLDLEVEL: (Build level)
DB2 v8.1.3.104 : s060120
STMTHEAP: (Statement heap size)
4096
Input Streams:
-------------
27) From Operator #2
Estimated number of rows: 0.04
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
2) FILTER: (Filter)
Cumulative Total Cost: 77.245
Cumulative CPU Cost: 614046
Cumulative I/O Cost: 6
Cumulative Re-Total Cost: 12.865
Cumulative Re-CPU Cost: 63680.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 77.245
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 6
Predicates:
----------
2) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 0.04
Predicate Text:
--------------
$INTERNAL_PRED$
Input Streams:
-------------
26) From Operator #3
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q18.$C0+Q18.$C1
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
27) To Operator #1
Estimated number of rows: 0.04
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
3) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 77.2444
Cumulative CPU Cost: 611550
Cumulative I/O Cost: 6
Cumulative Re-Total Cost: 12.8645
Cumulative Re-CPU Cost: 61185.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 77.2444
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 6
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
OUTERJN : (Outer Join type)
LEFT
Input Streams:
-------------
23) From Operator #4
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
25) From Operator #21
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q1.$RID$+Q1.BR_HI_ID
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
26) To Operator #2
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q18.$C0+Q18.$C1
Partition Column Names:
----------------------
+NONE
4) FILTER: (Filter)
Cumulative Total Cost: 64.381
Cumulative CPU Cost: 554680
Cumulative I/O Cost: 5
Cumulative Re-Total Cost: 12.8625
Cumulative Re-CPU Cost: 52775.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 64.381
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 5
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
Predicates:
----------
4) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
$INTERNAL_PRED$
Input Streams:
-------------
22) From Operator #5
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q16.$C0+Q16.$C1
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
23) To Operator #3
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
5) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 64.3804
Cumulative CPU Cost: 552185
Cumulative I/O Cost: 5
Cumulative Re-Total Cost: 12.8619
Cumulative Re-CPU Cost: 50280.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 64.3804
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 5
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
OUTERJN : (Outer Join type)
LEFT
Input Streams:
-------------
19) From Operator #6
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
21) From Operator #20
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q2.$RID$+Q2.COMP_CDE
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
22) To Operator #4
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q16.$C0+Q16.$C1
Partition Column Names:
----------------------
+NONE
6) FILTER: (Filter)
Cumulative Total Cost: 64.3706
Cumulative CPU Cost: 510553
Cumulative I/O Cost: 5
Cumulative Re-Total Cost: 12.8604
Cumulative Re-CPU Cost: 44109.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 64.3706
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 5
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
Predicates:
----------
6) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
$INTERNAL_PRED$
Input Streams:
-------------
18) From Operator #7
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q14.$C0+Q14.$C1
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
19) To Operator #5
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
7) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 64.37
Cumulative CPU Cost: 508058
Cumulative I/O Cost: 5
Cumulative Re-Total Cost: 12.8598
Cumulative Re-CPU Cost: 41614.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 64.37
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 5
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
OUTERJN : (Outer Join type)
LEFT
Input Streams:
-------------
15) From Operator #8
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
17) From Operator #19
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q3.$RID$+Q3.SRCE_SYS_NK_ID
Partition Column Names:
----------------------
+NONE
Output Streams:
--------------
18) To Operator #6
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q14.$C0+Q14.$C1
Partition Column Names:
----------------------
+NONE
8) TQ : (Table Queue)
Cumulative Total Cost: 64.3604
Cumulative CPU Cost: 467578
Cumulative I/O Cost: 5
Cumulative Re-Total Cost: 12.8586
Cumulative Re-CPU Cost: 36595.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 64.3604
Cumulative Comm Cost: 7.02433
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 5
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
LISTENER: (Listener Table Queue type)
FALSE
TQMERGE : (Merging Table Queue flag)
FALSE
TQREAD : (Table Queue Read type)
READ AHEAD
TQSEND : (Table Queue Write type)
BROADCAST
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
14) From Operator #9
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
15) To Operator #7
Estimated number of rows: 1
Partition Map ID: 4
Partitioning: ( 0)
Single
Node (# 0) Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+NONE
9) FILTER: (Filter)
Cumulative Total Cost: 64.3378
Cumulative CPU Cost: 371563
Cumulative I/O Cost: 5
Cumulative Re-Total Cost: 12.8586
Cumulative Re-CPU Cost: 36595.5
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 64.3378
Cumulative Comm Cost: 6.02204
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 5
Predicates:
----------
8) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
$INTERNAL_PRED$
Input Streams:
-------------
13) From Operator #10
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q12.$C0+Q12.$C1
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
14) To Operator #8
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+1: Q-9.?
10) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 64.3375
Cumulative CPU Cost: 370611
Cumulative I/O Cost: 5
Cumulative Re-Total Cost: 12.8584
Cumulative Re-CPU Cost: 35643.9
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 64.3375
Cumulative Comm Cost: 6.02204
Cumulative First Comm Cost: 6.02204
Estimated Bufferpool Buffers: 5
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
OUTERJN : (Outer Join type)
LEFT
Input Streams:
-------------
9) From Operator #11
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+1: Q-9.?
12) From Operator #17
Estimated number of rows: 1
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 1
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q4.$RID$
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
13) To Operator #9
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q12.$C0+Q12.$C1
Partition Column Names:
----------------------
+1: Q-9.?
11) FILTER: (Filter)
Cumulative Total Cost: 38.5954
Cumulative CPU Cost: 192331
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 12.8559
Cumulative Re-CPU Cost: 24847.8
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 38.5954
Cumulative Comm Cost: 3.01102
Cumulative First Comm Cost: 3.01102
Estimated Bufferpool Buffers: 3
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
Predicates:
----------
10) Residual Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1
Predicate Text:
--------------
$INTERNAL_PRED$
Input Streams:
-------------
8) From Operator #12
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q10.$C0+Q10.$C1
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
9) To Operator #10
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+1: Q-9.?
12) NLJOIN: (Nested Loop Join)
Cumulative Total Cost: 38.5952
Cumulative CPU Cost: 191380
Cumulative I/O Cost: 3
Cumulative Re-Total Cost: 12.8556
Cumulative Re-CPU Cost: 23896.1
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 38.5952
Cumulative Comm Cost: 3.01102
Cumulative First Comm Cost: 3.01102
Estimated Bufferpool Buffers: 3
Arguments:
---------
EARLYOUT: (Early Out flag)
LEFT
FETCHMAX: (Override for FETCH MAXPAGES)
IGNORE
ISCANMAX: (Override for ISCAN MAXPAGES)
IGNORE
OUTERJN : (Outer Join type)
LEFT
Input Streams:
-------------
4) From Operator #13
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+1: Q-9.?
7) From Operator #15
Estimated number of rows: 1
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 1
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q9.$RID$
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
8) To Operator #11
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q10.$C0+Q10.$C1
Partition Column Names:
----------------------
+1: Q-9.?
13) INSERT: (Insert)
Cumulative Total Cost: 12.8531
Cumulative CPU Cost: 13100
Cumulative I/O Cost: 1
Cumulative Re-Total Cost: 12.8531
Cumulative Re-CPU Cost: 13100
Cumulative Re-I/O Cost: 1
Cumulative First Row Cost: 12.8531
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 1
Arguments:
---------
JN INPUT: (Join input leg)
OUTER
Input Streams:
-------------
2) From Operator #14
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 21
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q6.$C0+Q6.$C1+Q6.$C2+Q6.$C3+Q6.$C4+Q6.$C5
+Q6.$C6+Q6.$C7+Q6.$C8+Q6.$C9+Q6.$C10+Q6.$C11
+Q6.$C12+Q6.$C13+Q6.$C14+Q6.$C15+Q6.$C16
+Q6.$C17+Q6.$C18+Q6.$C19+Q6.$C20
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
3) To Object EDWUTST.TCN_CLIENT
Estimated number of rows:
2.21438e+06
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 21
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q7.LAST_UPDT_LOAD_ID+Q7.MAIL_ADDR_HI_ID
+Q7.LOCN_ADDR_HI_ID+Q7.MAIL_ADDR_NK_ID
+Q7.LOCN_ADDR_NK_ID+Q7.COMP_CDE+Q7.LOAD_ID
+Q7.TRAN_USR_ID+Q7.TRAN_TS+Q7.PARENT_CLN_HI_ID
+Q7.BR_HI_ID+Q7.CLN_SECUR_NUM+Q7.CLN_TYP
+Q7.PARENT_CLN_NK_ID+Q7.CLN_NUM+Q7.BR_NK_ID
+Q7.SRCE_SYS_NK_ID+Q7.CLN_END_DT
+Q7.CLN_STRT_DT+Q7.CLN_NK_ID+Q7.CLN_HI_ID
Partition Column Names:
----------------------
+1: Q-9.?
4) To Operator #12
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+1: Q-9.?
14) TBSCAN: (Table Scan)
Cumulative Total Cost: 2.36172e-05
Cumulative CPU Cost: 100
Cumulative I/O Cost: 0
Cumulative Re-Total Cost: 2.36172e-05
Cumulative Re-CPU Cost: 100
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 2.12555e-05
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 0
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
ALL
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
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
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 0
Subquery predicate ID: Not
Applicable
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
2) To Operator #13
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 21
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q6.$C0+Q6.$C1+Q6.$C2+Q6.$C3+Q6.$C4+Q6.$C5
+Q6.$C6+Q6.$C7+Q6.$C8+Q6.$C9+Q6.$C10+Q6.$C11
+Q6.$C12+Q6.$C13+Q6.$C14+Q6.$C15+Q6.$C16
+Q6.$C17+Q6.$C18+Q6.$C19+Q6.$C20
Partition Column Names:
----------------------
+1: Q-9.?
15) TQ : (Table Queue)
Cumulative Total Cost: 25.7421
Cumulative CPU Cost: 178280
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00254974
Cumulative Re-CPU Cost: 10796.1
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.7421
Cumulative Comm Cost: 3.01102
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 3
Arguments:
---------
JN INPUT: (Join input leg)
INNER
LISTENER: (Listener Table Queue type)
FALSE
PARTCOLS: (Table partitioning columns)
1: Q-9.?
TQMERGE : (Merging Table Queue flag)
FALSE
TQREAD : (Table Queue Read type)
READ AHEAD
TQSEND : (Table Queue Write type)
DIRECTED
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
6) From Operator #16
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (MULT )
Multiple
Partitions
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q9.$RID$+Q9.ADDR_HI_ID
Partition Column Names:
----------------------
+1: Q9.ADDR_HI_ID
Output Streams:
--------------
7) To Operator #12
Estimated number of rows: 1
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 1
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q9.$RID$
Partition Column Names:
----------------------
+1: Q-9.?
16) IXSCAN: (Index Scan)
Cumulative Total Cost: 25.7171
Cumulative CPU Cost: 72257.1
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00254974
Cumulative Re-CPU Cost: 10796.1
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.7171
Cumulative Comm Cost: 0
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 3
Arguments:
---------
MAXPAGES: (Maximum pages for prefetch)
1
PREFETCH: (Type of Prefetch)
NONE
ROWLOCK : (Row Lock intent)
NEXT KEY SHARE
SCANDIR : (Scan Direction)
FORWARD
TABLOCK : (Table Lock intent)
INTENT SHARE
Predicates:
----------
11) Start Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1.4691e-07
Predicate Text:
--------------
(:? = Q9.ADDR_HI_ID)
11) Stop Key Predicate
Relational Operator: Equal (=)
Subquery Input Required: No
Filter Factor: 1.4691e-07
Predicate Text:
--------------
(:? = Q9.ADDR_HI_ID)
Input Streams:
-------------
5) From Object EDWUTST.XAP_ADDRESS
Estimated number of rows:
2.26896e+06
Partition Map ID: 3
Partitioning: (MULT )
Multiple
Partitions
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q9.$RID$+Q9.ADDR_HI_ID
Partition Column Names:
----------------------
+1: Q9.ADDR_HI_ID
Output Streams:
--------------
6) To Operator #15
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (MULT )
Multiple
Partitions
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q9.$RID$+Q9.ADDR_HI_ID
Partition Column Names:
----------------------
+1: Q9.ADDR_HI_ID
17) TQ : (Table Queue)
Cumulative Total Cost: 25.7421
Cumulative CPU Cost: 178280
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00254974
Cumulative Re-CPU Cost: 10796.1
Cumulative Re-I/O Cost: 0
Cumulative First Row Cost: 25.7421
Cumulative Comm Cost: 3.01102
Cumulative First Comm Cost: 0
Estimated Bufferpool Buffers: 3
Arguments:
---------
JN INPUT: (Join input leg)
INNER
LISTENER: (Listener Table Queue type)
FALSE
PARTCOLS: (Table partitioning columns)
1: Q-9.?
TQMERGE : (Merging Table Queue flag)
FALSE
TQREAD : (Table Queue Read type)
READ AHEAD
TQSEND : (Table Queue Write type)
DIRECTED
UNIQUE : (Uniqueness required flag)
FALSE
Input Streams:
-------------
11) From Operator #18
Estimated number of rows: 0.333333
Partition Map ID: 3
Partitioning: (MULT )
Multiple
Partitions
Number of columns: 2
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q4.$RID$+Q4.ADDR_HI_ID
Partition Column Names:
----------------------
+1: Q4.ADDR_HI_ID
Output Streams:
--------------
12) To Operator #10
Estimated number of rows: 1
Partition Map ID: 3
Partitioning: (HASH )
Hash
Directed to Single Partition
Number of columns: 1
Subquery predicate ID: Not
Applicable
Column Names:
------------
+Q4.$RID$
Partition Column Names:
----------------------
+1: Q-9.?
18) IXSCAN: (Index Scan)
Cumulative Total Cost: 25.7171
Cumulative CPU Cost: 72257.1
Cumulative I/O Cost: 2
Cumulative Re-Total Cost: 0.00254974
Cumulative Re-CPU Cost: 10796.1
Objects Used in Access Plan:
---------------------------
Schema: EDWUTST
Name: TAP_ADDRESS
Type: Table (reference only)
Schema: EDWUTST
Name: TGE_BRANCH
Type: Table (reference only)
Schema: EDWUTST
Name: TGE_COMPANY
Type: Table (reference only)
Schema: EDWUTST
Name: TGE_SOURCE_SYSTEM
Type: Table (reference only)
Schema: EDWUTST
Name: XAP_ADDRESS
Type: Index
Time of creation:
2006-07-12-15.19.57.500768
Last statistics update:
2006-11-01-17.46.37.428125
Number of columns: 1
Number of rows: 2268956
Width of rows: -1
Number of buffer pool pages: 23157
Distinct row values: Yes
Tablespace name: TS_PD_AT_INDEX_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 8
--More--Executing Connect Reset -- Connect Reset was Successful.
Index clustering statistic: 0.997959
Index leaf pages: 4071
Index tree levels: 3
Index full key cardinality: 2268956
Index first key cardinality: 2268956
Index first 2 keys cardinality: -1
Index first 3 keys cardinality: -1
Index first 4 keys cardinality: -1
Index sequential pages: 4034
Index page density: 98
Index avg sequential pages: 4034
Index avg gap between sequences:0
Index avg random pages: 31
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 2268956
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: EDWUTST
Base Table Name: TAP_ADDRESS
Columns in index:
ADDR_HI_ID
Schema: EDWUTST
Name: XGE_BRANCH
Type: Index
Time of creation:
2006-07-12-15.18.50.524865
Last statistics update:
2006-11-01-17.46.37.570622
Number of columns: 1
Number of rows: 1910
Width of rows: -1
Number of buffer pool pages: 31
Distinct row values: Yes
Tablespace name: TS_SD_AT_INDEX_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 8
Index clustering statistic: 1.000000
Index leaf pages: 4
Index tree levels: 2
Index full key cardinality: 1910
Index first key cardinality: 1910
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: 4
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 1910
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: EDWUTST
Base Table Name: TGE_BRANCH
Columns in index:
BR_HI_ID
Schema: EDWUTST
Name: XGE_COMPANY
Type: Index
Time of creation:
2006-07-12-15.18.50.621547
Last statistics update:
2006-11-01-17.46.37.655174
Number of columns: 1
Number of rows: 7
Width of rows: -1
Number of buffer pool pages: 1
Distinct row values: Yes
Tablespace name: TS_SD_AT_INDEX_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 8
Index clustering statistic: 100.000000
Index leaf pages: 1
Index tree levels: 1
Index full key cardinality: 7
Index first key cardinality: 7
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: 1
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 7
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: EDWUTST
Base Table Name: TGE_COMPANY
Columns in index:
COMP_CDE
Schema: EDWUTST
Name: XGE_SOURCE_SYSTEM
Type: Index
Time of creation:
2006-07-12-15.18.50.937443
Last statistics update:
2006-09-28-11.47.17.710150
Number of columns: 1
Number of rows: 1
Width of rows: -1
Number of buffer pool pages: 1
Distinct row values: Yes
Tablespace name: TS_SD_AT_INDEX_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 8
Index clustering statistic: 100.000000
Index leaf pages: 1
Index tree levels: 1
Index full key cardinality: 1
Index first key cardinality: 1
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: 1
Fetch avg sequential pages: -1
Fetch avg gap between sequences:-1
Fetch avg random pages: -1
Index RID count: 1
Index deleted RID count: 0
Index empty leaf pages: 0
Base Table Schema: EDWUTST
Base Table Name: TGE_SOURCE_SYSTEM
Columns in index:
SRCE_SYS_NK_ID
Schema: EDWUTST
Name: TCN_CLIENT
Type: Table
Time of creation:
2006-07-12-15.18.05.892019
Last statistics update:
2006-11-01-17.44.43.289644
Number of columns: 21
Number of rows: 2214377
Width of rows: 99
Number of buffer pool pages: 27687
Distinct row values: No
Tablespace name: TS_PD_AT_DATA_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Source for statistics: Single Node
Prefetch page count: 32
Container extent page count: 8
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
Base Table For Index Not Already Shown:
---------------------------------------
Schema: EDWUTST
Name: TAP_ADDRESS
Time of creation:
2006-07-12-15.19.57.463570
Last statistics update:
2006-11-01-17.46.37.428125
Number of columns: 14
Number of rows: 6806868
Number of pages: 69471
Number of pages with rows: 69081
Tablespace name: TS_PD_AT_DATA_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Prefetch page count: 32
Container extent page count: 8
Table overflow record count: 213
Indexspace name: TS_PD_AT_INDEX_01
Schema: EDWUTST
Name: TGE_BRANCH
Time of creation:
2006-07-12-15.18.50.505513
Last statistics update:
2006-11-01-17.46.37.570622
Number of columns: 23
Number of rows: 1910
Number of pages: 31
Number of pages with rows: 31
Tablespace name: TS_SD_AT_DATA_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Prefetch page count: 32
Container extent page count: 8
Table overflow record count: 0
Indexspace name: TS_SD_AT_INDEX_01
Schema: EDWUTST
Name: TGE_COMPANY
Time of creation:
2006-07-12-15.18.50.603272
Last statistics update:
2006-11-01-17.46.37.655174
Number of columns: 11
Number of rows: 7
Number of pages: 1
Number of pages with rows: 1
Tablespace name: TS_SD_AT_DATA_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Prefetch page count: 32
Container extent page count: 8
Table overflow record count: 0
Indexspace name: TS_SD_AT_INDEX_01
Schema: EDWUTST
Name: TGE_SOURCE_SYSTEM
Time of creation:
2006-07-12-15.18.50.918010
Last statistics update:
2006-09-28-11.47.17.710150
Number of columns: 3
Number of rows: 1
Number of pages: 1
Number of pages with rows: 1
Tablespace name: TS_SD_AT_DATA_01
Tablespace overhead: 12.670000
Tablespace transfer rate: 0.180000
Prefetch page count: 32
Container extent page count: 8
Table overflow record count: 0
Indexspace name: TS_SD_AT_INDEX_01
shsandeep wrote:
Pretty long one....here you go....
Not really... The INSERT is not guilty. At least not its plan.
What about the update?
Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
WAIUG Conference http://www.iiug.org/waiug/present/Fo...Forum2006.html This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Bernie |
last post by:
Greetings,
I have 3 servers all running SQL Server 2000 - 8.00.818. Lets call
them parent, child1, and child 2.
On parent, I create a view called item as follows:
CREATE view Item as...
|
by: Jack |
last post by:
I have a test database that I have built in a 3 partition (and 3 node)
environment. I have defined all the tables so they have the same
partition key. The tables (7 of them) form a hierarchical...
|
by: Private Pyle |
last post by:
AIX 5.1, DB2 8.1.3 64-bit ESE 5 partitions 1 catalog, 4 data.
I have a situation where I have to update 269,000,000 rows in a table
with the value in another table with just about the same number...
|
by: simon |
last post by:
Hi
Hopefully someone could point me in the right direction on this one.
INFRASTRUCTURE
DB2 v7 on MVS OS390
SCENARIO
We are currently loading large volumes of data (eg 20MM rows) per day...
|
by: 57R4N63R |
last post by:
I'm currently building a website for one of the client. There has been
few errors here and there, but just recently the problem is getting worse.
Basically the symptoms is that when the user try...
|
by: P. Adhia |
last post by:
Sorry for quoting an old post and probably I am reading out of context
so my concern is unfounded. But I would appreciate if I can get someone
or Serge to confirm. Also unlike the question asked in...
|
by: Varangian |
last post by:
Hi,
I have a performance issue question?
which is best (in terms of efficiency and performance, I don't care
neatness in code)... building an ArrayList of Object Instances using
SqlDataReader...
|
by: eavery |
last post by:
Does anyone know of any documentation on the performance of partition
merge/split? Does the merge or split of a partition cause any locking
on the partitioned table? If you were merging or...
|
by: eavery |
last post by:
Does anyone know of any documentation on the performance of partition
merge/split? Does the merge or split of a partition cause any locking
on the partitioned table? If you were merging or...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
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...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
