Re: SQL Functions "disappeared"

This just happened to us last night. A stored procedure that is used once
a month was "not found." But it's there, and has been there, for many
years.

A simple recreate will fix it?

I thought there was another thread that had more about it, but I can't seem
to find it now.



On Tue, Mar 5, 2013 at 6:59 PM, Mike Krebs <majkrebs@xxxxxxxxx> wrote:

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?

--
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.