IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Questions on MERGE query


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 117
Location: Bangalore

PostPosted: Thu Jan 29, 2015 3:02 pm
Reply with quote

I have a question on MERGE query.

In my project I had come across a scenario where I had to replace an Insert stmt with Merge query (to avoid -803). That's the time I got to about MERGE stmt. My code gone to production. Few days back I got a mail by saying my MERGE query is taking very long time. They said that I should not include those Columns are in ON condition (they are all primary columns) in UPDATE stmt. Example

My MERGE stmt:
-------------------------------------------------------
Code:
EXEC SQL                                 
  MERGE INTO <TABLE-NAME> AS T               
  USING (VALUES (:HOST-VAR1       
                ,:HOST-VAR2       
                ,:HOST-VAR3       
                ,:HOST-VAR4
      ,:HOST-VAR5))
  AS S(COL1     
    ,COL2     
    ,COL3     
    ,COL4
    ,COL5)
  ON  T.COL1_Primary = S.COL1
  AND T.COL2_Primary = S.COL2
  WHEN MATCHED THEN
 
  UPDATE SET [b] T.COL1_Primary = S.COL1[/b] --> I should not include.
         [b]T.COL2_Primary = S.COL2[/b] --> I should not include.
         T.COL3_Normal  = S.COL3
         T.COL4_Normal  = S.COL4
         T.COl5_Normal  = S.COL5

  WHEN NOT MATCHED THEN
  INSET (COL1     
        ,COL2     
        ,COL3     
        ,COL4
        ,COL5)
  VALUES(:HOST-VAR1       
        ,:HOST-VAR2       
        ,:HOST-VAR3       
        ,:HOST-VAR4
        ,:HOST-VAR5)


Both the columns mentioned above are Primary Key for this table. I know "WHEN MATCHED THEN" then no need to include Primary key column under UPDATE stmt because it is overwriting the same value but will this slow down the performance?

Is it something like every time I overwrite primary key column value with same value it is rebuilding the entire index ??

Thanks in advance
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2727
Location: NYC,USA

PostPosted: Fri Jan 30, 2015 4:14 am
Reply with quote

How about the indexes on -T.COL1_Primary and T.COL2_Primary ? Are you sure they have right indexes defined?

Code:
UPDATE SET [b] T.COL1_Primary = S.COL1[/b] --> I should not include.
         [b]T.COL2_Primary = S.COL2[/b] --> I should not include.


That's right, why wouyld you update these keys anyways? Have you thought of Reorg and Runstat on this table <if this is oftenly get inserted/updated/deleted>? if not then please have a process in place.
Back to top
View user's profile Send private message
subratarec

Active User


Joined: 22 Dec 2007
Posts: 117
Location: Bangalore

PostPosted: Fri Jan 30, 2015 11:56 am
Reply with quote

Hi Rohit, Yes T.COL1_Primary and T.COL2_Primary are used in Index. So Index contains 2 columns (T.COL1_Primary and T.COL2_Primary).

I thought the same that every time my SQL updating (not actually updates with new value but over writing the same value) DB2 is rebuilding the same index again and again. But I just wanted to confirm whether my understanding is correct or not.

Thanks
Back to top
View user's profile Send private message
Mickeydusaor

Active User


Joined: 24 May 2006
Posts: 258
Location: Salem, Oregon

PostPosted: Fri Jan 30, 2015 8:01 pm
Reply with quote

Why no handle the -803 and get rid of the overhead of the merge
Back to top
View user's profile Send private message
Rohit Umarjikar

Global Moderator


Joined: 21 Sep 2010
Posts: 2727
Location: NYC,USA

PostPosted: Fri Jan 30, 2015 9:27 pm
Reply with quote

Quote:
Why no handle the -803 and get rid of the overhead of the merge


Right, but if he does that then there will be two EXEC SQL threads(INSERT and UPDATE) instead one (with MERGE).

Subratarec,

Quote:
I thought the same that every time my SQL updating (not actually updates with new value but over writing the same value) DB2 is rebuilding the same index again and again. But I just wanted to confirm whether my understanding is correct or not.


Yes Correct and I hope your respective DBA has told you the same.

But however you still got to check upon REORG, RUNSTAT, REBIND options as to how often is required.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts DB2: Need helping below DB2 query DB2 13
No new posts DB2 SQL query to read and update data... DB2 12
No new posts Cobol-db2 : SQL query is running for ... DB2 4
No new posts Reading subsequent rows in a query. DB2 12
No new posts SYSIBM Tables Query DB2 8
Search our Forums:

Back to Top