DB2 Newby Query problem

[thendrickson]

I am very new with DB2.

I have a pass through query from Access to a DB2 database that seems to run forever (or until the ODBC timeout if set)
If I set the ODBC time out to 0 in Access (never tiems out) the query runs at leat 30 minutes before we killed it.

I am afraid I do not understand the query.

I am posting the whole query, but what I totally do not understand is the 4th line "Select 5".

I find similar in several other queries (different numbers)
which "work fine" according to the end user.

If someone has the time to look over the query and give me some ideas where to start, I would appreciate it.

INSERT INTO M5T1 (METRICID, DTE214, ENDDAYOFWEEK, DAYS214, CSTNO, ORDNO, ORDTYP, STATE, REQDTE, CSTCLS, DAYSREQDTE, TRANSITDAYS, GROSSDAYS, NETDAYS, ONTIME)
SELECT 5, H.*, I.NETDAYS,
CASE WHEN (I.NETDAYS=0) THEN 1 ELSE 0
END
AS ONTIME

FROM (
SELECT E.DTE214, E.ENDDAYOFWEEK, E.DAYS214, F.* ,
( DAYS214-TRANSITDAYS-DAYSREQDTE ) AS GROSS

FROM (
SELECT ORDNO, DTE214, DAYOFWEEK(DTE214) AS ENDDAYOFWEEK, DAYS(DTE214) AS DAYS214
FROM (
SELECT POD.ORDNO, CAST(POD.DATE214 AS DATE ) AS DTE214
FROM EDILIB.SC214 POD
) A ) E,
(
SELECT C.*, T.TRANSITDAYS
FROM (
SELECT A.*, B.CSTCLS, DAYS(REQDTECHAR) AS DAYSREQDTE
FROM
(
SELECT O.CSTNO, O.ORDNO, O.ORDTYP, O.STATE,
CASE
WHEN REQDTE > 99999 THEN '20' || SUBSTRING ( CAST(REQDTE AS CHAR(6)) ,5,2) || '-' || SUBSTRING ( CAST(REQDTE AS CHAR(6)) ,1,2) || '-' || SUBSTRING ( CAST(REQDTE AS CHAR(6)) ,3,2)
ELSE '20' || SUBSTRING ( CAST(REQDTE AS CHAR(6)) ,4,2) || '-0' || SUBSTRING ( CAST(REQDTE AS CHAR(6)) ,1,1) || '-' || SUBSTRING ( CAST(REQDTE AS CHAR(6)) ,2,2)
END AS REQDTECHAR

FROM CCSDTA.DOPORDM0 O , COTYLIB.SCORDLOG L
WHERE O.ORDNO = L.SCORDERNO

AND ( L.SCNEWSTAT = '7'
AND L.SCLGDATE >= (SELECT MONBEGDTE FROM SCORECARD.SCDATES WHERE ACTIVE<>0 )
AND L.SCLGDATE <= (SELECT MONENDDTE FROM SCORECARD.SCDATES WHERE ACTIVE<>0) )
) A,

(SELECT DISTINCT CSTCLS, CSTNO FROM CCSDTA.DOPCMST0) B
WHERE A.CSTNO = B.CSTNO
) C, SCTRANSIT T

WHERE C.STATE = T.STATE

) F
WHERE E.ORDNO = F.ORDNO

) H, SCORECARD.SCNETDAYS I
WHERE H.GROSS = I.GROSSDAYS
AND H.ENDDAYOFWEEK = I.ENDDAYOFWEEK

 

[PruSQLer]

The "5" is exactly what it says - 5. That will come out on the result row as the number 5. At least, that's what happens in DB2.

That query has ALOT of nested subqueries which may not be the most efficient way to accomplish the goal and may explain why it runs so long. How much data are we talking about though?

If you know what the target result is, you might consider rewriting the query in a more efficient way.

 

[thendrickson]

Thank you,
That was what I somewhat suspected since it seemed the logical conclusion. I have never before seen it used.

Yes there are a lot of sub queries.

If I understand the logic of this query, then each time the case evaluates then each sub query evaluates. Is this correct?

This is my first experience with DB2, although I have worked with MSSQL and Access quite a bit. Seldom used subqueries.

I will need to rewrite this query if I can figure out how once I get on site. (I have been working with the Access MDB they sent me)

There is a lot of data, that is certain. And the whole design does not look efficient (from my experience) so I have a lot of detective work to do yet.

Also please note the comma following I.Netdays in the same line.
I would not think that should be there.

Again, I appreciate the help.

 

[thendrickson]

Just a follow up if any one is interested.
Turns out that a huge table used by this and several other applications had no indexes whatsoever.

Other tables were not indexed properly

Once I got on site and figured out how to find my way around iNavigator the answer was pretty simple.

Additionally "A" is used as an alias twice in one query.
Not really certain what that would do, but it cannot be a good thing.

So I fixed the indexes, fixed the alias "problem" and everthing is fine now.