Hello all,

For this, I have 1 large history table (30mio records).
I have to merge a daily delta table (4mio records) into it.

To do this, I wrote the stmt below (truncated because the msg is too long for this window). I tested the syntax with just a few lines and it works. Now, the query is running in production but it seems to take a very long time. in the task manager on the server, I can see almost no CPU-usage or extra memory consumption.

What can be wrong ?
Which parameters to check or increase ?
Also, db2advis doesn't give anything back because it seems to be captured as a non valid sql stmt ....

Many thanks in advance

Peter



CONNECT TO NEWSTEP user db2admin using admindb2;
MERGE INTO DIFOT.EMEA_FEED_HIST as dest
USING (SELECT KEY_SLS_ORDR_FACT_ID, KEY_DLVRY_FACT_ID, SOLD_TO_CUST, REGION, SUBRGN, SUBRGN_DESC,
ACCT_CUST_GRP_CODE, ACCT_SPLY_CHN_GRP_CODE, SLS_ORG_CODE,
SLS_ORG_DESC, PLNT_LOCTN_CODE, DIVISION,
SLS_DOC_HDR_NUM_CODE, SLS_DOC_LN_ITM_NUM_CODE,
SLS_DOC_SCHDL_LN_NUM_CODE, DLVRY_DOC_HDR_NUM_CODE,
DLVRY_DOC_SCHDL_LN_NUM_CODE, KEY_CNCL_DATE_DIM_ID,
SHP_TO_CUST, SHP_TO_COUNTRY, MATRL, KEY_CCD_DATE_DIM_ID,
BUCKET_MONTH_DATE, KEY_CRD_DATE_DIM_ID, KEY_MAD_DIM_ID,
DATE_OF_DLVR_DUE, CANCEL_DATE, AGID_DATE, PGID_DATE,
SO_IDP_DATE, SCHDLD_BKNG_IN_DATE, PLND_SCHDLD_DT,
OUTGOING_DT, AFS_CAT_CODE, CRDT_STAT_CODE, SO_ITM_CAT_CODE,
SO_IDP_FLAG, DLVRY_CNT, ORDR_ENTRY_QTY, UNCNFRMD_QTY,
CNFRMD_QTY, BAS_DLVRY_QTY, RSRVD_UNIT_QTY, OPEN_QTY,
UNASSIGNED_QTY, ALLOC_F_QTY, ALLOC_T_QTY, DLVRY_UNIT_QTY,
RJCTD_UNIT_QTY, UOM_CNVRSN_FCTR, SOS_BAS_UOM, CAT_CD,
CHNL_CLASS, LIFECYCLE, SUB_CAT_CODE, TYPE_GRP, ACCT_TYPE,
ACCT_TYPE_DESC, DIRECT_SHIP_FLAG, QUALITY_CD, ALLOC_D_QTY,
SCHED_SC_QTY, SCHED_PH_QTY, CONTRACT_QTY, CONTRACT_FLAG,
CREDIT_FLAG, DIST_METHOD, ALLOC_FB_QTY, ALLOC_FNB_QTY,
EMEA_RPT_GRP, HOLD_TYPE, DLVRY_HOLD_QTY, ORDR_TYPE_CODE,
CREDIT_HOLD_QTY, SHIP_ALLOC_QTY, SHIP_IN_PROCESS_QTY,
FIRST_PRODUCT_OFFER_DATE, FLAG_AT_ONCE, FLAG_BGRADE,
FLAG_CLOSEOUTS, FLAG_FUTURES, FLAG_PARTNERS, FLAG_STRATEGIC,
MARKETING_CODE, LAUNCH_INDICATOR, CRDNTD_RLSE_CODE,
CUST_ORDR_NUM_CODE, CMPLTNSS_FLAG, SLS_OFFICE,
CNTRCT_START_DATE, CNTRCT_END_DATE, CNTRCT_DOC_HDR_NUM_CODE,
OB_DLVRY_BLCK_CODE, RSRVD_IN_TRANSIT_QTY, RSRVD_PO_QTY,
RSRVD_ON_HAND_QTY, DELIVERY_PRIORITY, DLVRY_GRP_CODE,
MATRL_SIZE_CODE, SO_DLVRY_BLCK_CODE, SO_LN_DLVRY_BLCK_CODE,
RTE_CODE, SHPPNG_COND, LDNG_PNT_LOCTN, ZZZ_RCRD_CREATE_DTTM,
IDP_DATE, IDP_FLAG
FROM DIFOT.EMEA_LOAD) as source
ON ( dest.KEY_SLS_ORDR_FACT_ID = source. KEY_SLS_ORDR_FACT_ID AND
dest.KEY_DLVRY_FACT_ID = source. KEY_DLVRY_FACT_ID)
WHEN MATCHED THEN
UPDATE SET
SOLD_TO_CUST = source.SOLD_TO_CUST ,
REGION = source.REGION,
SUBRGN = source.SUBRGN,
SUBRGN_DESC = source.SUBRGN_DESC ,
ACCT_CUST_GRP_CODE = source.ACCT_CUST_GRP_CODE ,
ACCT_SPLY_CHN_GRP_CODE = source.ACCT_SPLY_CHN_GRP_CODE ,
SLS_ORG_CODE = source.SLS_ORG_CODE ,
SLS_ORG_DESC = source.SLS_ORG_DESC ,
PLNT_LOCTN_CODE = source.PLNT_LOCTN_CODE ,
DIVISION = source.DIVISION ,
SLS_DOC_HDR_NUM_CODE = source.SLS_DOC_HDR_NUM_CODE ,
SLS_DOC_LN_ITM_NUM_CODE = source.SLS_DOC_LN_ITM_NUM_CODE ,
SLS_DOC_SCHDL_LN_NUM_CODE = source.SLS_DOC_SCHDL_LN_NUM_CODE ,
DLVRY_DOC_HDR_NUM_CODE = source.DLVRY_DOC_HDR_NUM_CODE ,
DLVRY_DOC_SCHDL_LN_NUM_CODE = source.DLVRY_DOC_SCHDL_LN_NUM_CODE ,
KEY_CNCL_DATE_DIM_ID = source.KEY_CNCL_DATE_DIM_ID ,
SHP_TO_CUST = source.SHP_TO_CUST ,
SHP_TO_COUNTRY = source.SHP_TO_COUNTRY ,
MATRL = source.MATRL ,
KEY_CCD_DATE_DIM_ID = source.KEY_CCD_DATE_DIM_ID ,
BUCKET_MONTH_DATE = source.BUCKET_MONTH_DATE ,
KEY_CRD_DATE_DIM_ID = source.KEY_CRD_DATE_DIM_ID ,
KEY_MAD_DIM_ID = source.KEY_MAD_DIM_ID ,
DATE_OF_DLVR_DUE = source.DATE_OF_DLVR_DUE ,
CANCEL_DATE = source.CANCEL_DATE ,
AGID_DATE = source.AGID_DATE ,
PGID_DATE = source.PGID_DATE ,
SO_IDP_DATE = source.SO_IDP_DATE ,
SCHDLD_BKNG_IN_DATE = source.SCHDLD_BKNG_IN_DATE ,
PLND_SCHDLD_DT = source.PLND_SCHDLD_DT ,
OUTGOING_DT = source.OUTGOING_DT ,
AFS_CAT_CODE = source.AFS_CAT_CODE ,
CRDT_STAT_CODE = source.CRDT_STAT_CODE ,
SO_ITM_CAT_CODE = source.SO_ITM_CAT_CODE ,
SO_IDP_FLAG = source.SO_IDP_FLAG ,
DLVRY_CNT = source.DLVRY_CNT ,
ORDR_ENTRY_QTY = source.ORDR_ENTRY_QTY ,
UNCNFRMD_QTY = source.UNCNFRMD_QTY ,
CNFRMD_QTY = source.CNFRMD_QTY ,
BAS_DLVRY_QTY = source.BAS_DLVRY_QTY ,
RSRVD_UNIT_QTY = source.RSRVD_UNIT_QTY ,
OPEN_QTY = source.OPEN_QTY ,
UNASSIGNED_QTY = source.UNASSIGNED_QTY ,
ALLOC_F_QTY = source.ALLOC_F_QTY ,
ALLOC_T_QTY = source.ALLOC_T_QTY ,
DLVRY_UNIT_QTY = source.DLVRY_UNIT_QTY ,
RJCTD_UNIT_QTY = source.RJCTD_UNIT_QTY ,
UOM_CNVRSN_FCTR = source.UOM_CNVRSN_FCTR ,
SOS_BAS_UOM = source.SOS_BAS_UOM ,
CAT_CD = source.CAT_CD ,
CHNL_CLASS = source.CHNL_CLASS ,
LIFECYCLE = source.LIFECYCLE ,
SUB_CAT_CODE = source.SUB_CAT_CODE ,
TYPE_GRP = source.TYPE_GRP ,
ACCT_TYPE = source.ACCT_TYPE ,
ACCT_TYPE_DESC = source.ACCT_TYPE_DESC ,
DIRECT_SHIP_FLAG = source.DIRECT_SHIP_FLAG,
QUALITY_CD = source.QUALITY_CD ,
ALLOC_D_QTY = source.ALLOC_D_QTY ,
SCHED_SC_QTY = source.SCHED_SC_QTY ,
SCHED_PH_QTY = source.SCHED_PH_QTY ,
CONTRACT_QTY = source.CONTRACT_QTY ,
CONTRACT_FLAG = source.CONTRACT_FLAG ,
CREDIT_FLAG = source.CREDIT_FLAG ,
DIST_METHOD = source.DIST_METHOD ,
ALLOC_FB_QTY = source.ALLOC_FB_QTY ,
ALLOC_FNB_QTY = source.ALLOC_FNB_QTY ,
EMEA_RPT_GRP = source.EMEA_RPT_GRP ,
HOLD_TYPE = source.HOLD_TYPE ,
DLVRY_HOLD_QTY = source.DLVRY_HOLD_QTY ,
ORDR_TYPE_CODE = source.ORDR_TYPE_CODE ,
CREDIT_HOLD_QTY = source.CREDIT_HOLD_QTY ,
SHIP_ALLOC_QTY = source.SHIP_ALLOC_QTY ,
SHIP_IN_PROCESS_QTY = source.SHIP_IN_PROCESS_QTY,
FIRST_PRODUCT_OFFER_DATE = source.FIRST_PRODUCT_OFFER_DATE ,
FLAG_AT_ONCE = source.FLAG_AT_ONCE ,
FLAG_BGRADE = source.FLAG_BGRADE ,
FLAG_CLOSEOUTS = source.FLAG_CLOSEOUTS ,
FLAG_FUTURES = source.FLAG_FUTURES ,
FLAG_PARTNERS = source.FLAG_PARTNERS ,
FLAG_STRATEGIC = source.FLAG_STRATEGIC ,
MARKETING_CODE = source.MARKETING_CODE ,
LAUNCH_INDICATOR = source.LAUNCH_INDICATOR ,
CRDNTD_RLSE_CODE = source.CRDNTD_RLSE_CODE ,
CUST_ORDR_NUM_CODE = source.CUST_ORDR_NUM_CODE ,
CMPLTNSS_FLAG = source.CMPLTNSS_FLAG ,
SLS_OFFICE = source.SLS_OFFICE ,
CNTRCT_START_DATE = source.CNTRCT_START_DATE ,
CNTRCT_END_DATE = source.CNTRCT_END_DATE ,
CNTRCT_DOC_HDR_NUM_CODE = source.CNTRCT_DOC_HDR_NUM_CODE ,
OB_DLVRY_BLCK_CODE = source.OB_DLVRY_BLCK_CODE ,
RSRVD_IN_TRANSIT_QTY = source.RSRVD_IN_TRANSIT_QTY ,
RSRVD_PO_QTY = source.RSRVD_PO_QTY ,
RSRVD_ON_HAND_QTY = source.RSRVD_ON_HAND_QTY ,
DELIVERY_PRIORITY = source.DELIVERY_PRIORITY ,
DLVRY_GRP_CODE = source.DLVRY_GRP_CODE ,
MATRL_SIZE_CODE = source.MATRL_SIZE_CODE ,
SO_DLVRY_BLCK_CODE = source.SO_DLVRY_BLCK_CODE ,
SO_LN_DLVRY_BLCK_CODE = source.SO_LN_DLVRY_BLCK_CODE ,
RTE_CODE = source.RTE_CODE ,
SHPPNG_COND = source.SHPPNG_COND ,
LDNG_PNT_LOCTN = source.LDNG_PNT_LOCTN ,
ZZZ_RCRD_CREATE_DTTM = source.ZZZ_RCRD_CREATE_DTTM ,
IDP_DATE = source.IDP_DATE ,
IDP_FLAG = IDP_FLAG
WHEN NOT MATCHED THEN
INSERT(KEY_SLS_ORDR_FACT_ID, KEY_DLVRY_FACT_ID, SOLD_TO_CUST, REGION, SUBRGN, SUBRGN_DESC,
ACCT_CUST_GRP_CODE, ACCT_SPLY_CHN_GRP_CODE, SLS_ORG_CODE,
SLS_ORG_DESC, PLNT_LOCTN_CODE, DIVISION,
SLS_DOC_HDR_NUM_CODE, SLS_DOC_LN_ITM_NUM_CODE,
SLS_DOC_SCHDL_LN_NUM_CODE, DLVRY_DOC_HDR_NUM_CODE,
DLVRY_DOC_SCHDL_LN_NUM_CODE, KEY_CNCL_DATE_DIM_ID,
SHP_TO_CUST, SHP_TO_COUNTRY, MATRL, KEY_CCD_DATE_DIM_ID,
BUCKET_MONTH_DATE, KEY_CRD_DATE_DIM_ID, KEY_MAD_DIM_ID,
DATE_OF_DLVR_DUE, CANCEL_DATE, AGID_DATE, PGID_DATE,
SO_IDP_DATE, SCHDLD_BKNG_IN_DATE, PLND_SCHDLD_DT,
OUTGOING_DT, AFS_CAT_CODE, CRDT_STAT_CODE, SO_ITM_CAT_CODE,
SO_IDP_FLAG, DLVRY_CNT, ORDR_ENTRY_QTY, UNCNFRMD_QTY,
CNFRMD_QTY, BAS_DLVRY_QTY, RSRVD_UNIT_QTY, OPEN_QTY,
UNASSIGNED_QTY, ALLOC_F_QTY, ALLOC_T_QTY, DLVRY_UNIT_QTY,
RJCTD_UNIT_QTY, UOM_CNVRSN_FCTR, SOS_BAS_UOM, CAT_CD,
CHNL_CLASS, LIFECYCLE, SUB_CAT_CODE, TYPE_GRP, ACCT_TYPE,
ACCT_TYPE_DESC, DIRECT_SHIP_FLAG, QUALITY_CD, ALLOC_D_QTY,
SCHED_SC_QTY, SCHED_PH_QTY, CONTRACT_QTY, CONTRACT_FLAG,
CREDIT_FLAG, DIST_METHOD, ALLOC_FB_QTY, ALLOC_FNB_QTY,
EMEA_RPT_GRP, HOLD_TYPE, DLVRY_HOLD_QTY, ORDR_TYPE_CODE,
CREDIT_HOLD_QTY, SHIP_ALLOC_QTY, SHIP_IN_PROCESS_QTY,
FIRST_PRODUCT_OFFER_DATE, FLAG_AT_ONCE, FLAG_BGRADE,
FLAG_CLOSEOUTS, FLAG_FUTURES, FLAG_PARTNERS, FLAG_STRATEGIC,
MARKETING_CODE, LAUNCH_INDICATOR, CRDNTD_RLSE_CODE,
CUST_ORDR_NUM_CODE, CMPLTNSS_FLAG, SLS_OFFICE,
CNTRCT_START_DATE, CNTRCT_END_DATE, CNTRCT_DOC_HDR_NUM_CODE,
OB_DLVRY_BLCK_CODE, RSRVD_IN_TRANSIT_QTY, RSRVD_PO_QTY,
RSRVD_ON_HAND_QTY, DELIVERY_PRIORITY, DLVRY_GRP_CODE,
MATRL_SIZE_CODE, SO_DLVRY_BLCK_CODE, SO_LN_DLVRY_BLCK_CODE,
RTE_CODE, SHPPNG_COND, LDNG_PNT_LOCTN, ZZZ_RCRD_CREATE_DTTM,
IDP_DATE, IDP_FLAG)
VALUES(
source.KEY_SLS_ORDR_FACT_ID, ....);

1. Make sure you have composite index on join columns for the historical table (you can also try creating the same index on a delta table)
2. Make sure statistics are up to date on both tables.
3. Post an access plan

Considering the amount of data, I would assume that the best access plan would be somewhere in
1. table scan over the delta and netsted loops index lookups for history table
2. index scan over composite keys(depends on cluster ratio of the key) and merge join
3. hash join (depends on resources and memory parameters), least likely

Hello,

Thanks for the response. Here is the access plan. Hope this helps you to give me some advice .....
I cut a whole part out in the middle. If you can help me more with the whole file, can you please give me your e-mail because here I can only paste 10000 chars

Plan:
-----------
Total Cost: 2.63619e+007
Query Degree: 0

Rows
RETURN
( 1)
Cost
I/O
|
15102
INSERT
( 2)
2.63619e+007
5.63197e+006
/----+----\
15102 3.27545e+007
TBSCAN TABLE: DIFOT
( 3) EMEA_FEED_HIST
2.63538e+007
5.63122e+006
|
15102
TEMP
( 4)
2.63512e+007
5.62933e+006
|
15102
UPDATE
( 5)
2.6331e+007
5.62744e+006
/----+----\
15102 3.27545e+007
FETCH TABLE: DIFOT
( 6) EMEA_FEED_HIST
2.61692e+007
5.61234e+006
/----+----\
15102 3.27545e+007
NLJOIN TABLE: DIFOT
( 7) EMEA_FEED_HIST
2.60074e+007
5.59724e+006
/---------+---------\
188775 0.08
FILTER UNION
( 8) ( 19)
2.60071e+007 0.00107591
5.59724e+006 0
| /------+-----\
4.71938e+006 0.04 0.04
TBSCAN FILTER FILTER
( 9) ( 20) ( 22)
2.6002e+007 0.000428652 0.000428652
5.59724e+006 0 0
| | |
4.71938e+006 1 1
SORT TBSCAN TBSCAN
( 10) ( 21) ( 23)
2.49746e+007 2.83407e-005 2.83407e-005
4.85207e+006 0 0
| | |
4.71938e+006 1 1
MSJOIN TABFNC: SYSIBM TABFNC: SYSIBM
( 11) GENROW GENROW
1.69621e+007
4.1069e+006
/-----+----\
4.71938e+006 7.54043e-006
TBSCAN FILTER
( 12) ( 15)
8.91488e+006 6.28638e+006
1.65215e+006 2.29182e+006
| |
4.71938e+006 3.27545e+007
SORT TBSCAN
( 13) ( 16)
7.93902e+006 6.28023e+006
944246 2.29182e+006
| |
4.71938e+006 3.27545e+007
TBSCAN SORT
( 14) ( 17)
326941 5.81384e+006
236339 1.96591e+006
| |
4.71938e+006 3.27545e+007
TABLE: DIFOT TBSCAN
EMEA_LOAD ( 18)
2.26865e+006
1.63999e+006
|
3.27545e+007
TABLE: DIFOT
EMEA_FEED_HIST



..
..
..


Objects Used in Access Plan:
---------------------------

Schema: DIFOT
Name: EMEA_FEED_HIST
Type: Table
Time of creation: 2004-02-01-06.37.54.546000
Last statistics update: 2004-02-04-11.29.08.796000
Number of columns: 107
Number of rows: 32754501
Width of rows: 787
Number of buffer pool pages: 1639992
Distinct row values: No
Tablespace name: DIFOT
Tablespace overhead: 10.670000
Tablespace transfer rate: 0.040000
Source for statistics: Single Node
Prefetch page count: 64
Container extent page count: 64
Table overflow record count: 0
Table Active Blocks: -1

Schema: DIFOT
Name: EMEA_LOAD
Type: Table
Time of creation: 2004-02-03-10.51.09.846001
Last statistics update: 2004-02-04-11.01.34.155000
Number of columns: 107
Number of rows: 4719380
Width of rows: 786
Number of buffer pool pages: 236339
Distinct row values: No
Tablespace name: DIFOT
Tablespace overhead: 10.670000
Tablespace transfer rate: 0.040000
Source for statistics: Single Node
Prefetch page count: 64
Container extent page count: 64
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

It's really difficult to read such collapsed access plan. Can you create a seperate file and make an attachment?

Can you create unique indexes on joins keys for both tables and try again?

I'm sorry. I didn't realise that attachments were possible.
Here's the complete explain.

I will make sure unique keys exist and then re-run the query (and explain)

Thanks already