title: How to create a user defined function that returns a table in a DB2 module?
body: <p>I am trying to create a user-defined function that returns a table in DB2. Here is what I have so far.</p>
  
  <p>This is a table that I use:</p>
  
  <pre><code>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");
  </code></pre>
  
  <p>This is a user-defined function that returns a table (which is working fine):</p>
  
  <pre><code>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
  </code></pre>
  
  <p>This is a how far I was able to get with the module:</p>
  
  <pre><code>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))
  </code></pre>
  
  <p>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.</p>
  
  <p>When I try this, I get 
  SQL0628N  Multiple or conflicting keywords involving the "RETURNS" clause are
  present.  LINE NUMBER=16.  SQLSTATE=42613</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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".</p>
  
  <pre><code>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
  </code></pre>