AW: How do I create a UDF?

["Birgitta Hauser" <Hauser@xxxxxxxxxxxxxxx>]

Hi,

the easiest way, to create an SQL stored procedure or an user defined
function is to use the iSeries Navigator Database (and not WDSC!).
It is also possible to type (without prompting) the CREATE FUNCTION command
within interactive SQL.

For more information about iSeries Navigator and User Defined Functions, you
may check the following redbooks:

Modernizing IBM eServer iSeries Application Data Access - A Roadmap
Cornerstone
http://www.redbooks.ibm.com/abstracts/sg246393.html?Open

Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal
Database for iSeries
http://www.redbooks.ibm.com/abstracts/sg246503.html?Open



Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"


-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Koester, Michael
Gesendet: Tuesday, 18. August 2009 17:35
An: MIDRANGE-L@xxxxxxxxxxxx
Betreff: How do I create a UDF?

(If this would be more appropriate to the RPG or WDSC lists, let me
know.)

 

I noticed some articles that reference some very handy user defined
functions that I'd like to incorporate and use in embedded SQL
statements.  Having never created a UDF before, I thought I could copy
some sample code and make it happen.  It appears that CREATE FUNCTION is
not available to me.  Interactive SQL does not list FUNCTION as a
supported argument to the CREATE verb.  Coding it into SQLRPGSRC makes
the WDSCi syntax-checker mad (it doesn't like the "Language SQL"
statement, among others), so I am wondering if I'm going about this all
wrong, or if we don't have the necessary licensed programs on our
machine, or ???

The UDF I tried to create is:

Create Function myLib/IsNumeric 

(@TestData VarChar(64))

Returns Char(1)

Deterministic

Not Fenced

Language SQL

Set Option Commit=*None, UsrPrf=*Owner 

Begin

 Declare @Double Double Not Null Default 0;

 Declare Exit Handler For SQLException 

  Return 'N'; 

 If @TestData Is Null Then 

  Return 'N';

 End If; 

 Set @Double=Cast(@TestData As Double); 

 Return 'Y';

End

I didn't see anything that ran counter to the syntax in the WDSCi
Reference.

Running V5R4 and WDSCi 7.0.0.8  Been doing embedded SQL programming here
for over a year. 

TIA.
--Michael Koester