SEO

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.

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/misc> db2exfmt -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