DB2V9.1 UDFScalar error during build. Any tool to convert T-SQL to DB2 SQL?

[okonita]

Hi all,

I have a T-SQL FUNCTION that I am trying to convert to DB2 SQL function. I ran the code through IBM migration Tool but was unable to get a clean conversion.

So okay, I ran the code through another product SwisSQL by AdventNet which appeared to have converted cleanly.

However, when I tried to build and register the UDF in my development center, I am getting this error which has frustrated me all day: The code snippet and error message is as follows:

CODE:
CREATE FUNCTION fnIsVowel
(
c CHAR(1)
)
RETURNS SMALLINT
SPECIFIC fnIsVowel
BEGIN ATOMIC
IF ( c = 'A' ) OR ( c = 'E' ) OR ( c = 'I' ) OR ( c = 'O' ) OR ( c = 'U' ) OR ( c = 'Y' ) THEN
RETURN 1;
END IF;
RETURN 0;
END

CREATE FUNCTION fnSlavoGermanic
(
Word CHAR(50)
)
RETURNS SMALLINT
SPECIFIC fnSlavoGermanic
BEGIN ATOMIC

--Catch NULL also...
IF ( LOCATE('W', Word) > 0 ) OR ( LOCATE('K', Word) > 0 ) OR ( LOCATE('CZ', Word) > 0 ) THEN
RETURN 1;
END IF;

RETURN 0;
END
ERROR MESSAGE
:
DB2ADMIN.fnIsVowel - Build started.
Create user-defined function returns -104.
DB2ADMIN.fnIsVowel: 20: IBMCLI DriverDB2/LINUX SQL0104N An unexpected token "CHAR" was found following "Word ". Expected tokens may include: ".". LINE NUMBER=20. SQLSTATE=42601

DB2ADMIN.fnIsVowel - Build failed.
DB2ADMIN.fnIsVowel - Roll back completed successfully.

As you can see, it seems to approve the first create function but not the second. Can someone point out what is wrong with this statements? So, I added a end of statement delimiter to the first create function to seperte the two functions but then, the parser now don't like the first create function, returning the same message as shown above but this time "IBMCLI DriverDB2/LINUX SQL0104N An unexpected token "CHAR" was found following "c". Expected tokens may include: ".". LINE NUMBER=20. SQLSTATE=42601".

Can someone point out what it is that I am doing wron with this statements? Is there a reliable IBM tool that really can do T-SQL conversion to DB2 SQL"

Thanks

[sakumar9]

The problem is missing delimiter. I tried executing these commands, and I was able to repro the problem.

 Working Code:
 
CREATE FUNCTION fnIsVowel
(
c CHAR(1)
)
RETURNS SMALLINT
SPECIFIC fnIsVowel
BEGIN ATOMIC
IF ( c = 'A' ) OR ( c = 'E' ) OR ( c = 'I' ) OR ( c = 'O' ) OR ( c = 'U' ) OR ( c = 'Y' ) THEN
RETURN 1;
END IF;
RETURN 0;
END @
 
CREATE FUNCTION fnSlavoGermanic
(
Word CHAR(50)
)
RETURNS SMALLINT
SPECIFIC fnSlavoGermanic
BEGIN ATOMIC
 
--Catch NULL also...
IF ( LOCATE('W', Word) > 0 ) OR ( LOCATE('K', Word) > 0 ) OR ( LOCATE('CZ', Word) > 0 ) THEN
RETURN 1;
END IF;
 
RETURN 0;
END @