DB2 Table UDF in COBOL or PL/1

shivani_jha · New User Joined: 12 Feb 2007 Posts: 11 Location: mumbai

Hi, I am writing a DB2 user defined function which returns a table of 2 columns.

I wan to know how can i return a table in PL/1 or COBOL. Can anybody has a sample code which i can refer for my requirement?

Regards,
Shivani

dick scherrer · Posted: Mon Mar 17, 2008 5:01 pm

Hello,

Please explain your requirement in more detail.

If the function creates a 2-column table, why can the pl/i or cobol code not simply read the table?

I suspect that i am missing something.

shivani_jha · New User Joined: 12 Feb 2007 Posts: 11 Location: mumbai

Hi Following is the function for which I want code in the host language (COBOL or PL/1)

CREATE FUNCTION GAME_RESULTS(INTEGER)
RETURNS TABLE (WEEK INTEGER,
WINNER CHAR(20),
WINNER_SCORE INTEGER,
LOSER CHAR(20),
LOSER_SCORE INTEGER)
EXTERNAL NAME GAMES
LANGUAGE COBOL
PARAMETER STYLE DB2SQL
NO SQL
DETERMINISTIC
NO EXTERNAL ACTION
FENCED
SCRATCHPAD
FINAL CALL
DISALLOW PARALLEL
CARDINALITY 300;

I hope it helps...Please let me know if you want more information.

anv2005 · New User Joined: 14 Jul 2005 Posts: 44 Location: US

Can't you DECLARE cursor:

EXEC SQL DECLARE MYCSR CURSOR FOR
SELECT WEEK, WINNER, ... FROM TABLE (GAME_RESULTS(<integer>))
END-EXEC.

shivani_jha · New User Joined: 12 Feb 2007 Posts: 11 Location: mumbai

No I can not declare cursor......as the approach taken for this project is to write UDF....and this is just an example there are so many functions that we need to write in this way.

I have verified that this is possible as per the information available on Internet but could not find any example for this.

anv2005 · New User Joined: 14 Jul 2005 Posts: 44 Location: US

The DECLARE CURSOR is used to invoke UDF within the COBOL or PL/I program. Then, FETCH the rows from the TABLE as returned by your UDF.

shivani_jha · New User Joined: 12 Feb 2007 Posts: 11 Location: mumbai

Thanks for the info.

Suppose I am using SELECT WHETHER,CITY FROM UDF_FUNCTION(AA);

and this UDF_FUNCTION is returning more than one row then what should be returned from the PL/1 program or COBOL program....is it a array for all the columns or just one occurence for those fields.....