SQL adopted authority problem.

["Smith, Dave" <dsmith@xxxxxxxxx>]

I have a complicated SQL issue that I'm working on that I hoe somebody
can shed some light on.   We are currently working with IBM and I am not
happy with their level of expertise.

 

Sorry for the long post.....

 

Program A *SQLRPGLE executes the following SQL to call PROCEDUREB

 

0205.00 C/EXEC SQL                         

0205.03 C+ CALL PROCEDUREB (:wWhs)

0205.12 C/END-EXEC                         

 

ProcedureB was created using:

 

CREATE PROCEDURE LIB/PROCEDUREB ( 

            IN WAREHOUSE DECIMAL(3, 0) ) 

            LANGUAGE SQL 

            SPECIFIC LIB/PROCBP 

            NOT DETERMINISTIC 

            MODIFIES SQL DATA 

            CALLED ON NULL INPUT 

            SET OPTION DYNUSRPRF = *OWNER

BEGIN 

...

 

ProcedureB uses SQL UDF FUNCTIONC

 

Select 

            FunctionC(VARCHAR(IMS#,8), Double(IMWH#),VarChar(''W'',1))

            ...

 

FUNCTIONC was created using:

 

CREATE FUNCTION LIB/FUNCTIONC ( 

            PRD# VARCHAR(8) , 

            WHS# DOUBLE PRECISION , 

            CODE VARCHAR(1) ) 

            RETURNS DECIMAL(15, 5)   

            LANGUAGE SQL 

            SPECIFIC FNC00005 

            NOT DETERMINISTIC 

            CONTAINS SQL 

            CALLED ON NULL INPUT 

            DISALLOW PARALLEL 

            NOT FENCED

            BEGIN

...       

 

FUNCTIONC calls RPGLE program D.    I've set this up both as an external
call and as a direct call from the SQL with the same result.

 

To summarize:

            SQLRPGLE A 

                        SQL PROCEDUREB

                                    SQL FUNCTIONC

                                                RPGLE D

 

Now the problem:

 

            When RPGLE D is executed, I've lost my adopted authority and
the user is not authorized to the files used in the RPGLE program.

 

I've tried creating the function as both fenced and not fenced.

I've set the authority correctly on all the CLE programs created via the
procedure and function creation.

 

This process works great for me because I am authorized to the files.
For the typical user who relies on adopted authority it blows on that
last RPGLE program.

 

I hope all that made sense ....Help?

 

Dave S.