Re: SQL User Defined Function Location - Can it be qualified of does library need to be in *LIBL ?

The UDF can be qualified when using SQL naming; e.g. UDFLIB.UDF1()

I either do not recall, or have never known, why there is no support for System naming for qualified functions; i.e. I have never known of the UDFLIB/UDF1() to be allowed.

For function names and for specific names specified for sourced functions, database manager uses the SQL path in conjunction with function resolution, as described under _Function resolution_
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzfuncres.htm

For information about when the SQL path is used to resolve unqualified names in both dynamic & static SQL statements and the effect of its value, see _Unqualified function, procedure, specific, and distinct_ _type names_.
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzqualun2.htm

Refer to the SQL PATH, CURRENT PATH special register, and SQL SET PATH
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzpath.htm
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzcurpath.htm
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzsetpath.htm

Also see SQLPATH for its use on SQL SET OPTION

Regards, Chuck

Richard Schoen wrote:

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.