RE: UDTF/Stored Procedure & Activation Groups

Doug,

So, if I understand correctly, you have:

- program P, that is called from
- service program S, that is called from
- program NEWSCAF3, that is
A: called by
- UDTF U that is called by Business Objects
B: used as
- stored procedure NEWSCAFCHG, that is called from Java
program J

Situation A errors out when P has activation group *NEW, but is OK when P
has a named activation group;
situation B is OK when P has activation group *NEW, but errors out when P
has a named activation group.

You have a path with lots of opportunities to trip :-) Parameter passing
between different worlds (Java, SQL, RPG) is always tricky.

But as I understand it, the call to the procedure in S seems to be
alright, so I would put S in debug (easy if you use RDP or WDSC; trickier
if you have to use 5250), observe how the parameters are passed and watch
what happens when S calls P.

Joep Beckeringh

rpg400-l-bounces@xxxxxxxxxxxx wrote on 27-07-2012 20:38:25:

Lewis, Doug

Joel,

The example is run sql in Ops Nav. I did this to test the stored
procedure NewScafChg which calls program NewScaf3. Normally it is
called from one of our Java apps.

The stored procedure runs a program that creates parm 7 & 8, which
is a date and time stamp, and returns it to the calling Java
program. These are part of the key fields to the file that program
NewScaf3 populates. Then Crystal Reports queries the file using the
timestamp and one other field.

The UDTF runs the same program NewScaf3 but it is called from
Business Objects.

The Stored Procedure:

-- Generate SQL
-- Version: V6R1M0 080215
-- Generated on: 07/27/12 08:49:54
-- Relational Database: ERPSERV
-- Standards Option: DB2 for i
CREATE PROCEDURE ERSDS.NEWSCAFCHG (
INOUT PARMRCID CHAR(19) ,
INOUT PARMEXTRA1 CHAR(19) ,
INOUT PARMEXTRA2 CHAR(19) ,
INOUT PARMEXTRA3 CHAR(19) ,
INOUT PARMFROMDATE CHAR(10) ,
INOUT PARMTODATE CHAR(10) ,
INOUT PARMCYYMMDD CHAR(7) ,
INOUT PARMHHMMSSMMM CHAR(9) )
LANGUAGE RPGLE
SPECIFIC ERSDS.NEWSCAFCHG
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'ERSDS/NEWSCAF3'
PARAMETER STYLE GENERAL ;

COMMENT ON SPECIFIC PROCEDURE ERSDS.NEWSCAFCHG
IS 'Extract eRSDS Billing Scaffold Charges & Daily Rental & Cost To

Date' ;

Hope that helps explain it a little more.

Doug