Re: SQL question on default *LIBL at connection

Lowary, Jim wrote:

We are trying to set up a method were we can use different
libraries for
testing by changing the connection string/user for different environments development/qua/production etc for our .net
developers so we can leave the data on the iSeries.

<<SNIP>>

To call the stored procedure we have to use SDIPoint/GetAgent. We get the following error:
IBM.Data.DB2.iSeries.iDB2SQLErrorException: SQL0204
"AGTHDR in DATADN type *FILE not found."

The owner of the stored procedure GetAgent is DATADN and there is a library DATADN but it does not have this file in it. If we change the owner of the stored procedure to a user that
does not have a library with the same name, then it tries to
find a library with the name of the
user profile that is the new owner.

So it seems for non-qualified access with SQL naming
conventions, that this is the default and it does not search
the library list?
Anyway around this so it will search the *LIBL?

Thanks for any help or suggestions you might have.

What happens after DROP PROCEDURE and then while *SYSTEM naming is active, CREATE PROCEDURE is done to recreate the routine.? Does that solve it? I am too far removed from actually using this stuff, but I do recall that the SET OPTION NAMING is not allowed for CREATE PROCEDURE :-( The lack of that SET OPTION might suggest that a called procedure would always inherit the /naming/ option for the SQL environment already active... but the connection string explicitly requesting /system naming/ would then have prevented the issue. So for that contradiction & my lack of recollection, I infer the NAMING must be set for each procedure, but only from the environment of the /compile-time/, not from either of SET OPTION nor /run-time/. If the naming is an attribute of the procedure, it seems odd both that SET OPTION NAMING is not allowed and that it is not an attribute maintained\presented in SYSFUNCS like with PATH.

Regards, Chuck