UDB Migration from LUW - Mainframe DB2

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

db2sysc@yahoo.com - 30 Mar 2005 19:25 GMT

Hi.

We have UDB 8.2 on Windows and we want it to be migrated to V8 of DB2
on Mainframe.

1. Can a SQL FUNCTION contains SELECT statements in RETURN Clause in
Mainframe? below function compiles in UDB.. Will it compile on
Mainframe DB2 V8?

create FUNCTION X.IsHoliday(dDate TIMESTAMP , HolidaySched varchar(12))
Returns INTEGER

READS SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL
BEGIN ATOMIC

RETURN
SELECT count(1)
FROM XXX
WHERE XXcode = HolidaySched
and country = 0
and holidaydate = dDate;

END

2) CAN A READ STORED PROCEDURE BE CALLED FROM A SQL FUNCTION? Any
example?

Reply to this Message

Serge Rielau - 30 Mar 2005 20:13 GMT

> Hi.
>
[quoted text clipped - 21 lines]
>
> END

Remove the BEGIN ATOMIC .. END
Also you need to add braces around the SELECT (I thought DB2 for LUW
also requires them....)

> 2) CAN A READ STORED PROCEDURE BE CALLED FROM A SQL FUNCTION? Any
> example?

In DB2 V8 for zOS only the RETURN statement is allowed.
So the answer is no.

Cheers
Serge

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message

db2sysc@yahoo.com - 30 Mar 2005 21:28 GMT

Serge,

The Mainframe DB2 manual says nothing about RETURN statement having a
SELECT clause.

Reply to this Message

Serge Rielau - 30 Mar 2005 22:42 GMT

> Serge,
>
> The Mainframe DB2 manual says nothing about RETURN statement having a
> SELECT clause.

CREATE FUNCTION (SQL scalar)
http://publib.boulder.ibm.com/infocenter/dzichelp/topic/com.ibm.db2.doc.sqlref/b
jnrmstr390.htm
introduces the RETURN statement. It also specified READS SQL DATA as an
option (a required, but not sufficient sign)
RETURN statement speficies expression
expression specifies "scalar subselect".
A quick (I confess) browse trhough CREATE function, return statement and
expression does not yield an exception.
Thus to me the docs support the notion that:
CRAEET FUNCTION ...
RETURNS INT
READS SQL DATA
RETURN (SELECT count(*) FROM T)

is legal in DB2 V8 for zOS
I don't have ready access to a DB2 zOS system... so I can't validate.

Signature

Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Reply to this Message