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

Stored Procedures, Triggers, and User-Defined Functions on DB2 Universal
Database for iSeries

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
"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

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)


Not Fenced

Language SQL

Set Option Commit=*None, UsrPrf=*Owner


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


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

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

--Michael Koester

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2020 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].