I get the messages shown below in the SQL trace after several DB2 tables are joined.

Can someone explain why this would happen? Why can't the join be passed? It seems this only happens with some joins, not all.

Thanks,

Francis.

(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2513) OUTER JOIN (ALL=ON or ALL=PASS) CANNOT BE PASSED
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED

This message has been edited. Last edited by: Francis Mariani,

I haven't any specific experience with DB2 (maybe the DB2 optimizer has a problem with outer joins), but if I ever get a join not passed, that I feel can really be passed properly, I force it with
SQL EDA SET OPTIMIZATION SQL

I don't think that would make it do an outer join if the adapter couldn't do one for that database, though. Are you sure you need an outer join?

What I'd like to do is to retrieve rows for data that is missing from the cross referenced tables.

Francis,

In situations like this I always resort to the SQL passthru or a view.

Francis ... try
SQL DB2 SET SQLJOIN OUTER ON

I'm reviving this question.

I tried what Piipster suggested - no difference.

I had to also add a MISSING test:

WHERE BSLC_DFLT_COMM_CUST_FAC_D.DFLT_FAC_END_DT EQ '9999-12-31' OR BSLC_DFLT_COMM_CUST_FAC_D.DFLT_FAC_END_DT IS MISSING

I'm wondering why.

Is your question about the missing test? The missing test was needed because once you do any criterion on a field, the nulls can not pass that test. Therefore you won't get the outer join anyway.

quote:

Nonexistent descendant instances affect whether parent segment instances are included in report results. The SET ALL parameter and the ALL. prefix enable you to include parent segment data in reports.

quote:

You can include parent instances with missing descendants by issuing the SET ALL parameter before executing the request.

Note: A request with WHERE or IF criteria, which screen fields in a segment that has missing instances, omits instances in the parent segment even if you use the SET ALL=ON command. To include these instances in the report, use the SET ALL=PASS command.

quote:

Example: Including Missing Segment Instances With SET ALL
The following request displays all employees, regardless of whether they have taken a course or not since the ALL=PASS command is set.

If the ALL=ON command had been used, employees that had not taken courses would have been omitted because of the WHERE criteria.

JOIN EMPDATA.PIN IN EMPDATA TO ALL TRAINING.PIN IN TRAIN2 AS JOIN1
SET ALL = PASS
TABLE FILE EMPDATA
PRINT LASTNAME AND FIRSTNAME AND COURSECODE AND EXPENSES 
BY PIN
WHERE EXPENSES GT 3000
END

There's no mention of having to add MISSING to the WHERE statement.

To further complicate things, when I add the MISSING clause in the WHERE statement, I get this in the SQL trace:

(FOC2525) FOREIGN KEY IS NOT A SUPERSET OF PRIMARY KEY FOR SEGMENT: : BSLC_D
FLT_COMM_CUST_F
(FOC2509) RDBMS-MANAGED JOIN SELECTED BUT RESULTS MAY BE INACCURATE
(FOC2524) JOIN TO A UNIQUE SEGMENT DOES NOT COVER ITS PRIMARY KEY

If the MISSING clause is not in the WHERE statement:

AGGREGATION DONE ...

Hi Francis... it sounds like what you are looking for is the ALL=PASS behaviour and from what I remember that is not supported for relational datasources. I'd use a MATCH.

KP, thanks for the information regarding relational databases and SET ALL=PASS.

Unfortunately, there's no way I'm going to figure out how to translate the following set of JOINs to MATCH, but thank you.

JOIN
BSLC_COMM_CUST_D.COMM_CUST_KEY      AND BSLC_COMM_CUST_D.DEFAULT_DT      IN BSLC_COMM_CUST_D      TO
BSLC_DFLT_COMM_CUST_F.COMM_CUST_KEY AND BSLC_DFLT_COMM_CUST_F.DEFAULT_DT IN BSLC_DFLT_COMM_CUST_F AS J1
END

JOIN
BSLC_COMM_CUST_D.COMM_CUST_KEY IN BSLC_COMM_CUST_D TO
BSLC_COMM_CUST_F.COMM_CUST_KEY IN BSLC_COMM_CUST_F AS J2
END

JOIN
BSLC_COMM_CUST_D.COMM_CUST_KEY          AND BSLC_COMM_CUST_D.DEFAULT_DT
  IN BSLC_COMM_CUST_D          TO ALL
BSLC_DFLT_COMM_CUST_FAC_D.COMM_CUST_KEY AND BSLC_DFLT_COMM_CUST_FAC_D.DEFAULT_DT
  IN BSLC_DFLT_COMM_CUST_FAC_D AS J3
END

JOIN
BSLC_DFLT_COMM_CUST_FAC_D.COMM_CUST_FAC_KEY           AND BSLC_DFLT_COMM_CUST_FAC_D.DEFAULT_DT
  IN BSLC_COMM_CUST_D                    TO ALL
BSLC_DFLT_COMM_CUST_FAC_CASH_FLOW_D.COMM_CUST_FAC_KEY AND BSLC_DFLT_COMM_CUST_FAC_CASH_FLOW_D.DEFAULT_DT
  IN BSLC_DFLT_COMM_CUST_FAC_CASH_FLOW_D AS J4
END

JOIN
BSLC_COMM_CUST_D.CNTRY_PROV_STAT_ID     IN BSLC_COMM_CUST_D     TO
BSLC_PROVINCE_STAT_D.CNTRY_PROV_STAT_ID IN BSLC_PROVINCE_STAT_D AS J5
END

JOIN
BSLC_DFLT_COMM_CUST_FAC_CASH_FLOW_D.CURRENCY_CD IN BSLC_COMM_CUST_D TO ALL
BSLC_CURR_EXCH_RATE_D.CURRENCY_CD IN BSLC_CURR_EXCH_RATE_D AS J6
END

Francis

A couple of things you could try:

1. I don't code the ALL or MULTIPLE in RDBMS joins, since it is only relevant to the WF JOIN model. The RDBMS join is always a restricted cartesian product and does not include the cardinality of the relationship in its syntax. ALL or MULTIPLE will tend to restrict WF in what it passes to RDBMS.

2. As suggested with SQL DB2 SET SQLJOIN OUTER ON, try setting the joins explicitly with LEFT_OUTER or INNER instead of relying on SET ALL.

Hope this is of some help.

Regards

John

John, LEFT_OUTER is a wf7 option I think. THanks for your suggestions.

If the other suggestions aren't working for you, I'd go with Tony's suggestion and use an RDBMS view or passsthru.

Then again, I guess it really depends on whether you are getting the correct results with acceptable response times with the WebFOCUS code you have come up with. If not... Tony's suggestions should give you better performance.

Apparently, in v7.7 a new setting is available:

SET ALL=SQL

Though I just don't understand why "a setting... can be added to your procedure so the left outer join stays a left outer join".