Re: SQL0204 - Function not found using a local UDF with a ALIAS pointing to a remote system

On 15-Jan-2016 13:51 -0700, Charles Wilt wrote:

I created a UDF that does some character manipulation..

I tried to use it on a column in remote table via an SQL ALIAS.

So something like the following was done?:

create alias locallib.myalias
for rmtSys.tgtTblLib.tgtTbl

select locallib.myudf(mydata)
from locallib.myalias

But I get a SQL0204 - MYUDF in LOCALLIB type *N not found.

Same error with this

select locallib.myudf('TEST123')
from locallib.myalias

But this works.

select locallib.myudf('TEST123')
from sysibm.sysdummy1

So my UDF is fine, I'm calling it properly. I think I'm hitting a
limitation in the support for remote tables.

Can anyone confirm? And is there some way around this limit... other
than copying the remote file to the local system?

Purely SWAG in this reply, being unable to test for the effect seen, and per limited reading of what is possible with and knowledge of what is the implementation with, the three-part-naming support:

Any luck with the following?:

select locallib.myudf('TEST123')
from ( select *
from locallib.myalias
) as LD

select locallib.myudf(LD.mydata)
from ( select *
from locallib.myalias
) as LD

I fully expect that replacing the reference to the ALIAS to a reference to the TABLE() of a User Defined Table Function (UDTF) [one that does nothing more than a RETURN of the SELECT from the remote table] would be functional, per feedback from Rob in a past discussion:
[http://archive.midrange.com/midrange-l/201502/msg00454.html]
[http://archive.midrange.com/midrange-l/201502/msg00481.html]

Imagine that the failure experienced, were the same as what would be seen with the following scripted actions, assuming that LOCALLIB and\or MYUDF do not exist at the current server; that is to allude, perhaps what is the origin for the error, whereby the effect of the three-part-naming support [currently] is little more than an effect of an implicit CONNECT to the remote system?:

connect to RmtSys
;
select locallib.myudf('TEST123')
from tgtTblLib.tgtTbl
; -- because locallib and locallib.myudf are not on RmtSys
-- then the sqlcode of -204 would be expected

Perhaps then, resolve that issue [both for the queries of the OP and the above query after the explicit CONNECT] by creating the User Defined Function (UDF) on the remote system? e.g. the following script:

connect to RmtSys
;
create function tgtUDFlib.MYUDF ( vc varchar(n) )
returns ...
;

Afterward, perhaps the following modification to the original queries would be fruitful?:

select tgtUDFlib.myudf('TEST123')
from locallib.myalias

select tgtUDFlib.myudf(mydata)
from locallib.myalias