Re: SQL Functions "disappeared"

A recreate did fix it. We're on 7.1 with TR6 "staged" to be applied
tonight (What? Nothing else to do on a Friday night?)

The escape message from the joblog:

CPF426A Escape 50 03/07/13 16:45:23.827186 QQQUDFHL
QSYS *STMT QSQRUN3 QSYS *STMT
From module . . . . . . . . :
QQQUDFHL
From procedure . . . . . . :
SIGNALUDFERROR
Statement . . . . . . . . . : 8801

To module . . . . . . . . . :
QSQOPEN
To procedure . . . . . . . :
FULL_OPEN
Statement . . . . . . . . . : 36556

Message . . . . : User-defined
function ISOTODEC cannot be invoked.
Cause . . . . . : External program
or service program ISOTODEC in library
DILGARD associated with user-defined
function ISOTODEC in library DILGARD
cannot be invoked for reason code 2.
The reason codes and their meanings
follow: 1 -- The program is not
found. 2 -- Either the service program is
not found or the program entry point
does not exist. 3 --
The user is not authorized to the
program or service program. 4 -- The
program is not an ILE program. 5 --
The program or service program specifies
ACTGRP(*NEW). 6 -- The class and
method specified by the Java external name
do not exist. 7 -- The

user is not authorized to the class file or jar file specified by the Java

external name. 8 -- The program had an exception (such as pointer not set).

See previous messages. Recovery . . . : Refer to the DB2 for i5/OS SQL

programming topic collection in the Database category in the IBM i

Information Center book, http://www.ibm.com/systems/i/infocenter/ for more

information on user-defined functions. The following action may be taken

based on the reason code: 1 -- Ensure the program exists. 2 -- Ensure the

service program and program entry point exist. 3 -- Get the necessary

authority to the program or service program from either the security
officer
or the owner. 4 -- Replace the program with an ILE program. 5 -- Recompile

the program or service program with ACTGRP(*CALLER). Once the action above

has been taken, try the operation again. 6 -- Ensure that the Java class
and
method exist. 7 -- Obtain the necessary authority to the class file or jar

file from either the security officer or the owner. 8 -- Try recreating the

function with Parameter Style General, then try the operation again.

(Parameter Style General should be used for value returning functions.)

It is followed in the joblog by diagnostic SQL0204 that the service program
was not found.



On Fri, Mar 8, 2013 at 9:30 AM, CRPence <CRPbottle@xxxxxxxxx> wrote:

On 08 Mar 2013 04:37, Jeff Crosby wrote:

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.

Perhaps clarify "this" with the spooled joblog showing the full
context of the error(s)? Presumably the -204 and preceding diagnostic.
Also if the environment is IBM i 7.1 TR#.

A simple recreate will fix it?

Seems so. I suggested perhaps if the ALTER syntax allows, then maybe
just an ALTER PROCEDURE without any requested /alterations/ in the
statement could resolve.

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

Did you see my reply about some APARs? There is one with symptom
string keywords that could potentially match with whatever is the
problem, if the symptoms of the specific issue were known.
Unfortunately there is no PTF [yet]. Regardless, this issue seems like
something to pursue as defect with the service provider.

--
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.