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  4297 
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_CLI ENT(CLN_HI_ID, CLN_NK_ID, CLN_STRT_DT,
CLN_END_DT, SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM,
PARENT_CLN_NK_I D, CLN_TYP, CLN_SECUR_NUM, BR_HI_ID,
PARENT_CLN_HI_I D, 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_SOU RCE_SYSTEM ID = 7,29
| | Index Scan: Name = EDWUTST.XGE_SOU RCE_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_COM PANY ID = 7,23
| | Index Scan: Name = EDWUTST.XGE_COM PANY 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_BRA NCH ID = 7,21
| | Index Scan: Name = EDWUTST.XGE_BRA NCH 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_CLI ENT 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_ADD RESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADD RESS 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_ADD RESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADD RESS 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_CLI ENT(CLN_HI_ID, CLN_NK_ID, CLN_STRT_DT,
CLN_END_DT, SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM,
PARENT_CLN_NK_I D, CLN_TYP, CLN_SECUR_NUM, BR_HI_ID,
PARENT_CLN_HI_I D, 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_SOU RCE_SYSTEM ID = 7,29
| | Index Scan: Name = EDWUTST.XGE_SOU RCE_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_COM PANY ID = 7,23
| | Index Scan: Name = EDWUTST.XGE_COM PANY 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_BRA NCH ID = 7,21
| | Index Scan: Name = EDWUTST.XGE_BRA NCH 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_CLI ENT 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_ADD RESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADD RESS 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_ADD RESS ID = 3,21
| Index Scan: Name = EDWUTST.XAP_ADD RESS 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_CLI ENT(CLN_HI_ID,
CLN_NK_ID, CLN_STRT_DT,
CLN_END_DT, SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM,
PARENT_CLN_NK_I D, CLN_TYP, CLN_SECUR_NUM, BR_HI_ID,
PARENT_CLN_HI_I D, 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@iceadm00 2:/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_REQUEST ER: 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_CLI ENT(CLN_HI_ID, CLN_NK_ID,
CLN_STRT_DT,CLN _END_DT,
SRCE_SYS_NK_ID, BR_NK_ID, CLN_NUM, PARENT_CLN_NK_I D, CLN_TYP,
CLN_SECUR_NUM, BR_HI_ID, PARENT_CLN_HI_I D, TRAN_TS, TRAN_USR_ID,
LOAD_ID, COMP_CDE,LOCN_A DDR_NK_ID, MAIL_ADDR_NK_ID ,
LOCN_ADDR_HI_ID ,
MAIL_ADDR_HI_ID , LAST_UPDT_LOAD_ ID)VALUES (?, ?, ?, ?, ?, ?,?, ?,
?,
?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Optimized Statement:
-------------------
$WITH CONTEXT$($CONST RAINT$(EDWUTST. TCN_CLIENT.SQL0 60719121710550) ,
$CONSTRAINT$(ED WUTST.TCN_CLIEN T.SQL0607191217 10520),
$CONSTRAINT$(ED WUTST.TCN_CLIEN T.SQL0607191217 10500),
$CONSTRAINT$(ED WUTST.TCN_CLIEN T.SQL0607191217 10470),
$CONSTRAINT$(ED WUTST.TCN_CLIEN T.SQL0607191217 10450))
INSERT INTO EDWUTST.TCN_CLI ENT 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_SYST E
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.$C 1
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.$C 1
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.$C 1
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.CO MP_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.$C 1
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.$C 1
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.SR CE_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.$C 1
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.$C 1
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.$C 1
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.$C 1
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.$C 1
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+Q 6.$C4+Q6.$C5
+Q6.$C6+Q6.$C7+ Q6.$C8+Q6.$C9+Q 6.$C10+Q6.$C11
+Q6.$C12+Q6.$C1 3+Q6.$C14+Q6.$C 15+Q6.$C16
+Q6.$C17+Q6.$C1 8+Q6.$C19+Q6.$C 20
Partition Column Names:
----------------------
+1: Q-9.?
Output Streams:
--------------
3) To Object EDWUTST.TCN_CLI ENT
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_L OAD_ID+Q7.MAIL_ ADDR_HI_ID
+Q7.LOCN_ADDR_H I_ID+Q7.MAIL_AD DR_NK_ID
+Q7.LOCN_ADDR_N K_ID+Q7.COMP_CD E+Q7.LOAD_ID
+Q7.TRAN_USR_ID +Q7.TRAN_TS+Q7. PARENT_CLN_HI_I D
+Q7.BR_HI_ID+Q7 .CLN_SECUR_NUM+ Q7.CLN_TYP
+Q7.PARENT_CLN_ NK_ID+Q7.CLN_NU M+Q7.BR_NK_ID
+Q7.SRCE_SYS_NK _ID+Q7.CLN_END_ DT
+Q7.CLN_STRT_DT +Q7.CLN_NK_ID+Q 7.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+Q 6.$C4+Q6.$C5
+Q6.$C6+Q6.$C7+ Q6.$C8+Q6.$C9+Q 6.$C10+Q6.$C11
+Q6.$C12+Q6.$C1 3+Q6.$C14+Q6.$C 15+Q6.$C16
+Q6.$C17+Q6.$C1 8+Q6.$C19+Q6.$C 20
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.AD DR_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_ADD RESS
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.AD DR_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.AD DR_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.AD DR_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_SYST EM
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_SYST EM
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_SYST EM
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_0 1
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_0 1
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_0 1
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_0 1
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_SYST EM
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_0 1
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
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
select * from child1.dbchild1.dbo.Item union all
select * from child2.DBChild2.dbo.Item
|
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
arrangement. The data is all bogus, so I using the generate_unique
function to come up with a partitioning key for each record. This is
all done through a stored proc with will insert about 90 records in
the heirarchy.
The behavior that I am...
|
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 of
records. It's a reporting table and the update is based on the
primary key and both tables are indexed to support to the look up.
Both tables also share the same partitioning key and are in the same
node group.
My first try was to declare...
|
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
into a partitioned DB2 database. In the installed version of DB2
there are only upto 240 (or so) partitions, so we have to double up
|
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 to access the page, it
takes really long time to load. However, after up to 1 hour, the website
will run fine again as normal.
This issue has been there with the site. I usually just ask the system
admin to restart the IIS Service. However, the...
| | |
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 the post below,
my question involves non-partitioned table loads.
I want to know if, in general, loading from cursor is slower than
loading from a file? I was thinking cursor would normally be faster,
because of DB2's superior buffer/prefetch...
|
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 OR using SqlDataAdapter to Fill a DataSet or DataTable ?
Thanks!
|
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 splitting a large
volume of data rebalancing your partitioned table would you
potentially lock users out?
|
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 splitting a large
volume of data rebalancing your partitioned table would you
potentially lock users out?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
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 tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
