The DEFINE FUNCTION syntax can be sent directly to an SQL engine as long as all expressions used in the function can be optimized.

Note that in order for DEFINE FUNCTION syntax to be optimized, the types and lengths of the arguments used to call the DEFINE FUNCTION must exactly match the types and lengths of the DEFINE FUNCTION parameters.

For example, the DB2 data source EMPINFO has columns LAST_NAME (alias LN) and FIRST_NAME (alias FN). The following DEFINE FUNCTION takes two arguments, N1 and N2, and sets a flag, which it returns as EMPNAME. EMPNAME has the value 1 if N1 is Smith and N2 is Richard:

DEFINE FUNCTION EMPNAME (N1/A15, N2/A10)
DEF1/I1 = IF N1 EQ 'SMITH' THEN 1 ELSE IF N1 EQ 'JONES' THEN 2
             ELSE IF N1 EQ 'CARTER' THEN 3 ELSE 0;
DEF2/I1 = IF N2 EQ 'RICHARD' THEN 1 ELSE IF N2 EQ 'BARBARA' THEN 2
          ELSE 0;
EMPNAME/I1 = IF DEF1 EQ 1 AND DEF2 EQ 1 THEN 1 ELSE 0;
END

The following request uses the result of the DEFINE FUNCTION in an aggregation command. Note that the format of LAST_NAME exactly matches the format defined for N1, and the format of FIRST_NAME exactly matches the format defined for N2:

SET TRACEUSER = ON
SET TRACESTAMP = OFF
SET TRACEON = STMTRACE//CLIENT
ENGINE DB2 SET OPTIFTHENELSE ON
DEFINE FILE EMPINFO
DEF3/I1 = EMPNAME(LAST_NAME, FIRST_NAME);
END
TABLE FILE EMPINFO
SUM MAX.LAST_NAME IF DEF3  EQ 1
END

The trace output shows that the IF-THEN-ELSE expressions from the DEFINE FUNCTION are translated to an SQL expression in the WHERE predicate of the SELECT statement passed to the RDBMS:

 SELECT                                                        
 MAX(T1."LN")                                                  
 FROM                                                          
USER1."EMPINFO" T1                                            
 WHERE                                                         
((CASE WHEN (((CASE (T1."LN")  WHEN 'SMITH' THEN 1 WHEN 'JONES'
THEN 2 WHEN 'CARTER' THEN 3 ELSE 0 END) = 1) AND ((CASE        
(T1."FN")  WHEN 'RICHARD' THEN 1 WHEN 'BARBARA' THEN 2 ELSE 0  
END) = 1)) THEN 1 ELSE 0 END) = 1)                             
 FOR FETCH ONLY;