Optimizing DEFINE Fields

The data adapter can translate certain DEFINE expressions to SQL as part of aggregation or record selection operations only. DEFINE expressions that are not translated for the RDBMS are listed in SQL Limitations on Optimization of DEFINE Expressions.

In this section: Optimizing DEFINE Fields Referenced in FOCUS BY Clauses (DB2, Teradata, Oracle) IF-THEN-ELSE Optimization Valued Expressions SQL Limitations on Optimization of DEFINE Expressions

• For RDBMS-managed record selection, the DEFINE expression must be an arithmetic valued expression, a character string valued expression, or a logical expression.
• For RDBMS-managed aggregation, the DEFINE expression must be an arithmetic or character string valued expression.

Optimizing DEFINE Fields Referenced in FOCUS BY Clauses (DB2, Teradata, Oracle)

The data adapter can optimize aggregation requests in which a DEFINE field is the object of a FOCUS BY clause. The display commands in these requests must be aggregation commands (SUM, COUNT, or WRITE) or prefix operators such as MIN., MAX., and AVE.

Example: Passing Aggregation on DEFINE Fields to the RDBMS for Processing

• FOCUS and the RDBMS use slightly different sorting/aggregation algorithms. Therefore, when the data adapter passes expressions as objects of SQL GROUP BY or ORDER BY phrases to the RDBMS, the sequence of report rows within a given sort key value may vary slightly from the sequence that would be produced by FOCUS. While both report results are equally correct, if such differences are unacceptable you can set data adapter optimization OFF. FOCUS will then handle all aggregation, sorts and join operations required to produce the report.
• BY TOTAL phrases are not optimized.

Example: Passing Aggregation on DEFINE Fields to the RDBMS for Processing

This example shows the SQL passed following aggregation on a DEFINE field that is the object of a BY clause:

DEFINE FILE DEDUCT
TAX = 0.085 * DED_AMT
END
 
TABLE FILE DEDUCT
SUM DED_AMT TAX
BY TAX NOPRINT
END

• The following SQL is passed to the DB2 DBMS:

SELECT SK001, SUM(VB001), SUM(VB002) FROM (SELECT (.085 *
T1.DA) AS SK001,T1.DA AS VB001,(.085 * T1.DA) AS VB002 FROM
"USER1"."DEDUCT" T1) X  GROUP BY SK001 ORDER BY SK001 FOR
FETCH ONLY;

• The following SQL is passed to the Teradata DBMS:

SELECT (.085 * T1.DA), SUM(T1.DA)(FLOAT), SUM((.085 *
T1.DA))(FLOAT) FROM USER1.DEDUCT T1 GROUP BY (.085 * T1.DA)
ORDER BY (.085 * T1.DA);

• The following SQL is passed to the Oracle DBMS:

SELECT (.085 * T1."DA"), SUM(T1."DA"), SUM((.085 * T1."DA"))
FROM USER1.DEDUCT T1 GROUP BY (.085 * T1."DA") ORDER BY (.085 *
T1."DA");

IF-THEN-ELSE Optimization

The data adapter can optimize TABLE requests that include 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 FOCUS.

How to: Enable IF-THEN-ELSE Optimization Example: Using IF-THEN-ELSE Optimization Without Aggregation Using IF-THEN-ELSE Optimization With Aggregation Using IF-THEN-ELSE Optimization With a Condition That Is Always False

Syntax: How to Enable IF-THEN-ELSE Optimization

When you issue the data adapter SET OPTIFTHENELSE command, the data adapter attempts to deliver the construct of a FOCUS 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 TABLE request or in the Master File.

SQL sqlengine SET OPTIFTHENELSE {ON|OFF}

where:

sqlengine

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

ON

Enables IF-THEN-ELSE optimization.

OFF

Disables IF-THEN-ELSE optimization. OFF is the default.

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 FOCUS TABLE requests and is subject to the limitations described in SQL Limitations on Optimization of DEFINE Expressions.

Example: Using IF-THEN-ELSE Optimization Without Aggregation

Consider the following request:

SQL DB2 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 data 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')))) FOR FETCH ONLY;

Example: Using IF-THEN-ELSE Optimization With Aggregation

SQL DB2 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 data 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'))) FOR FETCH ONLY;

TABLE FILE EMPINFO
SUM MAX.LAST_NAME IF DEF2 EQ 2
END

The data 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'))) FOR FETCH ONLY;

Example: Using IF-THEN-ELSE Optimization With a Condition That Is Always False

SQL DB2 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 data 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) FOR
FETCH ONLY;

Valued Expressions

There are two types of valued expressions: arithmetic and character string.

How to: Logical Expressions Reference: Arithmetic Expressions Character String Expressions

Reference: Arithmetic Expressions

Arithmetic expressions return a single number. DEFINE fields are classified as arithmetic if the expression on the right includes one or more of the following elements:

• Real-field operands of numeric data type (I, P, D, F).
• Numeric constants.
• Arithmetic operators (**, *, /, + , -).
• The subtraction of one DATE field from another.
• DEFINE-field operands satisfying any of the preceding items.

For example, the arithmetic expression in the following DEFINE uses a numeric real-field operand (CURR_SAL), a previously-specified DEFINE field (OTIME_SAL), and two numeric constants (1.1 and 100):

NEW_SAL/D12.2= ((CURR_SAL + OTIME_SAL) * 1.1) - 100;

Reference: Character String Expressions

Character string expressions return a character string. DEFINE fields are classified as character strings if the expression on the right includes one or more of the following elements:

• Real-field operands of alphanumeric (A) data type. (TX field formats are not supported in DEFINE expressions.)
• String constants.
• String concatenation operators ('|').
• DEFINE-field operands satisfying any of the preceding items.

For example, the character string expression in the following DEFINE uses two alphanumeric field operands (LAST_NAME and FIRST_NAME), a string constant (','), and string concatenation operators (|):

FORMAL_NAME/A36= LAST_NAME | ',' | FIRST_NAME;

Syntax: Logical Expressions

Logical expressions return one of two values: True (1) or False (0). DEFINE fields are classified as logical if the expression on the right includes any of the following elements:

• Real-field operands of any FOCUS-supported data type (including DATE fields).
• Constants of a data type consistent with fields in the predicate.
• Relational operators (EQ, NE, GT, LT, GE, LE).
• Logical operators (AND, OR, NOT).
• Arithmetic or character string expression operands (described in Valued Expressions).
• DEFINE-field operands satisfying any combination of the preceding items.

For example, the logical expression in the following DEFINE is composed of two expressions connected by the logical operator OR; each part is itself a logical expression:

SALES_FLAG/I1= (DIV_CODE EQ 'SALES') OR (COMMISSION GT 0);

In the next example, the DEFINE field, QUOTA_CLUB, is the value of a logical expression composed of two other expressions connected by the logical operator AND. Note that the first expression is the previously-specified DEFINE field, SALES_FLAG:

QUOTA_CLUB/I1= (SALES_FLAG) AND (UNITS_SOLD GT 100);

SQL Limitations on Optimization of DEFINE Expressions

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:

• User-written subroutines.
• Self-referential expressions such as:

X=X+1;

• EDIT functions for numeric-to-alpha or alpha-to-numeric field conversions.
• DECODE functions for field value conversions.
• Relational operators INCLUDES and EXCLUDES.
• FOCUS subroutines ABS, INT, MAX, MIN, LOG, and SQRT.

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.

• Expressions involving fields with ACTUAL=DATE, except for the subtraction of one DATE field from another and all logical expressions on DATE fields.
• Date-time manipulation handled by the FOCUS date-time functions is not converted to SQL.
• Financial Modeling Language (FML) cell calculations. FML is also referred to as Extended Matrix Reporting (EMR).

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:

• Any type of DECODE expression.
• STATIC SQL.
• IF/WHERE DDNAME.
• Partial date selection.