trying to create SQL-UDF on DB2 9 on z/os

[Herb]

CREATE FUNCTION test( P_CTY_ST_C VARCHAR(10), P_DATE DATE )
RETURNS CHARACTER(3)
-- VERSION V1
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN

RETURN VARCOUNT;
END


-- i get the following error... Can someone please help...thx

Statusbar 7/31/2012 11:24:38 AM DB2 Database Error: ERROR [42601] [IBM][DB2] SQL0199N The use of the reserved word "BEGIN" following "" is not valid. Expected tokens may include: "<END-OF-STATEMENT> SPECIFIC FENCED RETURN SOURCE PARAMETER".

[Cindy Ross]

You can get rid of that error by changing this:
BEGIN
to this:
BEGIN ATOMIC

(Then you get a different error because VARCOUNT isn't declared
anywhere, but that should be easy to fix.)

[Herb]

CREATE FUNCTION test( P_CTY_ST_C VARCHAR(10), P_DATE DATE )
RETURNS CHARACTER(3)
-- VERSION V1
DETERMINISTIC
NO EXTERNAL ACTION

BEGIN ATOMIC
DECLARE OUT_TIME_ZONE CHAR(3);
DECLARE V_YEAR CHAR(4);
DECLARE V_BEG_D DATE;
DECLARE V_END_D DATE;
DECLARE V_STD_TIME_DVTN_H SMALLINT;
DECLARE V_ADV_TIME_DVTN_H SMALLINT;

SET V_YEAR = YEAR(P_DATE);
END

-- still getting this error whether i use BEGIN or BEGIN ATOMIC...

Create user-defined function returns SQLCODE: -199, SQLSTATE: 42601.
JISAHV1.TEST: 6: ILLEGAL USE OF KEYWORD BEGIN. TOKEN <END-OF-
STATEMENT> SPECIFIC FENCED RETURN SOURCE PARAMETER WAS EXPECTED.
SQLCODE=-199, SQLSTATE=42601, DRIVER=3.61.65
ILLEGAL USE OF KEYWORD BEGIN. TOKEN <END-OF-STATEMENT> SPECIFIC
FENCED RETURN SOURCE PARAMETER WAS EXPECTED. SQLCODE=-199,
SQLSTATE=42601, DRIVER=3.61.65

[Herb]

On Aug 1, 8:18 am, Herb <HERB_VO...@JBHUNT.COM> wrote:
> CREATE FUNCTION test(  P_CTY_ST_C       VARCHAR(10), P_DATE     DATE )
>   RETURNS CHARACTER(3)
>     -- VERSION V1
>      DETERMINISTIC
>      NO EXTERNAL ACTION
> BEGIN ATOMIC
>         DECLARE OUT_TIME_ZONE CHAR(3);
>         DECLARE V_YEAR  CHAR(4);
>         DECLARE V_BEG_D  DATE;
>         DECLARE V_END_D  DATE;
>         DECLARE V_STD_TIME_DVTN_H  SMALLINT;
>         DECLARE V_ADV_TIME_DVTN_H  SMALLINT;
>
>                 SET V_YEAR = YEAR(P_DATE);

RETURN OUT_TIME_ZONE;

[Herb]

here is the stub of my function...:

CREATE FUNCTION test( P_CTY_ST_C VARCHAR(10), P_DATE
DATE )
RETURNS CHARACTER(3)
-- VERSION V1
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE OUT_TIME_ZONE CHAR(3);
DECLARE V_YEAR CHAR(4);
DECLARE V_BEG_D DATE;
DECLARE V_END_D DATE;
DECLARE V_STD_TIME_DVTN_H SMALLINT;
DECLARE V_ADV_TIME_DVTN_H SMALLINT;

SET V_YEAR = YEAR(P_DATE);

RETURN OUT_TIME_ZONE;

[Cindy Ross]

Sorry, Herb, I guess I can't help after all. I only use DB2 for LUW,
not z/OS. Interestingly, I do get an error with sqlstate 42601
referencing an unexpected token "BEGIN" on the original code you posted,
which is resolved on my system by adding "ATOMIC". With your new code,
I no longer get this error, so I guess this must be specific to z/OS.
(I did get a data type incompatibility error, and had to change the
V_YEAR declaration to be an INT instead of CHAR(4) to match the output
of the YEAR function.)

In other words, the following works fine for me on DB2 LUW.
Sorry, I don't know why it's not working for you on z/OS:

CREATE FUNCTION test( P_CTY_ST_C VARCHAR(10), P_DATE DATE )
RETURNS CHARACTER(3)
-- VERSION V1
DETERMINISTIC
NO EXTERNAL ACTION
BEGIN ATOMIC
DECLARE OUT_TIME_ZONE CHAR(3);

-- DECLARE V_YEAR CHAR(4);
DECLARE V_YEAR INT;

DECLARE V_BEG_D DATE;
DECLARE V_END_D DATE;
DECLARE V_STD_TIME_DVTN_H SMALLINT;
DECLARE V_ADV_TIME_DVTN_H SMALLINT;

SET V_YEAR = YEAR(P_DATE);
RETURN OUT_TIME_ZONE;

END @
DROP FUNCTION TEST @