Re: SQL UDF in RPG (PDQ?)

[CRPence <CRPbottle@xxxxxxxxx>]

On 20-Apr-2011 16:56 , James Lampert wrote:
> CRPence wrote:
> > Try this modification to direct to the external procedure in the
> > named *SRVPGM:
> >
> > EXTERNAL NAME 'WTDEVELOP/CENSORACCT(Procedure_Name)'
>
> That unscrewed me. After fixing a few other things, it
> works beautifully.
>
> Once the UDF was working and registered, I had to tear down and
> recreate the VIEW that uses it to censor the data.
>
> Question: Now that the UDF is registered on this ILE RPG SRVPGM,
> what happens if I have to modify the SRVPGM? Do I have to DROP and
> reCREATE the UDF and the VIEW?

  Since v6r1 the ALTER FUNCTION statement is available; I do not recall 
ever using that statement.  This UDF was "external scalar" and the 
syntax for that is found at the following link; separate links are 
available for SQL scalar, and both external and SQL TABLE functions:
http://publib.boulder.ibm.com/infocenter/iseries/v6r1m0/topic/db2/rbafzafsce.htm

  To get the SQL information associated with [i.e. added to or replaced 
in] the *SRVPGM, re-register the UDF either by DROP FUNCTION and then 
CREATE FUNCTION or by ALTER FUNCTION which presumably should suffice 
[when available].  Otherwise the "function resolution" for the reference 
in the VIEW should work just as before the changes; I do not recall ever 
having to recreate a VIEW unless the UDF would no longer function such 
as with interface changes.  Both the DROP and ALTER are enabled by 
default, though can be prevented if the RESTRICT clause is specified and 
the UDF is still referenced in the VIEW [or wherever usage of the UDF is 
tracked]; use the RESTRICT option to be sure deprecated references are 
removed before the DROP or ALTER.

Regards, Chuck