User Defined Table Function

SALVATORI GIORGIO CELSO

User Defined Table Function
Hi to all,
I'm almost new in this list aand I've a small problem ( I hope ).
I'd want write a User Define Function that return a table.
I'm working on Db2 v7 for Z/os in Cobol.
I'm not able to find a sample of this function on internet or IBM manuals

could someone help me?


thank very much

Giorgio Salvatori

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Turner

Re: User Defined Table Function
(in response to SALVATORI GIORGIO CELSO)
Giorgio

Here is an example. First the table function definition:

CREATE FUNCTION CUST_ORD(CUST_NO INTEGER)
RETURNS TABLE (ORD_NO INTEGER,
ORD_DATE DATE,
AMOUNT DECIMAL(9,2))
EXTERNAL NAME CUSTORD
LANGUAGE COBOL
...

and here is a fragment from the COBOL code:

IDENTIFICATION DIVISION.
...
DATA DIVISION.
...
LINKAGE SECTION.
01 IN-PARM PIC S9(9) COMP.
01 ORD-NO PIC S9(9) COMP.
01 ORD-DATE PIC X(10).
01 AMOUNT PIC S9(7)V99 COMP-3.
01 IN-IND PIC S9(4) COMP.
01 ORD-NO-IND PIC S9(4) COMP.
01 ORD-DATE-IND PIC S9(4) COMP.
01 AMOUNT-IND PIC S9(4) COMP.
...
01 CALL-TYPE PIC S9(9) COMP.
...
PROCEDURE DIVISION USING IN-PARM, ORD-NO, ORD-DATE,
AMOUNT, IN-IND, ORD-NO-IND, ORD-DATE-IND,
AMOUNT-IND, SQLSTATE, FUNC-NAME,
SPECIF-NAME, MESSGE, CALL-TYPE.
EXEC-SQL DECLARE C1 CURSOR FOR
SELECT ORD_NO, ORD_DATE, AMOUNT
FROM ORD WHERE CUST_NO = :IN-PARM
END-EXEC.
IF CALL-TYPE = -1 THEN
EXEC SQL OPEN C1 END-EXEC.
MOVE ‘00000’ TO SQLSTATE
END-IF.
IF CALL-TYPE = 0 THEN
EXEC SQL FETCH C1 INTO :ORD-NO, :ORD-DATE, :AMOUNT END-EXEC.
IF SQLCODE = 0 THEN
MOVE ‘00000’ TO SQLSTATE
ELSE
MOVE ‘02000’ TO SQLSTATE
END-IF
END-IF.
IF CALL-TYPE = +1 THEN
EXEC SQL CLOSE C1 END-EXEC.
MOVE ‘00000’ TO SQLSTATE
END-IF.
GOBACK.

There is one INTEGER input parameter (CUSTNO). There are three output
parameters, one for each column of the output table.

The function is called multiple times:

· Call Type -1 is the Open call.
· Call Type 0 is the Fetch call.
· Call Type +1 is the Close call.

DB2 will call the function once for the Open call and then many times for
the Fetch call, until the function returns SQLSTATE 02000 (end of data). DB2
then makes one final Close call. The function returns one result row for
each Fetch call except the one that signals end of data.

The error handling in the example assumes only SQLCODE 0 or +100 is returned
by the Fetch. You should replace this with better code.

Hope that helps.

Regards
Mike Turner
Email: [login to unmask email]
Tel: +44 (0)1565-873702
Web: www.michael-turner.ltd.uk

-----Original Message-----
From: [login to unmask email]
[mailto:[login to unmask email]On Behalf Of Giorgio Salvatori
Sent: Friday, 10 December 2004 16:37
To: [login to unmask email]
Subject: User Defined Table Function


Hi to all,
I'm almost new in this list aand I've a small problem ( I hope ).
I'd want write a User Define Function that return a table.
I'm working on Db2 v7 for Z/os in Cobol.
I'm not able to find a sample of this function on internet or IBM manuals

could someone help me?


thank very much

Giorgio Salvatori

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm