Re: SQL UDF...why do i need to qualify the UDF and the table being used?

On 25 Apr 2013 13:50, Needles,Stephen J wrote:

I deleted and re-created everything, meticulously following what you
had described. And it works!

The weird thing is...I thought that that was what I'd done in the
first place! Before I started going through all of the gyrations
with changing o *SQL and everything.

I have had many similar bang-my-head moments with regard to PATH and CURRENT SCHEMA. But usually after a request to PRTSQLINF and\or a SELECT CURRENT SCHEMA, CURRENT PATH FROM SYSIBM.SYSDUMMY1 [and on older releases, adjusting between the period and a slash based on the naming rule in effect], I am usually able to see the problem... even if not also see how I got there.

For example just recently, I had a prior SQL application that ran in my job, but the request had failed. That failed request somehow caused my interactive SQL session to adopt the CURRENT SCHEMA set by that prior failing application, instead of using the current schema that should have been defaulted [to *LIBL] and remained established in my *existing* session that was selected for my request to STRSQL.

I also seem to recall that [on v5r3] the request to SET CURRENT SCHEMA DEFAULT does not properly revert that register to *LIBL when running with NAMING(*SYS) within a RUNSQLSTM script, although it works as expected within STRSQL. That is a very frustrating anomaly [apparent defect], because then a single script could not create multiple routines with different defaults for both where the objects created for an unqualified CREATE statement will reside and where unqualified table-references are located.

I wonder what stray thought lead me down the wrong path.

Just a guess, but probably having [accidentally] used an environment that was started with NAMING(*SQL). The natural consequence of which, and thus the conclusions that "to get it to work, I needed to qualify the table" and "had to change over to *SQL rather than *SYS for naming conventions and had to qualify the UDF" both would make sense.... *if* "library" was not the equivalent of the "authorization id" and where "library" was the qualifier used that /resolved/ the difficulties.

Having a current library with the same name as my user profile name made it easy for me to accidentally create objects using *SQL naming without any problems, only to find later that I had not been creating with *SYS naming as I had presumed to be the case; i.e. the effects were not conspicuous because my current library name matched the authorization identifier. Now that the system I use has a different library name than my user profile name, the effects of accidentally using *SQL naming and unqualified names is diagnosed swiftly and clearly :-) As long as a library name matching my user profile never exists or if such a library does exist but is not authorized to me, then the possibility of making that mistake again is almost nil.

However the speculation is moot I suppose, since the issue is resolved :-)