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

Question

I am trying to create a custom function that returns a table in DB2. Here is what I still have.

This is the table I'm using:

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 custom function that returns a table (which works 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 how far I could 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 the 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 to do this, I get SQL0628N Multiple or conflicting keywords containing the sentence "RETURNS", 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 to do this (the RETURNS clause is deleted), I get the SQL0491N CREATE FUNCTION or ALTER MODULE statement used to define "CORPDATA.MODULE1.DEPTEMPLOYEES" should have a RETURNS clause and one of the following: EXTERNAL (with other keywords); SQL function body or SOURCE offer. 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 to do this (deleted by BEGIN ATOMIC), I get SQL0104N An unexpected token "SELECT" was found after "INISTIC RETURN". Expected tokens may include: "(". LINE NUMBER = 9. SQLSTATE = 42601 :) Yes, it says "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 

Answer 1

It seems that DB2 LUW, as of version 11.1, does not yet fully support table functions inside modules if this table function does not include a PIPE statement. This is despite published documentation suggesting that this is possible with some limitations. This is the reason you get the "conflicting keywords" error because the pipelined function can only return one row at a time opposite to RETURNS TABLE.

Also check if the implementation of the pipeline function can fulfill your requirements in this area.

When the modules arrived on Db2 V9.7, they did not support the table functions in the modules at all, but since V10.1 there seemed to be some support for the functions of the module table, although the documentation was uncertain, there were no working examples and the samples were not specially updated for this.

Here is a link to this limitation on developerworks starting in 2014.

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

You can also send a note about the documentation to the Db2 Knowledge Center page for alter-module , as well as limits-on-modules , which does not mention the additional restriction on the function of the table inside the modules regarding the use of the PIPE instruction.