DB2 MERGE STATEMENT ARE using table scan

Anwei Shen

unread,

Mar 6, 2011, 11:44:53 PM3/6/11

to

I have a merge statement run pretty slow, when checking explain. It
simply scan both tables.
Source table and Target table are partitioned by different columns,
both indexed.
This is on db2 v9.1
merge tgt
using src
on tgt.primarykey = src.key
when matched
when not matched.

I am expecting it scan only the src table.
Any idea?

Anwei Shen

unread,

Mar 7, 2011, 11:26:00 PM3/7/11

to

Even I create another table like Target table using the same partition
key, merge will do 2 table scan.

Frederik Engelen

unread,

Mar 8, 2011, 11:06:27 AM3/8/11

to

Hello Anwei,

Could you provide some DDL that would allow us to recreate your
situation? There are many factors involved in the optimizer's decision
to use a table scan. The information you're giving is too vague to
comment on.

--
Frederik Engelen

Serge Rielau

unread,

Mar 8, 2011, 11:06:59 AM3/8/11

to

Drop the optimization level to 3. That disables hashjoin.
Do you have stats on the source table? How big is the source table
compared to the target table?

--
Serge Rielau
SQL Architect DB2 for LUW, IBM Toronto Lab
Blog: tinyurl.com/SQLTips4DB2
Wiki: tinyurl.com/Oracle2DB2Wiki
Twitter: srielau

Frederik Engelen

unread,

Mar 8, 2011, 11:24:52 AM3/8/11

to

> Twitter: srielau- Hide quoted text -
>
> - Show quoted text -

"The information you're giving is too vague to comment on."

Ok, yes, well, euhm, Serge can, of course...

--
Frederik

Mark A

unread,

Mar 8, 2011, 5:45:23 PM3/8/11

to

"Serge Rielau" <sri...@ca.ibm.com> wrote in message
news:8tn2h4...@mid.individual.net...

> Drop the optimization level to 3. That disables hashjoin.
> Do you have stats on the source table? How big is the source table
> compared to the target table?
> --
> Serge Rielau
> SQL Architect DB2 for LUW, IBM Toronto Lab
> Blog: tinyurl.com/SQLTips4DB2
> Wiki: tinyurl.com/Oracle2DB2Wiki
> Twitter: srielau

Any chance of setting optimization level at the statement level (sort of
like can be done with isolation level)?

Helmut Tessarek

unread,

Mar 8, 2011, 6:23:08 PM3/8/11

to

> Any chance of setting optimization level at the statement level (sort of
> like can be done with isolation level)?

db2 set current query optimization 3
<run your statement here>
db2 set current query optimization <old value>

--
Helmut K. C. Tessarek
DB2 Performance and Development
IBM Toronto Lab

Helmut Tessarek

unread,

Mar 8, 2011, 6:36:46 PM3/8/11

to

> Any chance of setting optimization level at the statement level (sort of
> like can be done with isolation level)?

You can't set the optimization level at statement level (within the SQL
statement), but you can use the following special register:

set current query optimization = 3
<run your statement here>
set current query optimization = <old value>

Mark A

unread,

Mar 9, 2011, 12:40:59 AM3/9/11

to

"Helmut Tessarek" <tess...@evermeet.cx> wrote in message
news:il6eie$1ca$2...@news.albasani.net...

Yes, I already know that. My question is whether there is any chance of IBM
making an enhancement to DB2 to have it set at the statement level.

Changing the current optimization is unacceptable in most cases.

Serge Rielau

unread,

Mar 9, 2011, 5:10:06 AM3/9/11

to

Mark,

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/c0024649.htm

Cheers
Serge

PS: No this is not the complete answer, but knowing you you should be
able to connect the dots...

Message has been deleted

Anwei Shen

unread,

Mar 9, 2011, 1:52:46 PM3/9/11

to

CREATE TABLE FACT (
BATCH_NUM INTEGER NOT NULL,
DRN BIGINT NOT NULL,
SOURCE_ID INTEGER NOT NULL,
ID BIGINT ,
REVIEW_STATUS_PPR_INVC_DT DATE,
REVIEW_STATUS_PPR_INVC_DT_IDN INTEGER,
REVIEW_STATUS_PAPER_INVC CHAR(2) ,
ACTL_PKUP_QUAL CHAR(3) ,
ACTL_PKUP_QUAL_DESC VARCHAR(50) ,
DOC_REF1_DT TIMESTAMP ,
DOC_REF1_DT_DESC VARCHAR(50) ,
DOC_REF1_DT_QUAL CHAR(3) ,
DOC_REF2_DT TIMESTAMP ,
DOC_REF2_DT_DESC VARCHAR(50) ,
DOC_REF2_DT_QUAL CHAR(3) ,
DOC_REF3_DT TIMESTAMP ,
DOC_REF3_DT_DESC VARCHAR(50) ,
DOC_REF3_DT_QUAL CHAR(3) ,
DOC_REF4_DT TIMESTAMP ,
DOC_REF4_DT_DESC VARCHAR(50) ,
DOC_REF4_DT_QUAL CHAR(3) ,
ADJ_INVC_CNT SMALLINT ,
ADJ_INVC_LNK_TO_THIS_DOC_IND CHAR(1) ,
ADJ_INVC_TTL_CHRG DECIMAL(19,4) ,
ADJ_INVC_TTL_HDR_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_HDR_NONVAT_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_HDR_VAT_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_LN_ITM_CHRG DECIMAL(19,4) ,
PREMIUM_TRANSPORTATION_IND CHAR(1) ,
ADJ_INVC_TTL_LN_TAX DECIMAL(19,4) ,
ZONE VARCHAR(4) ,
ADJ_INVC_TTL_LN_NONVAT_TAX DECIMAL(19,4) ,
HDR_MSG2 VARCHAR(1056) ,
HDR_MSG2_CREATE_DT TIMESTAMP ,
HDR_MSG3 VARCHAR(1056) ,
HDR_MSG3_CREATE_DT TIMESTAMP ,
MOVE_TY VARCHAR(99) ,
ADJ_INVC_TTL_LN_VAT_TAX DECIMAL(19,4) ,
NOTE2_COMPANY VARCHAR(140) ,
NOTE2_CREATEBY VARCHAR(47) ,
NOTE2_CREATE_DT TIMESTAMP ,
NOTE2_DESC VARCHAR(320) ,
NOTE2_LN_NBR VARCHAR(20) ,
NOTE3_COMPANY VARCHAR(140) ,
NOTE3_CREATEBY VARCHAR(47) ,
NOTE3_CREATE_DT TIMESTAMP ,
NOTE3_DESC VARCHAR(320) ,
NOTE3_LN_NBR VARCHAR(20) ,
NOTE3_LN_TY CHAR(2) ,
NOTE3_MSG VARCHAR(264) ,
NOTE2_LN_TY CHAR(2) ,
NOTE2_MSG VARCHAR(264) ,
OFF_CONTRACT_PRICE_IND VARCHAR(99) ,
ADJ_INVC_TTL_SC DECIMAL(19,4) ,
ADJ_INVC_TTL_TAX DECIMAL(19,4) ,
ADJ_INVC_TTL_NONVAT_TAX DECIMAL(19,4) ,
EQUIPMENT_TY VARCHAR(75) ,
INCO_TERMS VARCHAR(50) ,
INCO_TERMS_CD CHAR(3) ,
ADJ_INVC_TTL_VAT_TAX DECIMAL(19,4) ,
BILL_TO_ADDR_LN1 VARCHAR(220) ,
BILL_TO_ADDR_LN2 VARCHAR(220) ,
BILL_TO_CITY VARCHAR(30) ,
CONSOLIDATED_INVC_SHIP_IND CHAR(1) ,
UDF1_DESC VARCHAR(320) ,
UDF1_VAL VARCHAR(120) ,
UDF2_DESC VARCHAR(320) ,
UDF2_VAL VARCHAR(120) ,
UDF3_DESC VARCHAR(320) ,
UDF3_VAL VARCHAR(120) ,
REF4_DESC VARCHAR(80) ,
REF4_QUAL CHAR(3) ,
REF4_VAL VARCHAR(30) ,
REF5_DESC VARCHAR(80) ,
REF5_QUAL CHAR(3) ,
REF5_VAL VARCHAR(30) ,
REF6_DESC VARCHAR(80) ,
REF6_QUAL CHAR(3) ,
REF6_VAL VARCHAR(30) ,
REF7_DESC VARCHAR(80) ,
REF7_QUAL CHAR(3) ,
REF7_VAL VARCHAR(30) ,
BILL_TO_COUNTRY VARCHAR(50) ,
BILL_TO_ID VARCHAR(80) ,
DIRECTION VARCHAR(10) ,
BUSS_SEGMENT VARCHAR(99) ,
BILL_TO_ID_TY VARCHAR(50) ,
BILL_TO_ID_TY_QUAL CHAR(2) ,
BILL_TO_NM VARCHAR(240) ,
BILL_TO_POSTAL_CD VARCHAR(15) ,
BILL_TO_STATE VARCHAR(30) ,
BILL_TO_STATE_CD CHAR(2) ,
BILL_INCL_ADJ_INVC_CHRG_CWT DECIMAL(7,4) ,
BUYER_ADDNL1_ID VARCHAR(30) ,
BUYER_ADDNL1_ID_TY VARCHAR(50) ,
BUYER_ADDNL1_ID_TY_QUAL CHAR(2) ,
BUYER_ADDNL2_ID VARCHAR(30) ,
BUYER_ADDNL2_ID_TY VARCHAR(50) ,
BUYER_ADDNL2_ID_TY_QUAL CHAR(2) ,
BUYER_ADDNL3_ID VARCHAR(30) ,
BUYER_ADDNL3_ID_TY VARCHAR(50) ,
BUYER_ADDNL3_ID_TY_QUAL CHAR(2) ,
BUYER_COUNTRY VARCHAR(50) ,
BUYER_NM VARCHAR(240) ,
BUYER_POSTAL_CD VARCHAR(15) ,
BUYER_STATE VARCHAR(30) ,
BUYER_STATE_CD CHAR(2) ,

Message has been deleted

Anwei Shen

unread,

Mar 9, 2011, 1:54:39 PM3/9/11

to

......................

)
DISTRIBUTE BY HASH(DRN,
SOURCE_ID)
IN TSM003 INDEX IN IXM003 ;

ALTER TABLE FACT VOLATILE CARDINALITY;

-- DDL Statements for primary key on Table FACT

ALTER TABLE FACT
ADD PRIMARY KEY
(DRN,
SOURCE_ID);

CREATE INDEX IDX01_DOC_HDR
ON DOC_HDR ( ID );

Message has been deleted

Anwei Shen

unread,

Mar 9, 2011, 3:40:45 PM3/9/11

to

split into 2 statement.
The target table is empty, Merge insert is OK.
Slow part is merge update, 10 times than insert although nothing to
update.

From the plan show MERGE UPDATE is scanning the Target table, but
MERGE INSERT is using Primary key.

DB2 v9.1.0.7

Anwei Shen

unread,

Mar 9, 2011, 4:42:03 PM3/9/11

to

MERGE INTO FACT
USING STAGING
WHEN MATCHED
THEN
UPDATE SET (... ) = (...)

Section Code Page = 1208

Estimated Cost = 64481.765625
Estimated Cardinality = 5745.120117

End of section

Optimizer Plan:

Serge Rielau

unread,

Mar 11, 2011, 3:45:01 PM3/11/11

to

These two filters area sign that DB2 cannot prove that
you won't have two rows in the source matching the same row in the
target. So DB2 will do extra work to catch duplicates.
Ideally you should have a unique index on the columns in the ON clause.

Your staging table's partitioning should match the FACT table's
partitioning.
That will eliminate the DTQ(18)

> HSJOIN
> ( 17)
> / \
> DTQ TBSCAN
> ( 18) ( 20)
> | |
> TBSCAN Table:
> ( 19) FACT
> |
> Table:
> STAGING