Re: SQL question on default *LIBL at connection

Lowary, Jim wrote:

<<SNIP>>

Now when they can call the stored procedure and if it doesn't use
derived parameters it works! However if they try and do a call
using DeriveParameters then they get this message:

"Test method SDI.Point.Admin.Test.AgentManagerTest.GetByCodeTest
threw exception: System.ArgumentException: The stored procedure
cannot be found in SYSPROCS, or the procedure name is
ambiguous.."

What is seems like (again I'm only guessing) is that it is
finding every instance of that stored procedure in the *LIBL and
then trying to find a match base on parms and finding more than
one procedure that fits the bill. Rather than taking the first
one it finds in the *LIBL.

This may be the way it works as it is setup using *SYS, but I
would have expected it to work much like an iSeries find for a
program; searching each library in order of the *LIBL. This way
you could have multiples and have your *LIBL structured like:
Development/QUA/Production, allowing CMS to handle moving the
objects to the next level as it passes testing, and only need
those objects that are changed in the Dev/QUA libraries.

I guess this is my question; is this working as designed (with
the DeriveParameters)? or is there some other setup issue that
could be changed to make it work more like what we are use to
with *LIBL and finding the object to run.

The SQL function resolution is not the same as locating a program in a dynamic call. First, it uses the SQL PATH, not simply *LIBL. Second, there is /overloading/ for routines. Third, it is not just /resolve system pointer/ MI instruction, but /routine resolution/ code which references the data in SYSROUTINE in QSYS2. Then because overloading is possible, a /best fit/ may be required. Thus as I understand, if the first of multiple matches for the name is not an exact match [for parameters], resolution must defer to /best fit/. If there is a UDT, then the type must be explicitly cast, else the use may be ambiguous. Additionally DeriveParameters() will not work for overloaded stored procedures according to [.pdf on web]:

Table 37. IBM OLE DB .NET Data Provider restrictions
:Class or feature : OleDbCommandBuilder.DeriveParameters
:Affected DB2 : All
:Restriction description : method will fail for overloaded stored procedures... multiple stored procedures of the name "MYPROC"... method will retrieve all of the parameters for all overloaded SP.

Regards, Chuck