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
 

 

DB2 QUERY taking too much of resource.

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

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Thu Aug 21, 2008 9:04 pm    Post subject: DB2 QUERY taking too much of resource.
Reply with quote

Hi,

This query Seem to be little tricky for me, somebody solve this puzzle or suggest me something. The query doesn't have any errors. But it takes too much of resource and it backs out. The 2 inner queries are nearly same except the AND condition. I even tried stopped the RESOURCE LIMIT. But its taking infinite time, so i had to terminate the session.
This is the code

Code:

SELECT  DISTINCT OA.AN_ANNUITANT_TIN                                   
  ,D.LSWC_POL_STAT_CD                                               
  , D.LSWC_POL_NO                                                   
  , OA.AN_FULL_NAME                                                   
  FROM  INEDATAT.ANNUITANT OA                                           
  INNER                                                               
  JOIN  INEDATAT.INET_LSW_CONTRACT D                                   
    ON  OA.AN_ANNUITANT_TIN  IN
(SELECT  DISTINCT A.AN_ANNUITANT_TIN                                   
  FROM  INEDATAT.ANNUITANT A                                           
  INNER                                                               
  JOIN  INEDATAT.INET_LSW_CONTRACT D                                   
    ON  A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM           
        INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
        = 2 )                                                         
  INNER                                                               
  JOIN  INEDATAT.POL_ANNUITANT_REL B                                   
    ON  A.AN_ANNUITANT_KEY = B.AN_ANNUITANT_KEY                       
    AND B.LSWC_POL_NO = D.LSWC_POL_NO                                 
    AND (D.LSWC_POL_STAT_CD = 'A' OR D.LSWC_POL_STAT_CD = 'B' OR       
         D.LSWC_POL_STAT_CD = 'E'))
and

OA.AN_ANNUITANT_TIN  IN
(SELECT  DISTINCT A.AN_ANNUITANT_TIN                                   
  FROM  INEDATAT.ANNUITANT A                                           
  INNER                                                               
  JOIN  INEDATAT.INET_LSW_CONTRACT D                                   
    ON  A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM           
        INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
        = 2 )                                                         
  INNER                                                               
  JOIN  INEDATAT.POL_ANNUITANT_REL B                                   
    ON  A.AN_ANNUITANT_KEY = B.AN_ANNUITANT_KEY                       
    AND B.LSWC_POL_NO = D.LSWC_POL_NO                                 
AND (D.LSWC_POL_STAT_CD NOT LIKE 'A' AND 
     D.LSWC_POL_STAT_CD NOT LIKE 'B' AND 
     D.LSWC_POL_STAT_CD NOT LIKE 'E'))     
;                                     


This is the 2 conditions codes in the inner query without these condition codes both the queries are same, alike.
Code:
    AND (D.LSWC_POL_STAT_CD = 'A' OR
             D.LSWC_POL_STAT_CD = 'B' OR       
             D.LSWC_POL_STAT_CD = 'E')

AND
Code:
AND (D.LSWC_POL_STAT_CD NOT LIKE 'A' AND 
     D.LSWC_POL_STAT_CD NOT LIKE 'B' AND 
     D.LSWC_POL_STAT_CD NOT LIKE 'E')


Query Purpose : A.AN_ANNUITANT_TIN column can have any number of duplicates. The query will return only records having 2 duplicate A.AN_ANNUITANT_TIN. After finding that inner joins are performed.
The result will be having 2 sets of duplicates. Iam not being clear i think.

This is the example,
Code:
SELECT  A.AN_ANNUITANT_TIN                                   
  ,D.LSWC_POL_STAT_CD
  ,D.LSWC_POL_NO                                                   
  FROM  INEDATAT.ANNUITANT A                                           
  INNER                                                               
  JOIN  INEDATAT.INET_LSW_CONTRACT D                                   
    ON  A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM           
        INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
        = 2 )                                                         
  INNER                                                               
  JOIN  INEDATAT.POL_ANNUITANT_REL B                                   
    ON  A.AN_ANNUITANT_KEY = B.AN_ANNUITANT_KEY                       
    AND B.LSWC_POL_NO = D.LSWC_POL_NO                                 
;

When the above query is executed this is the result.
Code:
A.AN_ANNUITANT_TIN LSWC_POL_STAT_CD  D.LSWC_POL_NO
002328787                   A                              1234
002328787                   A                              5678
002449241                   A                              1010
002449241                   A                              1111
003428906                   A                              1212
003428906                   T                              4444


The query i want should return only the rows having for example
Desired RESULT
Code:
A.AN_ANNUITANT_TIN LSWC_POL_STAT_CD  D.LSWC_POL_NO
002328787                   A                              1234
002328787                   T                              5678
002449241                   A                              1010
002449241                   T                              1111
003428906                   A                              1212
003428906                   T                              4444


one TIN number should have STAT_CD as 'A' and same TIN number other should have 'T'. Only difference is the policy numbers.

Same TIN number one should had STAT_Cd as 'A' and other should have 'T'.

That's it.
Back to top
View user's profile Send private message

dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6967
Location: porcelain throne

PostPosted: Fri Aug 22, 2008 12:46 am    Post subject:
Reply with quote

often a distinct can be resolved with a GROUP BY thus removing the DISTINCT.
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Sun Aug 24, 2008 1:42 pm    Post subject: Reply to: DB2 QUERY taking too much of resource.
Reply with quote

Try rewriting your query like this ....
Code:


SELECT  DISTINCT OA.AN_ANNUITANT_TIN                                   
  ,D.LSWC_POL_STAT_CD                                               
  , D.LSWC_POL_NO                                                   
  , OA.AN_FULL_NAME                                                   
  FROM  INEDATAT.ANNUITANT OA                                           
  INNER                                                               
  JOIN  INEDATAT.INET_LSW_CONTRACT D
Here use common cols of OA and D for join ...
INNER                                                               
  JOIN  INEDATAT.POL_ANNUITANT_REL B
ON B.LSWC_POL_NO = D.LSWC_POL_NO                                   
                                   
AND  OA.AN_ANNUITANT_TIN  IN
(SELECT  DISTINCT A.AN_ANNUITANT_TIN                                   
  FROM  INEDATAT.ANNUITANT A                                           
  INNER                                                               
  JOIN  INEDATAT.INET_LSW_CONTRACT D   
Here use common cols for A and D for join ....
AND  A.AN_ANNUITANT_TIN IN ( SELECT AN_ANNUITANT_TIN FROM           
        INEDATAT.ANNUITANT GROUP BY AN_ANNUITANT_TIN HAVING COUNT ( * )
        = 2 )                                                         
                                   
    AND ( (D.LSWC_POL_STAT_CD IN ( 'A','B','E'))  OR ( D.LSWC_POL_STAT_CD NOT IN ( 'A', 'B','E'))))

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