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
 

 

Optimization of query

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

New User


Joined: 07 Jan 2008
Posts: 27
Location: bangalore

PostPosted: Tue Nov 25, 2008 3:32 pm    Post subject: Optimization of query
Reply with quote

Hi,

Can anyone try to optimize the below query:
Code:
DELETE FROM T1                             
WHERE T1_KEY LIKE 'ABC%'                       
AND SUBSTR(T1_KEY,7,7) IN                           
 (SELECT SUBSTR(T2_KEY,7,7) FROM T2   
    WHERE T2 LIKE 'XYZ%'); COMMIT;


Also can we use Joins in Delete query?

Let me know for more details.

Rgds,
Harish.


Edited: Please use BBcode when You post some code, that's rather readable...Anuj
Back to top
View user's profile Send private message

Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Wed Nov 26, 2008 2:14 am    Post subject:
Reply with quote

Hi,

For first - let's start from you - what did you try so far ?

For this
Quote:
Also can we use Joins in Delete query?
answer is yes - but your query should be structured well.
Back to top
View user's profile Send private message
Suresh Ponnusamy

Active User


Joined: 22 Feb 2008
Posts: 107
Location: New York

PostPosted: Wed Nov 26, 2008 3:00 am    Post subject:
Reply with quote

Hi Anuj

I think we cannot use join in Delete query. I tried the same in my shop, I got error message.

Could you please post an example.
Back to top
View user's profile Send private message
harishch_ch

New User


Joined: 07 Jan 2008
Posts: 27
Location: bangalore

PostPosted: Wed Nov 26, 2008 9:25 am    Post subject: Optimization of query
Reply with quote

Hi,

Posted query is the optimized one which i had done.Still it is taking huge CPU time due to more number of records.I am not able to proceed after this.


Functionality of the query(may be helpful):
Inner query returns 80k recs. Each record fetched by outer query as to search all the 80k recs, this is leading to more cpu time.


Regards,
Harish.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Wed Nov 26, 2008 9:32 am    Post subject:
Reply with quote

Hello,

Quote:
I am not able to proceed after this
As long as you continue to use a single, very expensive query, probably not.

If the appropriate data was unloaded into qsam files, the process might be made to run very acceptable.

Of course, it would mean that some programming work might need to be done. . .
Back to top
View user's profile Send private message
Anuj Dhawan

Senior Member


Joined: 22 Apr 2006
Posts: 6258
Location: Mumbai, India

PostPosted: Thu Nov 27, 2008 4:20 am    Post subject:
Reply with quote

Hi Suresh,

Let's say you wanted to delete the rows in Table-1 (T1) that also exist in Table-2 (T2). A common mistake is to assume that this will work:
Code:
DELETE T1
    INNER JOIN T2
    ON T1.SE_NUMBER = T2.SE_NUMBER


will yield error some thing like : error near Line 2, Incorrect syntax near the keyword 'INNER'.

What you need to do is delete from the table, and perform the join in the subquery, e.g.:
Code:
DELETE T1
    FROM T1
    INNER JOIN T2
    ON T1.SE_NUMBER = T2.SE_NUMBER


However, and while it may not be completely ANSI-friendly, I would prefer to do the following simply for readability:

Code:
DELETE T1
    WHERE SE_NUMBER IN 
    (
        SELECT SE_NUMBER FROM T2
    )

This makes it much clearer that your intention is to delete rows *only* from the table T1.

PS. My system is not up & 'am not keeping well - so din't test it (By the way this works with MySQL, didn't check with IBM DB2 though), please give a try or I'll have a look on this after some time, Thanks.
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Thu Nov 27, 2008 9:21 am    Post subject:
Reply with quote

Hi Anuj,

The issue isn't getting the query to work, but to get the query to work acceptably performance-wise. If there are thousands of rows in the outter query and each returns 80k rows it does not take long to get to hundreds of millions of returned rows.

Moving the process outside of sql may reduce the resources required dramatically. One example i can mention is a process that was terminated after running 3 days. The people responsible for that project asked me to look into it. Took their developers and me most of a weekend to re-do the process and the eventual soluton ran in under 3 hours. Their problem was a 4-deep cartesian product. . . . Once it was all explained, one of their people calculated the job would probably have run more than 11 days. . .

Happy Thanksgiving!
Back to top
View user's profile Send private message
harishch_ch

New User


Joined: 07 Jan 2008
Posts: 27
Location: bangalore

PostPosted: Thu Nov 27, 2008 11:47 am    Post subject: Optimization of query
Reply with quote

Hi,

Proceeding with writing a new cobol-db2 program i am closing this topic.

Regards,
Harish.
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 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
No new posts GETMAIN/FREEMAIN query Suja.Sai CICS 9 Tue Jan 31, 2017 12:01 pm


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