Spent way more time on this than what it is worth!
Bottom-line...simple recreate will fix.
But I got curious and started looking around to try to resolve Chuck's questions and see if there is something important going on. I don't think so as very simple recreate fixes any problems we have. Although whywe would have to do that is beyond me.
I found a copy of the DEC3 service program in a save file from Feb 28. The original DEC3 service program was created on a different box at V5R4. It has Storage model: *SNGLVL . The new one has storage model *INHERIT. The other UDFs that still work all say *INHERIT (but they have all been recreated). Are *SNGLVL UDFs no longer valid within SQL? That doesn't seem to be the case as I just tested a *SNGLVL UDF that still works although it is an external (RPGLE) based UDF. I do have one SQL UDF that doesn't work. But it is not used in production so hard to know when it last worked.
To the questions...*************************
The journey started when a CLP that used RUNSQLSTMT blew with SQL9010. The results of the investigation showed two "lower level" messages CPF426A and SQL0204.
The "User-defined function DEC3..." is CPF426A. In the Cause section, reason code 2 is called out.
2 -- Either the service program is not found or the program entry point
does not exist.
Then blank line above represents 128 reverse image blanks. I think that means there is garbage in the sixth substitution variable.
SQL0204 is the "DEC3 in V84FILES# type *SRVPGM not found".
I inferred that the *SRVPGM did not exist, but was unsure where it went. There are differences between the old service program and the new service program from dspsrvpgm. Some of them I have listed below (mix of old and new but the categories are the issue):
Observable information compressed . . . . . . . : *NO
Storage model . . . . . . . . . . . . . . . . . : *INHERIT
Release service program created on . . . . . . . : V7R1M0
Release service program created for . . . . . . : V5R4M0
Earliest release service program can run . . . . : V5R4M0
Number of service programs:
Current . . . . . . . . . . . . . . . . . . . : 3
Number of program procedure exports:
Current . . . . . . . . . . . . . . . . . . . : 3
Number of program data exports:
Current . . . . . . . . . . . . . . . . . . . : 17
DEC3 QTEMP CLE
Program Library Activation Signature
QSQROUTQ QSYS *IMMED D8E2D8D9D6E4E3D84040404040404040
Beyond size and signature differences, the number of exports and the QSQROUTQ program being added in the new one, the only real change is the storage model.
QSQJRN generated a 819,000 page spool! I found some DEC3 stuff including something that looks like it is referring to the original definition (had user profile of creator in the text) but have no idea how to decipher the differences. Must be a layout for QSQJRN somewhere but not worth the trouble for me to figure it out.
The function was recreated using the retrieved SQL. Yes, the function still existed. The ones we have recreated so far have all been in V84FILES#. I tested several other UDFs (both SQL and external) in that library and they work correctly (save the EJPR one) so not sure how these are different from those others.
This is the generated SQL that fixed the problem. I did have to put a DROP statement in there but I didn't save it. I imagine the drop looked like:
drop function v84files#.dec3;
-- Generate SQL
-- Version:
V7R1M0 100423
-- Generated on: 03/04/13 17:17:56
-- Relational Database: S062668R
-- Standards Option: DB2 for i
SET PATH *LIBL ;
CREATE FUNCTION V84FILES#.DEC3 (
IN_NUMBER NUMERIC(30, 6) )
RETURNS NUMERIC(11, 3)
LANGUAGE SQL
SPECIFIC V84FILES#.DEC3
DETERMINISTIC
CONTAINS SQL
CALLED ON NULL INPUT
NO EXTERNAL ACTION
SET OPTION ALWBLK = *ALLREAD ,
ALWCPYDTA = *YES ,
COMMIT = *NONE ,
DECRESULT = (31, 31, 00) ,
DFTRDBCOL = *NONE ,
DLYPRP = *NO ,
DYNDFTCOL = *NO ,
DYNUSRPRF = *USER ,
SRTSEQ = *HEX
RETURN ROUND ( COALESCE ( IN_NUMBER , 0 ) , 3 ) ;
GRANT ALTER , EXECUTE
ON SPECIFIC FUNCTION V84FILES#.DEC3
TO GRPA ;
The other two UDFs that had to be recreated were like this one with various rounding on them.
The library name with the variant in it doesn't seem to be a problem. The CCSID shows 37 on both old and new and therefore should have translated correctly and consistently. And a few UDFs created before this issue came up continue to work fine.
PRTSQLINF differences only - this is between what was essentially a test version versus a production version (that we had to recreate within the last two weeks). Problem for me with the test version is that I don't know when it last worked correctly.
Object type...............*SRVPGM Object type...............*SRVPGM
OBJ(V84FILES#/EJPR) OBJ(QTEMP/DEC0)
SRCMBR(EJPR) SRCMBR(DEC0)
TGTRLS(V5R4M0) TGTRLS(V7R1M0)
CLOSQLCSR(*ENDACTGRP) CLOSQLCSR(*ENDMOD)
SQLPKG(V84FILES#/EJPR) SQLPKG(V84FILES#/DEC0)
TEXT('SQL FUNCTION EJPR ') TEXT('SQL FUNCTION DEC0 ')
SET : H : H = ROUND ( COALESCE ( : H : H , 0 ) , 0 ) SET : H : H = ( ROUND ( COALESCE ( : H : H , 0 ) , 0 ) )
SIGNAL SQLSTATE '2F005' SET MESSAGE_TEXT = : H SET : H : H = ( VALUES ( ROUND ( COALESCE ( : H : H , 0 ) , 0 ) ) )
GET DIAGNOSTICS EXCEPTION 1 : H = MESSAGE_LENGTH , : H = MESSAGE_TEXT
Using generate SQL on working and non-working version comes up with essentially the same. Name change and authority changes are the only differences.
________________________________
From: CRPence <CRPbottle@xxxxxxxxx>
To: midrange-l@xxxxxxxxxxxx
Sent: Monday, March 4, 2013 4:30 PM
Subject: 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?
midrange.com
