In this section: |
Adapter optimization allows the RDBMS to perform the work for which it is best suited, reducing the volume of RDBMS-to-server communication and improving response time. It also enables the RDBMS to exploit its own internal optimization techniques.
How to: |
The adapter can optimize DML requests by creating SQL statements that take advantage of RDBMS join, sort, and aggregation capabilities.
Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.
SQL SQLRED SET {OPTIMIZATION|SQLJOIN} setting
where:
Is the target RDBMS. You can omit this value if you previously issued the SET SQLENGINE command.
Is a synonym for OPTIMIZATION.
Is the optimization setting. Valid values are as follows:
ON instructs the adapter to create SQL statements that take advantage of RDBMS join, sort, and aggregation capabilities. Note that the multiplicative effect may disable optimization in some cases. However, misjoined unique segments and multiplied lines in PRINT-based and LIST-based report requests do not disable optimization. This is the default.
OFF instructs the adapter to create SQL statements for simple data retrieval from each table. The server handles all aggregation, sorting, and joining in your address space or virtual machine to produce the report.
Both OPTIMIZATION settings produce the same report.
Note: The OFF setting is not recommended since Red Brick supports only one active SQL per connection and will return:
** ERROR ** (10167) Attempt to initiate a new SQL...with results pending
With optimization enabled, the data adapter generates one SELECT statement that incorporates the join, sort, and aggregation operations. The RDBMS manages and processes the request. The server only formats the report.
SQL SQLRED 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
In a trace operation, you will see the following output:
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;
How to: Reference: |
The adapter can optimize DML requests to the server that include virtual (DEFINE) fields created using IF-THEN-ELSE syntax. In certain cases, such DEFINE fields can be passed to the RDBMS as expressions, enhancing performance and minimizing the size of the answer set returned to the server.
Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.
When you issue the SET OPTIFTHENELSE command, the adapter attempts to deliver the construct of a DML IF-THEN-ELSE DEFINE field to the RDBMS as an expression. The DEFINE field must be an object of a selection test or an aggregation request. The DEFINE definition may be specified in the DML request or in the Master File.
SQL SQLRED SET OPTIFTHENELSE {ON|OFF}
where:
Indicates the target RDBMS. You can omit this value if you previously issued the SET SQLENGINE command.
Enables IF-THEN-ELSE optimization. This is the default.
Disables IF-THEN-ELSE optimization.
There is no guarantee that the SQL that is generated will improve performance for all requests. If you find that this feature does not improve performance, set OPTIFTHENELSE OFF to disable the feature.
IF-THEN-ELSE optimization applies to SELECT statements created as a result of requests and is subject to SQL limitations on optimization of DEFINE expressions.
Consider the following request:
SQL SQLRED SET OPTIFTHENELSE ON DEFINE FILE EMPINFO DEF1 = IF (LAST_NAME EQ ' ') AND (FIRST_NAME EQ ' ') AND (DEPARTMENT EQ 'MIS') THEN 1 ELSE 0; END TABLE FILE EMPINFO PRINT DEPARTMENT LAST_NAME FIRST_NAME WHERE DEF1 EQ 1 END
The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the WHERE DEF1 EQ 1 test:
SELECT T1.'LN',T1.'FN',T1.'DPT' FROM USER1.'EMPINFO' T1 WHERE (((((T1.'LN' = ' ') AND (T1.'FN' = ' ')) AND (T1.'DPT' = 'MIS'))));
Consider the following request:
SQL SQLRED SET OPTIFTHENELSE ON DEFINE FILE EMPINFO DEF2 = IF LAST_NAME EQ 'SMITH' THEN 1 ELSE IF LAST_NAME EQ 'JONES' THEN 2 ELSE IF LAST_NAME EQ 'CARTER' THEN 3 ELSE 0; END TABLE FILE EMPINFO SUM MAX.LAST_NAME IF DEF2 EQ 1 END
The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the WHERE DEF2 EQ 1 test:
SELECT MAX(T1.'LN') FROM USER1.'EMPINFO' T1 WHERE (((T1.'LN' = 'SMITH'))); TABLE FILE EMPINFO SUM MAX.LAST_NAME IF DEF2 EQ 2 END
The adapter generates an SQL request that incorporates the IF-THEN-ELSE condition corresponding to the WHERE DEF2 EQ 2 test:
SELECT MAX(T1.'LN') FROM USER1.'EMPINFO' T1 WHERE (((NOT (T1.'LN' = 'SMITH')) AND (T1.'LN' = 'JONES')));
SQL SQLRED SET OPTIFTHENELSE ON DEFINE FILE EMPINFO DEF3 = IF FIRST_NAME EQ 'RITA' THEN 1 ELSE 0; END TABLE FILE EMPINFO PRINT FIRST_NAME IF DEF3 EQ 2 END
Because DEF3 EQ 2 will never be true, the adapter passes the WHERE test 1=0 (which is always false) to the RDBMS, returning zero records from the RDBMS:
SELECT T1.'FN' FROM USER1.'EMPINFO' T1 WHERE (1 = 0);
Since the FOCUS reporting language is more extensive than native SQL, the data adapter cannot pass certain DEFINE expressions to the RDBMS for processing. The data adapter does not offload DEFINE-based aggregation and record selection if the DEFINE includes:
X=X+1;
Note: Do not confuse the FOCUS user-written subroutines MAX and MIN with the MAX. and MIN. prefix operators. DEFINE fields cannot include prefix operators.
Note: FML report requests are extended TABLE requests. The Financial Modeling Language provides special functions for detailed reporting. Consult your FOCUS documentation for more information.
In addition, IF-THEN-ELSE optimization does not support the following features:
A virtual field defined as a constant is passed directly to an SQL database engine (RDBMS) for optimized processing that takes advantage of RDBMS join, sort, and aggregation capabilities, thus reducing the volume of RDBMS-to-server communication and improving response time. (In prior releases, constants were not passed to the database engine, causing optimization to be turned off.)
Constants in the following formats are passed to the RDBMS: NUMERIC, CHAR, VARCHAR, CHAR/INTEGER combination, and DATE. (Note that a few formats are not passed to the RDBMS; these are CHAR and VARCHAR combination, TIME, and DATETIME.)
When valid constants are passed to an RDBMS engine, a report is calculated based on the defined value(s) and only the calculated subset of records is returned. The extent of data manipulaton at the adapter level is limited, thereby improving performance.
DEFINE FILE SMIX87 INTEGERCONST/I4 = 5 REALCONST/D20.2 = -97995.38 NUMERICCONST/P13.4 = -92999.3647
TABLE FILE SMIX87 SUM INTEGERCONST REALCONST NUMERICCONST MAX.QUOT.FA01INTEGER BY QUOT.FA02INTEGER END
The following SQL is generated:
SELECT T2."FA02INTEGER", SUM(5), SUM(-97995.38), SUM(-92999.3647), MAX(T2."FA01INTEGER") FROM TMIX83A T2 GROUP BY T2."FA02INTEGER" ORDER BY T2."FA02INTEGER";
DEFINE FILE SMIX87 CHARCONST/A10 = '2N'
TABLE FILE SMIX87 SUM CHARCONST MIN.FA02CHAR_15 BY QUOT.FA02INTEGER END
The following SQL is generated:
SELECT T2."FA02INTEGER", MAX('2N'), MIN(T3."FA02CHAR_15") FROM TMIX83A T2,TMIX86A T3 WHERE (T3."FA01INTEGER" = T2."FA01INTEGER") GROUP BY T2."FA02INTEGER" ORDER BY T2."FA02INTEGER";
DEFINE FILE SMIX87 CHARCONST/A10 = '2N' INTEGERCONST/I4 = 5
TABLE FILE SMIX87 SUM CHARCONST INTEGERCONST BY QUOT.FA02INTEGER END
The following SQL is generated:
SELECT T2."FA02INTEGER", MAX('2N'), SUM(5) FROM TMIX83A T2 GROUP BY T2."FA02INTEGER" ORDER BY T2."FA02INTEGER";
The Redbrick adapter can optimize FOCUS OUTER JOINs to SQL. However, the OUTER JOIN optimization depends on native Redbrick capabilities. Non-equality conditions in ON clauses are supported for INNER JOINS only. For OUTER JOINs, non-equality conditions will return:
RBS1609,..., RBS1612 An outer join <problem criteria>
or
10167 Attempt to initiate a new SQL...with results pending
For more information please refer to IBM Redbrick documentation.
iWay Software |