x

To invoke the optimization process, enter the following adapter command at the FOCUS command level

{ENGINE|SQL} [sqlengine] SET {OPTIMIZATION|SQLJOIN} setting 

where:

sqlengine

Is the target RDBMS. Valid values are DB2, SQLDBC, SQLIDMS, or SQLORA. Omit if you previously issued the SET SQLENGINE command.

SQLJOIN

Is a synonym for OPTIMIZATION.

setting

Is the optimization setting. Valid values are as follows:

OFF instructs the adapter to create SQL statements for simple data retrieval from each table. FOCUS handles all aggregation, sorting, and joining in your address space to produce the report.

ON instructs the adapter to create SQL statements that take advantage of RDBMS join, sort, and aggregation capabilities. It is compatible with previous releases in regard to the multiplicative effect. Misjoined unique segments and multiplied lines in PRINT and LIST based report requests do not disable optimization (see RDBMS and FOCUS Join Management). Other cases of the multiplicative effect invoke the adapter-managed native join logic described in Optimizing Joins. ON is the default value.

FOCUS passes join logic to the RDBMS only when the results will be the same as from a FOCUS-managed request. Misjoined unique segments, the multiplicative effect, and multiplied lines in PRINT and LIST based report requests (see RDBMS and FOCUS Join Management) invoke the adapter-managed native join logic described in Optimizing Joins.

SQL passes join logic to the RDBMS in all possible cases. The multiplicative effect does not disable optimization, even in cases involving aggregation (SUM, COUNT). Does not pass join logic to the RDBMS for tables residing on multiple subsystems and for tables residing on multiple DBMS platforms.

NOAGGR disables optimization of calculations (DEFINE fields) without disabling optimization of join and sort operations.

AGGR enables optimization of calculations (DEFINE fields). This is the default value for optimization of calculations.

You can invoke the adapter trace facility to evaluate the SQL statements generated by the adapter. The SQLAGGR trace component verifies whether the adapter passed aggregation and join operations to the RDBMS. The STMTRACE component displays the SQL SELECT statements that the adapter generates from report requests. A single SQL SELECT statement indicates RDBMS-managed join processing. Two or more indicate FOCUS-managed join processing. For information about adapter trace facilities, see Tracing Adapter Processing.

When optimizing a TABLE request, the adapter tries to ensure, within reasonable limits, that the results of execution of the optimized query will be the same as the results obtained when reading raw unfiltered data from the same data hierarchy.

Top of page

Example: SQL Requests Passed to the RDBMS With Optimization OFF

This example demonstrates SQL statements generated without optimization. The report request joins tables EMPINFO and FUNDTRAN with trace components SQLAGGR and STMTRACE allocated.

When optimization is disabled, the adapter generates two SELECT statements. The first SELECT retrieves any rows from the EMPINFO table that have the value MIS in the DEPARTMENT column. For each EMPINFO row, the second SELECT retrieves rows from the cross-referenced FUNDTRAN table, resolving the parameter marker (?, :000n, or :H, depending on the RDBMS) with the value of the host field (EMP_ID). Both SELECT statements retrieve answer sets, but FOCUS performs the join, sort, and aggregation operations:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT

SQL DB2 SET OPTIMIZATION OFF
 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

(FOC2510) FOCUS-MANAGED JOIN SELECTED FOR FOLLOWING REASON(S):
(FOC2511) DISABLED BY USER
(FOC2590) AGGREGATION NOT DONE FOR THE FOLLOWING REASON:
(FOC2592) RDBMS-MANAGED JOIN HAS BEEN DISABLED
  SELECT T1.EID,T1.LN,T1.DPT,T1.CSAL,T1.OJT FROM
 "USER1"."EMPINFO" T1 WHERE (T1.DPT = 'MIS') FOR FETCH ONLY;
  SELECT T2.EID FROM "USER1"."FUNDTRAN" T2 WHERE (T2.EID = ?)
 FOR FETCH ONLY;

Top of page

Example: SQL Requests Passed to the RDBMS With Optimization ON

This example shows the SQL generated with optimization for the report request in SQL Requests Passed to the RDBMS With Optimization OFF.

With optimization enabled, the adapter generates one SELECT statement that incorporates the join, sort, and aggregation operations. The RDBMS manages and processes the request. FOCUS only formats the report.

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = SQLAGGR//CLIENT
SET TRACEON = STMTRACE//CLIENT

SQL DB2 SET OPTIMIZATION 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:

AGGREGATION DONE ...
    SELECT T2.EID,T1.LN, AVG(T1.CSAL), SUM(T1.OJT) FROM
   "USER1"."EMPINFO" T1,"USER1"."FUNDTRAN" T2 WHERE (T2.EID =
   T1.EID) AND (T1.DPT = 'MIS') GROUP BY T2.EID,T1.LN ORDER BY
   T2.EID,T1.LN FOR FETCH ONLY;

Both OPTIMIZATION settings produce the same report.

x

There are minor differences in the specific SQL syntax generated for each RDBMS. However, the adapter messages are the same and the generated SQL statements are similar enough that for the remainder of this manual most examples will illustrate SQL syntax generated by one adapter.