Removal of Outer join query in WORKFLOW_CONTEXT Table

The below SQL query is one of the important one to get output from DB Statistics report. However, from what we can see, we are not sure why the 2nd join with WORKFLOW_CONTEXT (WC2) is needed (see the yellow-highlight in the attached screenshot). If we take that join out, it ran in just a few seconds in QA, as compared to the 4 minutes run when we did the test. We have also checked with IBM as part of TS013435687 and now as per IBM recommendation we are opening a enhancement request.

SELECT WFD.NAME AS NAME ,

WC.WFD_ID AS WFD_ID ,

COUNT(DISTINCT WC.WORKFLOW_ID) AS TOTALBPS ,

COUNT(*) AS TOTAL_STEPS ,

MIN(WC.START_TIME) AS MIN_START_TIME,

MAX(WC.START_TIME) AS MAX_START_TIME

FROM WORKFLOW_CONTEXT WC

LEFT OUTER JOIN WFD

ON

(

WC.WFD_ID = WFD.WFD_ID

AND WC.WFD_VERSION = WFD.WFD_VERSION

)

,

WORKFLOW_CONTEXT WC2

WHERE WC.STEP_ID = 0

AND WC.WORKFLOW_ID = WC2.WORKFLOW_ID

GROUP BY WFD.NAME,

WC.WFD_ID

ORDER BY 3 DESC

Note: We understand that this is not a show-stopper and with regular DB Re-Indexing & Re-Org activities we can still get data faster using the above said query. Even most of customer will not even raise any concern on this query, as they might be getting output data in quick time. We are also getting outputs, but raising this enhancement request so IBM can check the SQL Query and correct it.

Again it's not on the performance, but on the correctness of the SQL Query. Currently we have extra outer join and it's not necessary at all.