How to create UDF in DB2 for Zos

SriniShareTips
SriniShareTips

Simple UDF code, it is written in ‘C’ Language. UDFs are small programs that you can write to perform an operation.

CREATE FUNCTION DAYSINMONTH(DATE)
  RETURNS INTEGER
  EXTERNAL NAME 'DAYMTH'
   LANGUAGE C;

This statement creates a UDF named DAYSINMONTH, with one parameter of DATE data type. It returns a single value of INTEGER data type. The external name for the function, that is the actual program name, is DAYMTH and it is coded in C. Most of the parameters have been omitted from this simple CREATE FUNCTION example.

Once the user-defined function has been created, and the appropriate authority has been GRANTed, the UDF can be used in an SQL statement, for example:

SELECT  EMPNO, LASTNME, BIRTHDATE, DAYSINMONTH(BIRTHDATE)
FROM    PAYROLL.EMP
WHERE   DAYSINMONTH(BIRTHDATE) < 31;

The results of this statement would be a list of employees whose birth date falls in a month having fewer than 31 days (that is, February, April, June, September, and November). This assumes that the program for the user-defined function, DAYSINMONTH, is correctly coded to examine the date specified as input and return the actual number of days in the month.

Refer more here

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.