Re: SQL naming + library list

[CRPence <CRPbottle@xxxxxxxxx>]

  Sorry about the late reply... making this thread re-appear two months 
later.

  AFaIK there *is* a "way to use a library list on a stored procedure 
CALL with SQL naming" in effect; it works for me on v5r3, for a purely 
*dynamic* CALL.  So at least in that environment, it is possible when 
changing from the qualified SQL request CALL library.sProc to the 
unqualified SQL request CALL sProc, to have the SQL invoke the 
*LIBL/sProc instead of the Authorization-ID/sProc being invoked by the 
SQL, regardless of the OPTION being NAMING=*SQL.  The requirements in 
that scenario are that the SQL PATH has been established as *LIBL and 
the CALL is dynamic vs embedded.  AFaIK, for an embedded CALL request, 
the PATH that was either specified on the compile request of the program 
or was in effect during the CREATE of the routine\program, will be the 
PATH used at run-time to locate the executable.  Given "[t]he SPROC is 
called from a Java web app", I infer the CALL is dynamic, and thus 
specifying the desired PATH should assist.  So...

  From the second linked article, was the SQL request to SET PATH = 
*LIBL tested to see if the effects were satisfactory to resolve the 
issue for the unqualified CALL of a stored procedure "even when the SQL 
naming" is in effect?  From that link:

"...
The SQL path can also be set or changed by executing the SET PATH statement.
 ...
 The special value, *LIBL, can be used to set the SQL path to the 
current library list, even when the SQL naming convention is used.
 ...

In the following example, the current path is first assigned to the 
current library list ...
 ...
SET PATH = *LIBL;
 ..."

Regards, Chuck

On 13 Feb 2013 06:01, Allen, Todd wrote:
> Thanks for the article links.  Those contain great info.  Most of
> the material I had read before.  One thing I did not know is that
> the owner of a schema will be the user profile if that schema name
> matches an existing user profile.
>
> To my original question, it seems there is no way to use a library
> list on a stored procedure call with SQL naming.
>
> Alan Seiden on Wednesday, February 13, 2013 1:54 AM wrote:
>
> > In some cases, the naming mode used to create the stored procedure
> > may supersede the manner in which the procedure is called.
> >
> > This topic is more complex than it seems. For details, I recommend
> > Birgitta Hauser's thorough, authoritative articles about SQL and
> > System naming:
> >
> > http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html
> >
> > http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html
> >
> > On 11 Feb 2013 05:55, Allen, Todd wrote:
> >
> > > The SPROC is called from a Java web app. The call is currently
> > > qualified as library.sproc. Leaving out the library defaults to
> > > the user profile name as the library. That is the expected
> > > behavior. However, I remember reading about a way around this by
> > > using an alias but cannot recall the details.
> > >
> > > Allen, Todd on February 8, 2013 4:09 PM wrote:
> > >
> > > > Is there any way to use SQL naming in an unqualified (no
> > > > library) call to a stored procedure and have the library list
> > > > used? I seem to recall that an alias can be used to mimic the
> > > > library list behavior when using SQL naming.
> > > >
> > > > I know system naming would be much easier but that is not an
> > > > option at this time.