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
 

 

Tuning a SQL query

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

Active User


Joined: 13 Jul 2005
Posts: 136

PostPosted: Thu Oct 08, 2009 5:29 pm    Post subject: Tuning a SQL query
Reply with quote

We are going to tune this below query

MERGE INTO S_USERS s
USING
(
SELECT COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM,
COUNT(S_uid) cnt_users,COUNT(temp_S_uid) cnt_tmp_use
FROM
( SELECT temp_S_users.*, 1 temp_S_uid,
to_number(null) S_uid
FROM temp_S_users
UNION ALL
SELECT S_users.*, to_number(null) temp_S_uid,
2 S_uid
FROM S_users
)
GROUP BY COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM
HAVING COUNT(S_UID) <> COUNT(TEMP_S_UID) AND
COUNT(temp_S_uid) = 1
) t
ON(s.COL_geid = t.COL_geid)

WHEN MATCHED THEN UPDATE SET
s.N_EMP = t.N_EMP,
s.COL_SSN = t.COL_SSN,
s.COL_SOEID = t.COL_SOEID,
s.COL_RITS = t.COL_RITS,
s.COL_EMP_STATUS = t.COL_EMP_STATUS,
s.D_HIRED = t.D_HIRED,
s.COL_EXPENSE_CODE = t.COL_EXPENSE_CODE,
s.COL_COUNTRY = t.COL_COUNTRY,
s.COL_WORK_PHONE = t.COL_WORK_PHONE,
s.T_EMAIL = t.T_EMAIL,
s.T_MGR_LAYER = t.T_MGR_LAYER,
s.COL_MGR_GEID = t.COL_MGR_GEID,
s.COL_MGMT_CODE = t.COL_MGMT_CODE,
s.D_TERM = t.D_TERM

WHEN NOT MATCHED THEN INSERT
(
COL_GEID,N_EMP,COL_SSN,COL_SOEID,COL_RITS,COL_EMP_STATUS,
D_HIRED,COL_EXPENSE_CODE,COL_COUNTRY,COL_WORK_PHONE,T_EMAIL,
T_MGR_LAYER,COL_MGR_GEID,COL_MGMT_CODE,D_TERM
)
VALUES
(
t.COL_GEID,t.N_EMP,t.COL_SSN,t.COL_SOEID,t.COL_RITS,
t.COL_EMP_STATUS,t.D_HIRED,t.COL_EXPENSE_CODE,
t.COL_COUNTRY,t.COL_WORK_PHONE,t.T_EMAIL,t.T_MGR_LAYER,
t.COL_MGR_GEID,t.COL_MGMT_CODE,t.D_TERM
);

Any idea for this.... Thanks in advance...
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 08, 2009 6:59 pm    Post subject:
Reply with quote

well, think what this query is supposed to do:

Code:

temp_user S_user  all columns     COUNT(TEMP_S_UID) COUNT(S_UID)   Result
 exists    exists   equal           

      Y       Y        Y       =>           1              1       => Discarded because COUNT(TEMP_S_UID) = COUNT(S_UID)
      Y       Y        N       =>           1              0       => Merge => update
                                      &     0              1       => Discarded because COUNT(TEMP_S_UID) = 0
      N       Y        -       =>           0              1       => Discarded because COUNT(TEMP_S_UID) = 0
      Y       N        -       =>           0              1       => Merge : Insert


all that having and counting is just bullshit
maybe you could just

MERGE INTO S_USERS s
USING (select * from temp_S_users T1 where not exists ( select from s_users S1 where all S1.columns are equal to T1.columns)...
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1281
Location: Belgium

PostPosted: Thu Oct 08, 2009 7:28 pm    Post subject:
Reply with quote

if you don't care that you do an unnecesary update when all columns are equal:

MERGE INTO S_USERS s
USING (select * from temp_S_users T1 )
...

PS. Next time use a more significant topic Title than "DB2"
Back to top
View user's profile Send private message
vicky10001
Warnings : 1

Active User


Joined: 13 Jul 2005
Posts: 136

PostPosted: Mon Oct 19, 2009 5:43 pm    Post subject:
Reply with quote

Thanks for good confirmation .. Thanks a lot GuyC.
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
No new posts Batch job tuning sgandhla Testing & Performance analysis 5 Fri Mar 24, 2017 9:41 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts SQL query not working in Cobol program. CuriousMainframer COBOL Programming 14 Wed Feb 22, 2017 5:56 pm
No new posts Omegamon for DB2 query ashek15 DB2 0 Wed Feb 15, 2017 11:25 am
No new posts Query on DB2 WLM stored procedures ashek15 DB2 8 Sun Feb 12, 2017 11:13 pm


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