How to: Reference: |
With the SET SQLJOIN OUTER command, you can control when the adapter optimizes outer joins without affecting the optimization of other operations. This parameter provides backward compatibility with prior releases of the adapter and enables you to fine-tune your applications.
When join optimization is in effect, the adapter generates one SQL SELECT statement that includes every table involved in the join. The RDBMS can then process the join. When join optimization is disabled, the adapter generates a separate SQL SELECT statement for each table, and FOCUS processes the join.
You can use the SQLJOIN OUTER setting to disable outer join optimization while leaving other optimization enhancements in effect.
SQL sqlengine SET SQLJOIN OUTER {ON|OFF}
where:
Indicates the target RDBMS. Valid values are DB2, SQLDBC, or SQLORA. Omit if you issued the SET SQLENGINE command.
Enables outer join optimization. ON is the default value for Teradata and Oracle.
Disables outer join optimization. OFF is the default value for DB2.
Note:
(FOC1420) OPTIMIZATION OF ALL=ON AS LEFT JOIN - : OFF
The following table describes how different combinations of OPTIMIZATION and SQLJOINÂ OUTER settings affect adapter behavior. It assumes that SET ALL = ON:
Settings |
Results | ||
---|---|---|---|
OPTIMIZATION |
SQLJOIN OUTER |
Outer Join Optimized? |
Other Optimization Features |
ON |
ON |
Yes |
Enabled |
ON |
OFF |
No |
Enabled |
OFF |
N/A |
No |
Disabled |
SQL |
ON |
Yes, in all possible cases |
Enabled |
SQL |
OFF |
No |
Enabled |
FOCUS |
ON |
Yes if results are equivalent to FOCUS-managed request |
Enabled |
FOCUS |
OFF |
No |
Enabled |
The following request specifies a left outer join between the EMPINFO and FUNDTRAN tables. The SQLJOIN OUTER setting specifies that the left outer join should be optimized, and the SQLAGGR and STMTRACE trace components are activated:
SET TRACEUSER = ON SET TRACEOFF = ALL SET TRACEON = SQLAGGR//CLIENT SET TRACEON = STMTRACE//CLIENT SET ALL = ON SQL DB2 SET OPTIMIZATION ON SQL DB2 SET SQLJOIN OUTER ON JOIN EMP_ID IN EMPINFO TO ALL WHO IN FUNDTRAN AS J1 TABLE FILE EMPINFO SUM AVE.CURRENT_SALARY ED_HRS BY WHO BY LAST_NAME IF DEPARTMENT EQ 'MIS' END
The following trace is generated. One SELECT statement is generated. The LEFT OUTER JOIN phrase in the FROM predicate specifies the left outer join:
AGGREGATION DONE ... SELECT T2."EID",T1."LN", AVG(T1."CSAL"), SUM(T1."OJT") FROM ( USER1."EMPINFO" T1 LEFT OUTER JOIN "USER1"."FUNDTRAN" T2 ON T2."EID" = T1."EID") WHERE (T1."DPT" = 'MIS') GROUP BY T2."EID",T1."LN" ORDER BY T2."EID",T1."LN" FOR FETCH ONLY;
Information Builders |