Re: SQL naming + library list

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.