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

Chuck - as far as I have been able to discern, the *SYS naming for qualifying a UDF looks like division - it fails in the parser, as I recall. One of those ambiguous patterns the lexer and parser don't know what to do with - heh!

Vern

-------------- Original message --------------
From: 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/rbafzfuncr
es.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/rbafzqualu
n2.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/rbafzcurpa
th.htm
http://publib.boulder.ibm.com/infocenter/systems/scope/i5os/topic/db2/rbafzsetpa
th.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.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.