Re: SQL Functions "disappeared"

On 04 Mar 2013 12:56, Mike Krebs wrote:

<<SNIP>>
We have "lost" several User Defined Functions.
The error is FUNCTION in LIBRARY type *SRVPGM not found.

And that message is an SQL0204? Is the failing invocation LIBRARY.FUNCTION()? or is LIBRARY the authorization identifier?

User-defined function DEC3 cannot be invoked.
DEC3 in V84FILES# type *SRVPGM not found.

Are we to infer that the *SRVPGM does exist? Before a DROP\re-CREATE the results of a PRTSQLINF against an existing *SRVPGM after that message is issued might be interesting. Is the former sentence an SQL message preceding a -204? The latter wording [of an apparent -204] seems to imply the routine definition exists, but the *SRVPGM does not. However that may just be a consequence of the generic wording of the message. Yet, the sql0204 does suggest that "If a function was not found, &1 is the service program that contains the function."

The variant character in the name shown is suspect. The QSQJRN in QSYS2 logs the activity to the SQL Catalog Tables there for the current IPL by default; that journal environment could be modified. It may be possible that the code point of that character could be compared for the old routine definition in the journal image and the new row [image] after the routine was re-created. IIRC there were changes to add UNICODE support for those SQL catalogs like with the *DBXREF; not sure if, nor how\when those would be done with the noted PTF activity. Seems that would have been at an upgrade to IBM i 7.1 according to the MTU [where SYSROUINE is documented as changing and where a "New view QSYS2/SYSROUTINES_PREV (system name SYSROUTINP) matches the pre-7.1 definition of SYSROUTINE."]:
http://www-912.ibm.com/s_dir/sline003.NSF/7f02fa4a8460c21f86256c450052b184/32745592b51aaa608625770500718012?OpenDocument

Prior to such a change, the CCSID of the catalog had been tagged according to the primary language, yet the characters were stored as code points rather than as the specific character originating from the client [local\5250 or otherwise], and thus changes to the CCSID of the column could map the data in unexpected ways for variant characters like #. Similarly anyone referring to the function name via a feature that treated the name as a character string, would need to locate based on the stored value irrespective of encoding, yet in order to find the matching name the string is translated; and this is primarily why the SQL documentation has a warning stating that variant characters in identifiers are strongly discouraged.

Retrieving them using System i Navigator

That seems to imply that the routine definition exists; i.e. can be selected in iNav to be retrieved.? Or does /retrieve/ merely imply that a previously saved source is run?

and recreating (yes we have to DROP the non-existent function!)
without any changes fixes the problem.

There is an ALTER FUNCTION, but I am not sure what is minimally required to be specified. Maybe an ALTER FUNCTION DEC3 without any specifications would also fix the issue.?

So far, the ones we had to recreate are straight SQL types.

Does that mean each was of type FUNCTION (SQL Scalar) and FUNCTION (SQL Table), or does that mean each one implemented a SQL DISTINCT TYPE?

From prior comments... Perhaps only those with variant characters in the name had the problem?