IBM DB2 for i: Code example

UDF written with SQL

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

Contact IBM

Browse Power Systems

Next generation applications for big data and analytics and cognitive computing are providing unprecedented insights into opportunities, threats and efficiencies. IBM Power Systems is at the forefront of delivering solutions to gain faster insights from analyzing both structured information and unstructured big data. With the secure, flexible and open platform of IBM Power Systems plus solutions and software, organizations can outpace their competitors by delivering faster services, providing differentiated offerings and turning operational cost into investment opportunity.

IBM Power Systems hardware is at the heart of enabling major business and industry transformations fueled by the explosive growth of big data and analytics. Power Systems help organizations drive faster business insights with industry leading performance, scalability and virtualization flexibility.

IBM Power Systems deliver flexibility and choice of operating systems to enable your business to support the next generation applications for big data and analytics and cognitive computing that are transforming how organizations work today. Whether running 1, 2, or all 3 - coupled with PowerVM, they maximize the benefit of Power Systems in your business.

IBM's integrated approach to developing Systems and Systems Software stacks together delivers a platform that supports next generation applications for big data and analytics and cognitive computing on an open platform for choice while providing maximum utilization, availability, and flexibility to help you deliver new advantages to your business.

IBM and IBM Business Partner solutions exploit key benefits in IBM Power Systems’ unique design combined with systems software to provide support for next generation applications for big data and analytics and cognitive computing to providing unprecedented insights into opportunities, threats and efficiencies to your organization.


Over the last five years thousands of clients have migrated to IBM Power Systems. Learn how Power Systems has helped them support next generation applications for big data and analytics and cognitive computing on an open platform for choice while improving business performance, reducing risk, and establishing a platform for growth.

Additional information