Optimizing Function Calls

In this section:

The relational adapters can convert calls to certain FOCUS functions to calls to SQL functions.

The following FOCUS functions can be optimized:

Calls to the following FOCUS functions cannot be optimized:

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.


Top of page

x
Optimization of the HPART, DPART, HDIFF, HDATE, and DATEDIF Functions

In this section:

The relational adapters can translate certain calls to the HPART, DPART, HDIFF, HDATE, and DATEDIF functions to SQL date and time functions, allowing the optimization of some TABLE requests with DEFINE fields or WHERE phrases that call these functions.



x
Optimization of the DATEDIF and HDIFF Functions

The DATEDIF function returns the difference between two dates in units, and the HDIFF function returns the difference between two date-time values in units. Calls to these functions can be translated to SQL when the unit is the number of days between two values.



x
Syntax: How to Calculate the Number of Days Between Date or Date-Time Values

The syntax for the DATEDIF function with the day unit is

DATEDIF(from_date, to_date, 'D')

The syntax for the HDIFF function with the day unit is

HDIFF(to_date_time, from_date_time, 'DAY', outfield)

where:

from_date

Is the starting date or date-time value from which to calculate the difference.

to_date

Is the ending date or date-time value from which to calculate the difference.

outfield

Numeric

Is the name of the field that contains the result, or the format of the output value enclosed in single quotation marks.



x
Reference: Conversion of the DATEDIF and HDIFF Functions to SQL

For Oracle, calls to DATEDIF and HDIFF translate calls to the native DATE-TIME functions with Datepart Parameter DAY, Startdate, and Enddate.

For DB2, calls to DATEDIF and HDIFF translate as calls to the DAYS function:

SELECT DAYS(d2) - DAYS(d1) FROM tablename

For Teradata, calls to DATEDIF and HDIFF translate as a simple difference between two date values for the DATE datatype and as calls to the CAST function for the TIMESTAMP datatype.



Example: Optimizing the Difference Between DB2 Date or Timestamp Columns

The following Master File represents a DB2 table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:

FILENAME=DATETIME, SUFFIX=DB2     , $
  SEGMENT=DATETIME, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $
    FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $

The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 DATE2 TIMEST1 TIMEST2 
WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND
HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1
END

The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL DAYS function in the SQL WHERE predicate:

SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2"
  FROM USER1."DATETIME" T1  
 WHERE ((DAYS(T1."TIMEST1") - DAYS(T1."TIMEST1")) = 1) AND              
       ((DAYS(T1."DATE1") - DAYS(T1."DATE2")) < 5)                      
       FOR FETCH ONLY;


Example: Optimizing the Difference Between Oracle Date or Timestamp Columns

The following Master File represents an Oracle table named DATETIME with two DATE fields named DATE1 and DATE2 and two TIMESTAMP fields named TIMEST1 and TIMEST2:

FILENAME=DATETIME, SUFFIX=SQLORA  , $
  SEGMENT=DATETIME, SEGTYPE=S0, $
    FIELDNAME=DATE1, ALIAS=DATE1, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=DATE2, ALIAS=DATE2, USAGE=YYMD, ACTUAL=DATE,
      MISSING=ON, $
    FIELDNAME=TIMEST1, ALIAS=TIMEST1, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $
    FIELDNAME=TIMEST2, ALIAS=TIMEST2, USAGE=HYYMDm, ACTUAL=HYYMDm,
      MISSING=ON, $

The following request calculates the number of days difference between DATE1 and DATE2 using the DATEDIF function and the number of days difference between TIMEST1 and TIMEST2 using the HDIFF function:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 DATE2 TIMEST1 TIMEST2 
WHERE DATEDIF(DATE2, DATE1, 'D') LT 5 AND
HDIFF(TIMEST1, TIMEST1, 'DAY', 'I11') EQ 1
END

The SQL generated for this request replaces the calls to DATEDIF and HDIFF with calls to the SQL EXTRACT and TRUNC functions in the SQL WHERE predicate:

SELECT T1."DATE1", T1."DATE2", T1."TIMEST1", T1."TIMEST2"
   FROM OWNER1.DATETIME T1
   WHERE (EXTRACT(DAY FROM (TRUNC(T1."TIMEST1") - TRUNC(T1."TIMEST1")) 
   DAY(9) TO SECOND) = 1)
   AND (EXTRACT(DAY FROM (TRUNC(T1."DATE1") - TRUNC(T1."DATE2"))
   DAY(9) TO SECOND) < 5);


x
Optimization of the HPART and DPART Functions

The HPART function extracts a specified component from a date-time value and returns it in numeric format. The DPART function extracts a specified component from a date value and returns it in numeric format. Calls to HPART and DPART are optimized when the second parameter is a case insensitive YEAR, MONTH, QUARTER, DAY-OF-MONTH, or DAY. Calls to HPART are also optimized when the second argument is a case-insensitive HOUR, MINUTE, SECOND, or MICROSECOND.



x
Syntax: How to Extract a Component From a Date-Time Value
HPART(dtvalue, 'component', outfield)

where:

dtvalue

Date-time

Is a date-time value, the name of a date-time field that contains the value, or an expression that returns the value.

component

Alphanumeric

Is the name of the component to be retrieved enclosed in single quotation marks.

outfield

Integer

Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.



x
Syntax: How to Extract a Date Component and Return a Date Component in Integer Format
DPART(datevalue, 'component', outfield)

where:

datevalue

Date

Is a full component date.

component

Alphanumeric

Is the name of the component to be retrieved enclosed in single quotation marks. Valid values are:

For year: YEAR, YY

For month: MONTH, MM

For day: DAY, For day of month: DAY-OF-MONTH.

For quarter: QUARTER, QQ

outfield

Integer

Is the field that contains the result, or the integer format of the output value enclosed in single quotation marks.



x
Reference: Conversion of the HPART Function to SQL

For DB2, calls to HPART translate as calls to the YEAR, MONTH, or DAY function.

For Oracle and Teradata (Both DATE and TIMESTAMP data types), calls to HPART and DPART translate as calls to the EXTRACT function.



Example: Extracting a Component From a DB2 Timestamp Column

The following request uses the HPART function to retrieve the year component from the DB2 TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT TIMEST1
WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008
END

The generated SQL replaces the call to the HPART function with a call to the SQL YEAR function in the SQL WHERE predicate:

SELECT T1."TIMEST1" FROM USER1."DATETIME" T1                 
   WHERE (YEAR(T1."TIMEST1") = 2008)                          
    FOR FETCH ONLY;


Example: Extracting a Component From an Oracle Timestamp Column

The following request uses the HPART function to retrieve the year component from the Oracle TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT TIMEST1
WHERE HPART(TIMEST1, 'YEAR', 'I4') EQ 2008
END

The generated SQL replaces the call to the HPART function with a call to the SQL EXTRACT function in the SQL WHERE predicate:

SELECT T1."TIMEST1" FROM OWNER1.DATETIME T1    
    WHERE (EXTRACT(YEAR FROM T1."TIMEST1") = 2008);


x
Optimization of the HDATE Function

The HDATE function converts the date portion of a date-time value to the date format YYMD.



x
Syntax: How to Extract the Date Portion of a Date-Time Value
HDATE(dtvalue, {'YYMD'|outfield})

where:

dtvalue

Date-time

Is the date-time value to be converted, the name of a date-time field that contains the value, or an expression that returns the value.

YYMD

Date

Is the output format. The value must be YYMD.

outfield

YYMD

Is the field that contains the result.



x
Reference: Conversion of the HDATE Function to SQL

For DB2 and Teradata, calls to HDATE translate as calls to the CAST function.

For Oracle, calls to HDATE translate as calls to the TRUNC function.



Example: Extracting the Date Portion of a DB2 Timestamp Column

The following request uses the HDATE function to extract the date portion of the TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 TIMEST1 
WHERE HDATE(TIMEST1, 'YYMD') GT DATE1
END

In the generated SQL, the call to HDATE is replaced by a call to the SQL CAST function in the SQL WHERE predicate:

SELECT T1."DATE1", T1."TIMEST1" FROM USER1."DATETIME" T1     
   WHERE (CAST(T1."TIMEST1" AS DATE) > T1."DATE1")                        
   FOR FETCH ONLY;


Example: Extracting the Date Portion of an Oracle Timestamp Column

The following request uses the HDATE function to extract the date portion of the TIMEST1 column:

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
TABLE FILE DATETIME
PRINT DATE1 TIMEST1 
WHERE HDATE(TIMEST1, 'YYMD') GT DATE1
END

In the generated SQL, the call to HDATE is replaced by a call to the SQL TRUNC function in the SQL WHERE predicate:

SELECT T1."DATE1", T1."TIMEST1" FROM OWNER1.DATETIME T1     
    WHERE (TRUNC(T1."TIMEST1") > T1."DATE1");

Top of page

x
Passing the SUBSTR Character Function to SQL

The SUBSTR character function can be passed to SQL for optimized processing that takes advantage of RDBMS substring functions, thus improving performance and response time.


Top of page

x
Passing Function Calls Directly to a Relational Engine Using SQL.Function Syntax

Reference:

The SQL adapters can pass virtual fields that call certain SQL scalar functions to the relational engine for processing. This enables you to use SQL functions in a request even when they have no equivalent in the WebFOCUS language. The function must be row-based and have a parameter list that consists of a comma-delimited list of column names or constants. In order to reference the function in an expression, prefix the function name with SQL.

If the virtual field is in the Master File, both TABLE requests and those SQL requests that qualify for Automatic Passthru (APT) can access the field. If the virtual field is created by a DEFINE FILE command, TABLE requests can access the field. The function name and parameters are passed without translation to the relational engine. Therefore, the expression that creates the DEFINE field must be optimized, or the request will fail.



x
Reference: Usage Notes for Direct SQL Function Calls


Example: Calling the SQL CONCAT Function in a Request

This example uses the WebFOCUS Retail demo sample. You can create this sample data source for a relational adapter by right-clicking the application in which you want to place this sample, and selecting New and then Samples from the context menu. Then, select WebFOCUS - Retail Demo from the Sample procedures and data for drop-down list and click Create.

The following request against the WebFOCUS Retail demo data source uses the SQL CONCAT function to concatenate the product category with the product subcategory.

SET TRACEUSER = ON
SET TRACEOFF = ALL
SET TRACEON = STMTRACE//CLIENT
SET TRACESTAMP=OFF
SET XRETRIEVAL = OFF
 
DEFINE FILE WF_RETAIL
CAT_SUBCAT/A50 = SQL.CONCAT(PRODUCT_CATEGORY, PRODUCT_SUBCATEG);
END
 
TABLE FILE WF_RETAIL
PRINT CAT_SUBCAT
BY PRODUCT_CATEGORY NOPRINT
END

The trace output shows that the SQL function call was passed to the RDBMS.

SELECT      
CONCAT(T2."PRODUCT_CATEGORY",T2."PRODUCT_SUBCATEG"),  
T2."PRODUCT_CATEGORY",      
T2."PRODUCT_SUBCATEG"  
FROM    
wfr_product T2  
ORDER BY    
T2."PRODUCT_CATEGORY"   
FOR FETCH ONLY;

Information Builders