How to specify the library of an SQL function on the IBM i

I'm currently using an SQL CONNECT statement to access a remote system. While troubleshooting, I would like to see the current library list, so I made up a quick CL to retrieve it, and hooked up an SQL function (with CREATE FUNCTION) to call it.

When logged into the remote system directly, and when I have the library the function resides in contained in the library list (QGPL), it works (select rtvlibl() from sysibm/sysdummy1). However, when connecting remotely (like through STRSQL), QGPL is not in the library list, and it can't find it the function.

I can't seem to specify the library like a procedure - QGPL/RTVL开发者_开发知识库IBL() - fails in *SYS naming convention. *SQL naming convention also fails on QGPL.RTVLIBL().

I'm on V6R1.

Try this using *SQL naming convention:

CONNECT (systemname)
SET PATH=QGPL
SELECT RTVLIBL() FROM SYSIBM.SYSDUMMY1

Using SET PATH you can provide a comma-separated list of libraries for SQL to use when searching for UDFs.

By the way, I just did a CONNECT between two V5R4 systems, and on the target system I saw the server job that was running my remote queries. This job (which popped up in QCMN subsystem) appeared to use QDFTJOBD job description, so that may be where your library list is coming from.