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

[CRPence <CRPbottle@xxxxxxxxx>]

  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.