Optimizing Record Selection and Projection

In this section:

Regardless of the OPTIMIZATION setting, the adapter may pass record selection and projection to the RDBMS. It is always more efficient to do so


Top of page

x
Record Selection

In this section:

The adapter can translate all forms of FOCUS record selection to predicates of an SQL WHERE clause, except those that contain:

The adapter optimizes screening conditions based on DEFINE fields that derive their values from a single segment of the join structure. The adapter optimizes these screening conditions as long as you do not set OPTIMIZATION OFF, even if it has disabled join optimization in your request.

When using LIKE in a WHERE clause, make sure any constant in the LIKE predicate either:

Escape characters in the LIKE predicate are optimized. See your FOCUS documentation for a discussion of LIKE.

If you fail to specify a wildcard pattern in the LIKE predicate, the WHERE clause passes an equality predicate to the RDBMS instead of the LIKE predicate. For example

WHERE EID LIKE 'A'

will generate

WHERE (T1.EID = 'A')

not:

WHERE (T1.EID LIKE 'A')

In addition, because of unpredictable comparisons between VARCHAR data types, the WHERE clause is not passed to the RDBMS if both of the following conditions are true:

In this case, FOCUS performs the screening condition on all rows returned from the RDBMS.



x
Optimizing Selection of Relational Variable Length Character Data Types

TABLE IF criteria can reference RDBMS variable character data types such as VARCHAR, LONG VARCHAR, and CLOB (described in the Master File with USAGE=TX and ACTUAL=TX).

Certain types of IF criteria that reference variable length character data types are included in the generated SQL, causing the selection operations to be performed by the RDBMS and improving performance.

The IF test to be optimized must be a CONTAINS or OMITS test against a field described with USAGE=TX and ACTUAL=TX in the Master File. The RDBMS column must be a character variable length data type.

CONTAINS translates to LIKE in the generated SQL, and OMITS translates to NOT LIKE. The generated SQL places wildcard characters around the literal string specified in the CONTAINS or OMITS test.



x
Reference: Usage Notes for Optimization of Selection of Variable Length Data Types

The following options are not supported with text fields:

  • CRTFORM
  • TYPE
  • FSCAN
  • MODIFY


Example: Optimizing a Selection Test Against a Variable Length Character Column

Consider the following variation of the DB2 Master File named EMPINFO. A CLOB column named JOBDESC has been added that contains a job description:

FILENAME=EMPINFO ,SUFFIX=DB2,$
 
SEGNAME=EMPINFO  ,SEGTYPE=S0,$
 FIELD=EMP_ID        ,ALIAS=EID         ,USAGE=A9     ,ACTUAL=A9     ,$
 FIELD=LAST_NAME     ,ALIAS=LN          ,USAGE=A15    ,ACTUAL=A15    ,$
 FIELD=FIRST_NAME    ,ALIAS=FN          ,USAGE=A10    ,ACTUAL=A10    ,$
 FIELD=HIRE_DATE     ,ALIAS=HDT         ,USAGE=YMD    ,ACTUAL=DATE   ,$
 FIELD=DEPARTMENT    ,ALIAS=DPT         ,USAGE=A10    ,ACTUAL=A10,
   MISSING=ON,$
 FIELD=CURRENT_SALARY,ALIAS=CSAL        ,USAGE=P9.2   ,ACTUAL=P4     ,$
 FIELD=CURR_JOBCODE  ,ALIAS=CJC         ,USAGE=A3     ,ACTUAL=A3     ,$
 FIELD=JOBDESC       ,ALIAS=JDSC        ,USAGE=TX50   ,ACTUAL=TX     ,$ 
 FIELD=ED_HRS        ,ALIAS=OJT         ,USAGE=F6.2   ,ACTUAL=F4,
   MISSING=ON,$
 FIELD=BONUS_PLAN    ,ALIAS=BONUS_PLAN  ,USAGE=I4     ,ACTUAL=I4     ,$

The following request specifies a CONTAINS test against the JOBDESC field:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
 
TABLE FILE EMPINFO
PRINT EMP_ID LAST_NAME FIRST_NAME DEPARTMENT CURR_JOBCODE JOBDESC
IF JOBDESC CONTAINS 'PR'
END

The CONTAINS operator is translated to a LIKE operator in the generated SQL:

    SELECT T1."EID",T1."LN",T1."FN",T1."DPT",T1."CJC",T1."JDSC"
FROM USER1."EMPINFO" T1 WHERE (T1."JDSC" LIKE '%PR%') FOR
FETCH ONLY;

Top of page

x
Projection

In relational terminology, projection is the act of retrieving any subset of the available columns from a table. The adapter implements projection by retrieving only those columns referenced in a TABLE request. Projection reduces the volume of data returned from the RDBMS, which, in turn, improves application response time.


Information Builders