Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Questions on MERGE query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
subratarec

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Thu Jan 29, 2015 3:02 pm    Post subject: Questions on MERGE query
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jan 30, 2015 4:14 am    Post subject:
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

New User


Joined: 22 Dec 2007
Posts: 73
Location: Bangalore

PostPosted: Fri Jan 30, 2015 11:56 am    Post subject:
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    Post subject:
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

Senior Member


Joined: 21 Sep 2010
Posts: 1610
Location: NY,USA

PostPosted: Fri Jan 30, 2015 9:27 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
This topic is locked: you cannot edit posts or make replies. Db2 SQL query to convert rows into co... subratarec DB2 5 Thu Nov 17, 2016 4:51 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Problem in Running Query via JCL vickey_dw DB2 3 Tue Oct 18, 2016 11:11 pm
No new posts SQL Query optimization. arunsoods DB2 12 Sun Sep 04, 2016 6:27 am
No new posts Query on BLSR Nileshkul JCL & VSAM 2 Sat Aug 13, 2016 5:18 am


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us