Argh! Argh!

I am in the middle of parallel testing all our reports on our Production v5.3.4 system and on our Parallel v7.6.5 system.

Besides some minor issues and a major problem with styling ACROSS columns I now have uncovered this major issue.

DB2 Joins seem to be interpreted differently between the two versions. I diligently turn on SQL traces and when I developed the report on v5.3.4 I got no informational messages in the SQL trace other than "AGGREGATION DONE ...". Now, in v7.6.5 I get:

FOC2506 - INTERFACE-MANAGED NATIVE JOIN SELECTED FOR THE FOLLOWING REASON:
FOC2516 - MULTIPLICATIVE EFFECT WOULD ENSUE
FOC2590 - AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
FOC2592 - RDBMS-MANAGED JOIN HAS BEEN DISABLED


What the ???

Both WF servers are pointing to the same database. I verified the acx files on both servers and the number of keys for all the tables involved in the joins are the same. The programs are identical.

I will now try to determine if there's something wrong with the following join:

JOIN
BASEL_BSL_CL_WROFF_F.ACCOUNT_NUMBER IN BASEL_BSL_CL_WROFF_F TO ALL
BASEL_BSL_CL_WROFF_D.ACCOUNT_NUMBER IN BASEL_BSL_CL_WROFF_D AS J1
END
JOIN
BASEL_BSL_CL_WROFF_D.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_D.ACCOUNT_NUMBER IN BASEL_BSL_CL_WROFF_F TO ALL
BASEL_BSL_CL_WROFF_ACT_TXN_A.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_ACT_TXN_A.ACCOUNT_NUMBER IN BASEL_BSL_CL_WROFF_ACT_TXN_A AS J2
END
JOIN
BASEL_BSL_CL_WROFF_ACT_TXN_A.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_ACT_TXN_A.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_ACT_TXN_A.TIME_DIM_KEY IN BASEL_BSL_CL_WROFF_F TO ALL
BASEL_BSL_CL_WROFF_ACT_TXN_F.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_ACT_TXN_F.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_ACT_TXN_F.TIME_DIM_KEY IN BASEL_BSL_CL_WROFF_ACT_TXN_F AS J3
END
JOIN
BASEL_BSL_CL_WROFF_F.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_F.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_F.TIME_DIM_KEY IN BASEL_BSL_CL_WROFF_F TO ALL
BASEL_BSL_CL_WROFF_ACT_TXN_F.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_ACT_TXN_F.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_ACT_TXN_F.TIME_DIM_KEY IN BASEL_BSL_CL_WROFF_ACT_TXN_F AS J4
END
JOIN
BASEL_BSL_CL_WROFF_ACT_TXN_F.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_ACT_TXN_F.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_ACT_TXN_F.LOAN_TYP_CD IN BASEL_BSL_CL_WROFF_F TO ALL
BASEL_BSL_CL_WROFF_D.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_D.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_D.LOAN_TYP_CD IN BASEL_BSL_CL_WROFF_D AS J5
END
JOIN
BASEL_BSL_CL_WROFF_ACT_TXN_F.TIME_DIM_KEY IN BASEL_BSL_CL_WROFF_F TO
BASEL_TIME_D.TIME_DIM_KEY IN BASEL_TIME_D AS J6
END
JOIN
BASEL_BSL_CL_WROFF_ACT_TXN_F.RESP_NODE_ID IN BASEL_BSL_CL_WROFF_F TO
BASEL_BSL_REPTG_RESP_F.RESP_NODE_ID IN BASEL_BSL_REPTG_RESP_F AS J7
END

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

There were two too many joins in that structure - I suppose v7.6.5 got confused by a confusing set of joins!

No good:

JOIN
BASEL_BSL_CL_WROFF_F.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_F.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_F.TIME_DIM_KEY IN BASEL_BSL_CL_WROFF_F TO ALL
BASEL_BSL_CL_WROFF_ACT_TXN_F.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_ACT_TXN_F.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_ACT_TXN_F.TIME_DIM_KEY IN BASEL_BSL_CL_WROFF_ACT_TXN_F AS J4
END
JOIN
BASEL_BSL_CL_WROFF_ACT_TXN_F.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_ACT_TXN_F.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_ACT_TXN_F.LOAN_TYP_CD IN BASEL_BSL_CL_WROFF_F TO ALL
BASEL_BSL_CL_WROFF_D.SP_ACT_KEY AND BASEL_BSL_CL_WROFF_D.ACCOUNT_NUMBER AND BASEL_BSL_CL_WROFF_D.LOAN_TYP_CD IN BASEL_BSL_CL_WROFF_D AS J5
END