AW: SQL User Defined Function Location - Can it be qualified of doeslibrary need to be in *LIBL ?

Hi,

It depends on the naming you are using where the UDF is searched.
With System naming and unqualified access to an UDF or a stored procedure,
the library list is searched.
With SQL naming and unqualified access to an UDF or a stored procedure the
path is searched, the library list is not considered at all.

To set the current path, you can use the SQL statement SET PATH.
In the set path statement you list the libraries/schemas where your UDFs and
Stored Procedures are located.
Like a library list, the path is searched from the first specified schema to
the last one.
The first occurence of the UDF or stored procedure with the appropriate
parameter definition is used.

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 Richard Schoen
Gesendet: Saturday, June 28, 2008 05:44
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL User Defined Function Location - Can it be qualified of
doeslibrary need to be in *LIBL ?


Hello All,

We created an SQL user defined function in a service program for use within
our application code.

As an example let's say the service program is called UDFRPG and the
function is called UDF1. UDFRPG containing UDF1 is located in library
UDFLIB.

When I use the UDF I normally specify it as such:

SELECT * FROM LIB/FILE WHERE UDF1() = 1

If the UDFRPG library is in my library list, SQL finds UDFRPG/UDF1 just
fine.

If not, the SQL processor tells me UDF1 was not found.

Now the question:

Is there a way to qualify a call to a SQL UDF so that it does not need to be
in the library list of the calling job ?

I wanted to ask here before I invested time researching since someone here
has undoubtedly come across the same issue.

**Note: The job is running from a Java process that uses JDBC/JT400. I know
I can affect the library list with the JDBC property called: "libraries",
but I was curious on the qualification of UDF calls because we don't want to
hard set libraries in the JDBC properties settings.

Any thoughts would be appreciated.

Regards,
Richard Schoen
RJS Software Systems Inc.
"Get the information you need. Now!"
Email: richard@xxxxxxxxxxxxxxx
Web Site: http://www.rjssoftware.com <http://www.rjssoftware.com/>
Tel: (952) 898-3038
Fax: (952) 898-1781
Toll Free: (888) RJSSOFT