On 04 Mar 2013 12:56, Mike Krebs wrote:
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."]:
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?