Announcement

**Scott Klement** · June 14, 2019, 10:12 AM

The issue is not related to activation groups.

When SQL calls a subprocedure, it does not set the parameter count, so you cannot use %PARMS to check if a parameter wasn't passed. The parameter count in ILE is passed as part of what is called a "minimal operational descriptor", which SQL does not offer support for, so there's no way to know if a parameter wasn't passed.

When you try to refer to a parameter that wasn't passed, ILE programs (including RPG) will refer to whatever happens to be in memory where the parameter would have been if it were passed. So it refers to a memory location that wasn't set by SQL -- and its hard to predict what might be there. It might be a valid value for your variable, or it might not be. The parameter's address (the pointer used to pass it) might be a valid address, and it might not.

Changing the activation group doesn't really fix anything. It just changes things around a little bit so that you're accessing a different part of memory. It's still unpredictable what will be there, but since it's a different space, you might in your testing get a different response. It's still not valid to use it, and if if it seems like it's fixing the problem now, your program could still fail.

Valid solutions that come to mind would be:

1. Call a program instead of a subprocedure. This causes %PARMS to be set reliably.

2. Use wrappers when calling from SQL. This is a simple subprocedure with fixed parameters, none are optional, that in turn calls the procedure with the optional parameters. Since this wrapper can be written in an ILE language that supports the parameter count, the optional parameters can be passed safely from the wrapper code.

**jj_dahlheimer** · June 14, 2019, 10:37 AM

Sorry, should have included this.

The UDTF is calling the subprocedure in service program A with fixed parameters. Subprocedure from service program A is calling a subprocedure in service program B while not passing all parameters and that is where is issue is.

Here are some definitions if it helps.

Code:

//UDTF
CREATE FUNCTION SWANSON/ORDERHEADER ( 
    COMPANY DECIMAL(3, 0) , 
    ORDER# DECIMAL(8, 0) , 
    SHIPTO# DECIMAL(3, 0) ) 
    RETURNS TABLE ( 
    ALTERNATEORDER# VARCHAR(30) , 
    WAREHOUSE DECIMAL(3, 0) , 
    SHIPVIA DECIMAL(2, 0) , 
    SHIPDATE DATE , 
    CONSIGNEECOMPANY VARCHAR(30) , 
    CONSIGNEECONTACT VARCHAR(50) , 
    ADDRESS1 VARCHAR(32) , 
    APARTMENT VARCHAR(10) , 
    ADDRESS2 VARCHAR(32) , 
    ADDRESS3 VARCHAR(32) , 
    ADDRESS4 VARCHAR(32) , 
    CITY VARCHAR(25) , 
    STATE VARCHAR(2) , 
    POSTALCODE VARCHAR(10) , 
    COUNTRY VARCHAR(3) , 
    PHONE# VARCHAR(14) , 
    RESIDENTIAL CHAR(1) , 
    EMAIL VARCHAR(50) , 
    DIMENSIONS VARCHAR(11) , 
    ESTIMATEDWEIGHT DECIMAL(7, 3) )   
    LANGUAGE RPGLE 
    SPECIFIC SWANSON/ORDERHEADER 
    NOT DETERMINISTIC 
    READS SQL DATA 
    CALLED ON NULL INPUT 
    EXTERNAL NAME 'SWANSON/SHPUDTFR01(ORDERHEADER)' 
    PARAMETER STYLE DB2SQL ;

select *                                            
from table(orderheader(1,69678604,1)) as orderheader

//procedure in service program A
Dcl-Proc OrderHeader export;
  Dcl-Pi OrderHeader;
    company packed(3) Const;
    order# packed(8) Const;
    shipTo# packed(3) Const;
    alternateOrder# varChar(30);
    warehouse packed(3);
    shipVia packed(2);
    shipDate date;
    consigneeCompany varchar(30);
    consigneeContact varchar(50);
    address1 varchar(32);
    apartment varchar(10);
    address2 varchar(32);
    address3 varchar(32);
    address4 varchar(32);
    city varchar(25);
    state varchar(2);
    postalCode varchar(10);
    country varchar(3);
    phone# varchar(14);
    residential char(1);
    email varchar(50);
    dimensions varchar(11);
    estimatedWeight packed(7:3);
    n_company int(5) Const;
    n_order# int(5) Const;
    n_shipTo# int(5) Const;
    n_warehouse int(5);
    n_shipVia int(5);
    n_alternateOrder# int(5);
    n_shipDate int(5);
    n_consigneeCompany int(5);
    n_consigneeName int(5);
    n_address1 int(5);
    n_apartment int(5);
    n_address2 int(5);
    n_address3 int(5);
    n_address4 int(5);
    n_city int(5);
    n_state int(5);
    n_postalCode int(5);
    n_country int(5);
    n_phone# int(5);
    n_residential int(5);
    n_email int(5);
    n_dimensions int(5);
    n_estimatedWeight int(5);
    SQLSTT char(5);
    Function varchar(517) const;
    Specific varchar(128) const;
    errorMsg varchar(70);
    CallType Int(10) const;
  End-Pi;

//procedure in service program A calls procedure in service program B

phone# = %Trim(OrderP_GetShipToPhone#(company:order#));

//procedure in service program B 

dcl-proc OrderP_GetShipToPhone#  export;
         dcl-pi *n char(14);
           company packed(3) const;
           order packed(8) const;
           shipTo# packed(3) Const Options(*noPass:*omit);
           type char(1) Const Options(*NoPass:*Omit);
         end-pi;

// type is sometimes '0'

**Scott Klement** · June 14, 2019, 01:11 PM

How is OrderP_GetShipToPhone# checking to see if the parameters are passed?

You say "type is sometimes '0'" but if its not passed, you should never be looking at it....

**jj_dahlheimer** · June 14, 2019, 01:35 PM

I think I found it, head on wall moment. I was passing type into another procedure and assume I have to use phoneType instead, still odd that it works perfect while calling from an rpg program but not using the UDTF.

Code:

dcl-proc OrderP_GetShipToPhone#  export;
         dcl-pi *n char(14);
           company packed(3) const;
           order packed(8) const;
           shipTo# packed(3) Const Options(*noPass:*omit);
           type char(1) Const Options(*NoPass:*Omit);
         end-pi;

         Dcl-S inShipTo# packed(3);
         Dcl-S phoneType char(1);

         If %parms > 2 and %Addr(shipTo#) <> *Null and shipTo# > 0;
           inShipTo# = shipTo#;
         Else;
           inShipTo# = 1;
         EndIf;


         If %parms > 3 and %Addr(type) <> *Null;
           phoneType = type;
         Else;
           phoneType = *Blanks;
         EndIf;

// other logic to get ship to phone #

// if it finishes without finding a ship to phone # tries to look up a sold to phone #
// Need to use phoneType instead of type
return OrderP_GetSoldToPhone#(company:order:type);

       end-proc;

dcl-proc OrderP_GetSoldToPhone# export;
         dcl-pi *n char(14);
           company packed(3) const;
           order packed(8) const;
           type char(1) Const Options(*NoPass:*Omit);
         end-pi;

         Dcl-S phoneType char(1);

         If %parms > 2 and %Addr(type) <> *Null;
           phoneType = type;
         Else;
           phoneType = *Blanks;
         EndIf;

//phoneType at this point is '0'

**Scott Klement** · June 14, 2019, 02:53 PM

Yep, looks like you found it.

Its just chance of what ever happens to be in that spot in memory.

**Barbara Morris** · June 17, 2019, 11:48 AM

There was a Db2 for i RFE about not passing the operational descriptor for calls from SQL. The RFE is "delivered".

The PTFs are
- 7.2 5770SS1 SI67232
- 7.3 5770SS1 SI67233

IBM Ideas

https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=106989

**Scott Klement** · June 18, 2019, 10:58 AM

Thanks, Barbara, I hadn't heard about that.

Announcement

UDTF activation group

UDTF activation group

Comment

Comment

Comment

Comment

Comment

Comment

Comment