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