Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 6968
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 HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Finding IMS resource blocker after se... Hooman24 IMS DB/DC 1 Wed Aug 16, 2017 9:17 pm
No new posts Need DB2 query to fetch previous row ! Chandan1993 DB2 10 Sat Jun 03, 2017 10:43 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us