Example: Square of a number UDF

 

Suppose that you want a function that returns the square of a number.

The query statement is:

SELECT SQUARE(myint) FROM mytable

By using the code examples, you agree to the terms of the Code license and disclaimer information.

The following examples show how to define the UDF in several different ways.

 

Using an SQL function

The CREATE FUNCTION statement:

CREATE FUNCTION SQUARE( inval INT) RETURNS INT

LANGUAGE SQL SET OPTION DBGVIEW=*SOURCE BEGIN RETURN(inval*inval);

END

This creates an SQL function that you can debug.

 

Using an external function, parameter style SQL

The CREATE FUNCTION statement:

CREATE FUNCTION SQUARE(INT) RETURNS INT CAST FROM FLOAT

LANGUAGE C

EXTERNAL NAME 'MYLIB/MATH(SQUARE)'

DETERMINISTIC NO SQL NO EXTERNAL ACTION PARAMETER STYLE SQL ALLOW PARALLEL 

The code:

void SQUARE(int *inval,
double *outval,
short *inind,
short *outind,
char *sqlstate,
char *funcname,
char *specname,
char *msgtext)
	{
if (*inind<0)
	  *outind=-1;
	else 	  {
	   *outval=*inval;
	   *outval=(*outval)*(*outval);
	   *outind=0;
	  }
	return;
	}

To create the external service program so it can be debugged:

CRTCMOD MODULE(mylib/square) DBGVIEW(*SOURCE)
CRTSRVPGM SRVPGM(mylib/math) MODULE(mylib/square)
		EXPORT(*ALL) ACTGRP(*CALLER)

 

Using an external function, parameter style GENERAL

The CREATE FUNCTION statement:

CREATE FUNCTION SQUARE(INT) RETURNS INT CAST FROM FLOAT

LANGUAGE C EXTERNAL NAME 'MYLIB/MATH(SQUARE)'

DETERMINISTIC NO SQL NO EXTERNAL ACTION PARAMETER STYLE GENERAL ALLOW PARALLEL 

The code:

double SQUARE(int *inval)
{
  double outval;
  outval=*inval;
  outval=outval*outval;
	return(outval);
	}

To create the external service program so it can be debugged:

CRTCMOD MODULE(mylib/square) DBGVIEW(*SOURCE)

   CRTSRVPGM SRVPGM(mylib/math) MODULE(mylib/square)
		EXPORT(*ALL) ACTGRP(*CALLER)

 

Parent topic:

Examples: UDF code