Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2722
  • Last Modified:

DB2 -- COMPLEX SQL JOIN

I have some SQL which does not perform on DB2.  It was originally a couple of queries joined together in Access. I pulled the queries appart and create this join.  When issued from the command center it does not error but it does not ever return.  Is there a fundamental mistake in this query?  Is there an optimal way to approach this type of join?  Here is the sql.  

Thanks,

Jeff

SELECT OPT1.OTY_DESCRIPTION,
       OPT1.ODF_SPEC_NO,
       OPT1.OPT_SPEC_SIGN,
       OPT1.OPT_DESCRIPTION,
       OPT2.ID,
       OPT2.OPT_SPEC_SIGN,
       OPT2.OPT_DESCRIPTION,
       OPT2.ODF_SPEC_NO
       FROM (SELECT DISTINCT
                    OTY_DESCRIPTION,
                    ODF_SPEC_NO,
                    OPT_SPEC_SIGN,
                    OPT_DESCRIPTION
               FROM EPOPTTYP_TBL INNER JOIN (EPOPTION_TBL INNER JOIN EPOPTDEF_TBL ON EPOPTION_TBL.ID = ODF_TO_OPT_RT) ON EPOPTTYP_TBL.ID = OPT_TO_OTY_RT
              WHERE OPT_TO_MDL_RT = 4
                AND ODF_SP_CODE = 'S'
                AND ODF_MONTH_FROM <= 200012
                AND ODF_MONTH_TO >= 200012
                AND OPT_MONTH_FROM <= 200012
                AND OPT_MONTH_TO >=200012) OPT1
       INNER JOIN (SELECT DISTINCT
                          EPOPTION_TBL.ID,
                          OPT_SPEC_SIGN,
                          OPT_DESCRIPTION,
                          ODF_SPEC_NO
                     FROM EPOPTION_TBL INNER JOIN EPOPTDEF_TBL ON EPOPTION_TBL.ID = ODF_TO_OPT_RT
                    WHERE OPT_TO_MDL_RT = 4
                      AND ODF_SP_CODE = 'P'
                      AND ODF_MONTH_FROM <= 200012
                      AND ODF_MONTH_TO >=200012
                      AND OPT_MONTH_FROM <= 200012
                      AND OPT_MONTH_TO >= 200012) OPT2
       ON OPT1.ODF_SPEC_NO = OPT2.ODF_SPEC_NO
0
jwstairs
Asked:
jwstairs
1 Solution
 
kelfinkCommented:
Certainly is complex.

Um, is this the whole thing?  Looks like it's not all there.  If I'm counting correctly, you've not closed the nested query in the top-level FROM clause...

Also, if you could post sample definitions of the tables involved, that would help us to try running the query.

Kevin
0
 
ibroCommented:
what is the datatype of OPT_MONTH_FROM and OPT_MONTH_FROM. Is it numeric or date? Maybe you have problem with the where clause.
 The statement seems ok to me.
0
 
gregnashCommented:
Hi,
I would also ask:
- How many rows in each of the tables?
- What size is your bufferpool?
- Is the CPU being utilized at 100%, or is the disc thrashing, or neither, or both?  (The ways to find out differ depending on your platform, which you haven't named).
- What indexes do you have on the tables?

If you can answer those, I'll have a go at why your query is slow.

-Greg
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
jwstairsAuthor Commented:
The problem has been temporarily fixed.  It is no longer a requirement.  Thanks for having a look anyway.

Jeff
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
JgouldCommented:
Question has been closed as per recommendation

JGould-EE Moderator
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now