By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,334 Members | 1,290 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,334 IT Pros & Developers. It's quick & easy.

DB2 V8 FP12 - Merge Statement - Plan Question

P: n/a
Hi,

If someone can clarify this, it would be of great help!

We have a merge, which is written *only* to update the target table.
but the access plan shows branches with inserts and deletes on target
table. On top of it, the application snapshot monitor shows "Rows
inserted".

Thanks for your time!

This is the merge statement and the access plan has insert and delete
branches:
Note: the merge statement does NOT have insert/delete code in it.
merge into stella.facttransC as target
using (select
dwcontractid,
keyfactrans,
actper
from stella.fact_temp where dwcontractid = 4096 ) as source
on target.keyfactrans = source.keyfactrans
and target.dwcontractid = source.dwcontractid
and target.dwcontractid = 4096
when matched then update
set target.actper = source.actper
@
Access Plan:
-----------
Total Cost: 2.04237e+06
Query Degree: 0

Rows
RETURN
( 1)
FstTup
I/O
|
69386.5
INSERT
( 2)
2.04237e+06
166586
/----+----\
69386.5 2.14366e+08
TBSCAN TABLE: STELLA
( 3) FACTRANSC
2.02929e+06
165580
|
69386.5
TEMP
( 4)
2.02924e+06
165580
|
69386.5
DELETE
( 5)
2.02921e+06
165580
/----+----\
69386.5 2.14366e+08
UPDATE TABLE: STELLA
( 6) FACTRANSC
1.13719e+06
96193.8
/---+---\
69386.5 2.14366e+08
FILTER TABLE: STELLA
( 7) FACTRANSC
66.063
26807.4
|
1.73466e+06
NLJOIN
( 8)
64.367
26807.4
/------------+-----------\
1.73466e+06 0.000194074
FETCH FETCH
( 9) ( 11)
38.5722 25.7947
26137 2.00019
/----+----\ /----+----\
1.73466e+06 1.73466e+06 0.000194074 2.14366e+08
IXSCAN TABLE: STELLA IXSCAN TABLE: STELLA
( 10) FACT_TEMP ( 12) FACTRANSC
25.7164 25.7923
1477 2
| |
1.73466e+06 2.14366e+08
INDEX: STELLA INDEX: STELLA
FACT_TEMP_IX1 FACTRANSC_MDC_5

If I create a table like the target table and do the merge, the delete/
insert sections are gone from the plan:
db2 "create table stella.fact_backup like stella.factransC"
merge into stella.fact_backup as target using
(select dwcontractid, keyfactrans, actper
from stella.fact_temp
where dwcontractid = 4096) as source on target.keyfactrans =
source.keyfactrans and target.dwcontractid =
source.dwcontractid and
target.dwcontractid = 4096
when matched
then
update set target.actper = source.actper
Access Plan:
-----------
Total Cost: 1.38305e+06
Query Degree: 0

Rows
RETURN
( 1)
FstTup
I/O
|
69386.5
UPDATE
( 2)
1.38305e+06
144897
/---+---\
69386.5 1.99964e+06
FETCH TABLE: STELLA
( 3) FACT_BACKUP
172804
75511
/---+---\
69386.5 1.99964e+06
FILTER TABLE: STELLA
( 4) FACT_BACKUP
172791
50824
|
1.73466e+06
HSJOIN
( 5)
172791
50824
/--------+-------\
3199.43 1.73466e+06
TBSCAN FETCH
( 6) ( 7)
38.9302 38.5722
24687 26137
| /----+----\
1.99964e+06 1.73466e+06 1.73466e+06
TABLE: STELLA IXSCAN TABLE: STELLA
FACT_BACKUP ( 8) FACT_TEMP
25.7164
1477
|
1.73466e+06
INDEX: STELLA
FACT_TEMP_IX1

What is going on with the merge?

Appreciate your response!!!

Vijay

Sep 7 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
UDBDBA wrote:
Update:

We were able to get this problem resolved with IBM PMR. There was a
bug and are in the process of getting special build.
As a workaround, selectivity 0.001 on the dwcontractid got the merge
running faster in 4 mins vs. 40 min.

Also, there is a DAM above the UPDATE in the plan which is not
necessary if we are doing only updates to MDC. MDC converts MDC
dimension column updates into deletes and inserts but the snapshot
monitor reported rows inserted but did not report any rows being
deleted.

All of this is supposed to be fixed in special build.. waiting for it.
Glad to hear it.

Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Sep 14 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.