UDB Migration from LUW - Mainframe DB2

[db2sysc]

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?

[Serge Rielau]

db*****@yahoo.com wrote:

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 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

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[db2sysc]

Serge,

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

[Serge Rielau]

db*****@yahoo.com wrote:

Serge,

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

CREATE FUNCTION (SQL scalar)
http://publib.boulder.ibm.com/infoce...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.

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab