IBM DB2 for i: Code example

Here's an example of a User Defined Function (UDF) written with 
SQL (aka, SQL UDF) to compute the area of a circle: 

   CREATE FUNCTION  circle_area (radius FLOAT)
  RETURNS FLOAT LANGUAGE SQL
  BEGIN
       DECLARE pi FLOAT DEFAULT 3.14;
       DECLARE area FLOAT;
 
      SET area =  pi * radius * radius;
      RETURN area;
  END

Here's another UDF that also computes the area of a circle, but 
it's implemented as an External UDF with an "external" C program:


CREATE FUNCTION cirArea (FLOAT)
  RETURNS FLOAT
  EXTERNAL NAME 'pgmlib/math(cirArea)' 
  LANGUAGE C  
  NO SQL
  NO EXTERNAL ACTION 
  PARAMETER STYLE SQL 
  DETERMINISTIC
  ALLOW PARALLEL;

void cirArea(double *radius, double *outarea, 
   short *inind, short *outind, char *sqlstate,
   char *funcname, char *specname, char *msgtext)
{
    double pi=3.14;
    
    *outarea = pi * (*radius) * (*radius);

/* Steps for creating a debuggable version of this           */
/* service program:                                          */
/*  1) CRTCMOD MODULE(pgmlib/cirArea) DBGVIEW(*SOURCE)       */
/*  2) CRTSRVPGM SRVPGM(pgmlib/math) MODULE(pgmlib/cirarea)  */
/*          EXPORT(*ALL) ACTGRP(*CALLER)                     */

}



Both the SQL UDF & External UDF are invoked the same.  Here 
are several invocation examples for the circle_area UDF:

SELECT color, circle_area(radius) FROM shapetable;

SELECT color, radius FROM shapetable WHERE 
                                     circle_area(radius) >= 50