14.1 Sample User Defined Function

Creating a Sample User Defined Function in DB2 UDB

Count(*) Sample Table	Click on Create Function	Create UDF	Execute	Executing a UDF	Result of UDF

Create table DDL

CREATE TABLE "DB2ADMIN"."AQUA_CUSTOMER" (
    "F_NME"     VARCHAR(25),
    "L_NME"     VARCHAR(25),
    "TITLE"     VARCHAR(10),
    "ADDR_1"    VARCHAR(25),
    "ADDR_2"    VARCHAR(25),
    "CITY"      VARCHAR(25),
    "ZIP"       VARCHAR(25)
    )

Insert Statements

INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('James', 'Smith', 'Manager', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Andrew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drake', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Micheal', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Drew', 'Smith', 'CTO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO
INSERT INTO DB2ADMIN.AQUA_CUSTOMER(F_NME, L_NME, TITLE, ADDR_1, ADDR_2, CITY, ZIP)
    VALUES('Barry', 'Hike', 'CEO', '1970 Napa Ct.', '', 'Bothell', '98011')
GO

SAMPLE DB2 USER DEFINED FUNCTION

This User Defined Function when executed will delete row from the table DB2ADMIN.AQUA_CUSTOMER based on the input parameter Aqua_Code. This parameter is matched to the specified TITLE column in the table. For example, if you want the UDF to delete rows from the table with TITLE=Manager, you need to specify the input parameter AQUA_CODE as 'Manager'.

-----------------------------------------------------------------------------------------------------------
 -- This UDF takes in a code and removes rows in the table with that CODE
-----------------------------------------------------------------------------------------------------------

CREATE FUNCTION DB2ADMIN.UDF_SAMPLE_1 (AQUA_CODE varchar (10))
  RETURNS TABLE (title  varchar (10),
                 f_nme varchar(25),
                 l_nme varchar(25))
  MODIFIES SQL DATA
  LANGUAGE SQL
  BEGIN ATOMIC

    DECLARE  V_F_NME varchar(25);
    DECLARE  V_L_NME varchar(25);

    SET (V_F_NME, V_L_NME) = (SELECT f_nme, l_nme
                               FROM OLD TABLE (DELETE FROM DB2ADMIN.AQUA_CUSTOMER
                                                WHERE AQUA_CUSTOMER.TITLE = AQUA_CODE ));

    RETURN VALUES (AQUA_CODE,V_F_NME, V_L_NME);
  END

Executing DB2 USER DEFINED FUNCTION

Connect to  the Database Server and Open a Query Analyzer. Type the following and Execute to see the UDF result

SELECT * FROM TABLE(DB2ADMIN.UDF_SAMPLE_1('Manager')) AS FUNCTABLE
GO