× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



The new default on IBM i 7.1 is *INHERIT per the MTU; and suggests a re-create [or CHGSRVPGM or CHGPGM for procedures] can be done to adjust that Storage Model setting on the SQL routines. Thus after re-creating the function on 7.1, that new STGMDL value is a side effect.

FWiW: There was a past defect for /special characters/ [aka variant characters; explicit mention to the hash symbol # also often called pound] in the routine name, early in v7r1. APAR SE41727:
http://www-912.ibm.com/n_dir/nas4apar.nsf/c79815e083182fec862564c00079d117/f1d35f39fdda81f08625770c004b971e?OpenDocument

FWiW: It is not the CCSID for the [service] program that matters, but the CCSID of the catalog and the job\source from which the CREATE is run.

Whether the above is a side effect for the more recent APARs noted later... I am not sure. The first below has no PTF, and the second has a PTF that is not on a cumulative that may or may not have been on a DB group.

The specifics from a spooled joblog of the msgCPF426A RC2 were not given, so I can only guess the likely or possible relatedness to either of:

APAR SE54837: something about entry point missing in catalog, so this seems a good match for the &6 replacement data missing; as detected in the lab, so I infer, ¿the origin for the issue apparently was a system crash?
http://www.ibm.com/support/docview.wss?uid=nas2e26e874c01c4049886257b100042409f

APAR SE54153: /renamed/ routines; new support for RNMOBJ [RNMLIB] and MOVOBJ for routines intends to be recorded in the catalogs [I think another thing in the 7.1 MTU]
http://www.ibm.com/support/docview.wss?uid=nas24dc1c8b26619c7e286257aca004203fa

Regards, Chuck

On 05 Mar 2013 15:59, Mike Krebs 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 RUNSQLSTM 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 )
<<SNIP>>

Using generate SQL on working and non-working version comes up with
essentially the same. Name change and authority changes are the only
differences.


As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.