How to create a user defined function that returns a table in a DB2 module?

Question

I am trying to create a user-defined function that returns a table in DB2. Here is what I have so far.

This is a table that I use:

CREATE TABLE "CORPDATA"."EMPLOYEE" (
    "EMPNO" CHAR(6) NOT NULL, 
    "FIRSTNME" VARCHAR(12) NOT NULL, 
    "MIDINIT" CHAR(1) NOT NULL, 
    "LASTNAME" VARCHAR(15) NOT NULL, 
    "WORKDEPT" CHAR(3), 
    "PHONENO" CHAR(4), 
    "HIREDATE" DATE, 
    "JOB" CHAR(8), 
    "EDLEVEL" SMALLINT NOT NULL, 
    "SEX" CHAR(1), 
    "BIRTHDATE" DATE, 
    "SALARY" DECIMAL(9 , 2), 
    "BONUS" DECIMAL(9 , 2), 
    "COMM" DECIMAL(9 , 2)
);

ALTER TABLE "CORPDATA"."EMPLOYEE" ADD CONSTRAINT "PK_EMPLOYEE" PRIMARY KEY
("EMPNO");

This is a user-defined function that returns a table (which is working fine):

CREATE OR REPLACE FUNCTION "CORPDATA"."DEPTEMPLOYEES" (DEPTNO CHAR(3))
 RETURNS TABLE (EMPNO CHAR(6),
                LASTNAME VARCHAR(15),
                FIRSTNAME VARCHAR(12))
 LANGUAGE SQL
 READS SQL DATA
 NO EXTERNAL ACTION
 DETERMINISTIC
 BEGIN ATOMIC
   RETURN
     SELECT EMPNO, LASTNAME, FIRSTNME
     FROM CORPDATA.EMPLOYEE
     WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;
 END

This is a how far I was able to get with the module:

CREATE MODULE CORPDATA.MODULE1

ALTER MODULE CORPDATA.MODULE1
PUBLISH FUNCTION DEPTEMPLOYEES2 (DEPTNO CHAR(3))
RETURNS TABLE (EMPNO CHAR(6),
                LASTNAME VARCHAR(15),
                FIRSTNAME VARCHAR(12))

Any attempts to actually add a function to a module failed with various errors. Here is my DB2 version information: Database server = DB2/LINUXX8664 11.1.2.2 This is an Express-C installation under Redhat.

When I try this, I get SQL0628N Multiple or conflicting keywords involving the "RETURNS" clause are present. LINE NUMBER=16. SQLSTATE=42613

ALTER MODULE corpdata.module1
ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     RETURNS TABLE (EMPNO CHAR(6),
                    LASTNAME VARCHAR(15),
                    FIRSTNAME VARCHAR(12))
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
BEGIN ATOMIC
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
       FROM CORPDATA.EMPLOYEE
       WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;
END

When I try this (removed RETURNS clause), I get SQL0491N The CREATE FUNCTION or ALTER MODULE statement used to define "CORPDATA.MODULE1.DEPTEMPLOYEES" must have a RETURNS clause, and one of: the EXTERNAL clause (with other required keywords); an SQL function body; or the SOURCE clause. LINE NUMBER=8. SQLSTATE=42601

ALTER MODULE corpdata.module1
ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
     LANGUAGE SQL
     READS SQL DATA
     NO EXTERNAL ACTION
     DETERMINISTIC
BEGIN ATOMIC
     RETURN
       SELECT EMPNO, LASTNAME, FIRSTNME
       FROM CORPDATA.EMPLOYEE
       WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO;
END

When I try this (removed BEGIN ATOMIC), I get SQL0104N An unexpected token "SELECT" was found following "INISTIC RETURN ". Expected tokens may include: "(". LINE NUMBER=9. SQLSTATE=42601 :) Yes, it does say "INISTIC".

ALTER MODULE corpdata.module1
ADD FUNCTION DEPTEMPLOYEES (DEPTNO CHAR(3))
 LANGUAGE SQL
 READS SQL DATA
 NO EXTERNAL ACTION
 DETERMINISTIC
 RETURN
   SELECT EMPNO, LASTNAME, FIRSTNME
   FROM CORPDATA.EMPLOYEE
   WHERE WORKDEPT = "DEPTEMPLOYEES".DEPTNO

Racquelracquet

Do you think it might be useful to include the "various errors" that you encounter, or should we start guessing? – Carabin

May be this will give you a hint: "The module function definition can only specify the RETURNS TABLE clause when the SQL-routine-body is an compound SQL (compiled) statement that specifies NOT ATOMIC". – Carabin

I will update with a couple of things that I tried. – Racquelracquet

Well, you need to read the syntax diagram in the manual again. The first two attempts fail because of the unsupported inlined compound SQL (begin atomic); the last one fails because it lacks the returns clause. – Carabin

But if I add the RETURNS clause it gives me SQL0628N Multiple or conflicting keywords involving the "RETURNS" clause are present. LINE NUMBER=10. SQLSTATE=42613, like in #1. So, basically, you are saying, it is not possible? – Racquelracquet

Answer 1

It appears that DB2 LUW as at version 11.1 does not yet fully support table-functions inside modules unless that table-function includes the PIPE statement. This is despite the published documentation suggesting that it's possible with some restrictions. This is the reason you get the "conflicting keywords" error, since a pipelined function can only return one row at a time which is the opposite of RETURNS TABLE.

Check also if implementing a pipelined function might satisfy your requirements in this area.

When modules arrived at Db2 V9.7 they did not support table functions in modules at all, but since V10.1 there appeared to be some support for module table-functions, although the documentation was vague, lacking worked examples and the samples were not updated specifically for this.

There's a reference to this limitation on developerworks dating from 2014.

If this is important to your company, consider opening a request for enhancement (RFE), google for details.

You might also want to submit a documentation remark on the Db2 Knowledge Centre page for alter-module, and also on the restrictions-on-modules page, which do not mention the additional restriction on table-function inside modules regarding the use of the PIPE statement.

Tush