On Fri 04-Apr-2011 17:14 , James Lampert wrote:
I wrote:
I just spent the afternoon bypassing the whole "pass the library to
the UDF" thing by way of a strategy that requires the UDF calls in
a VIEW definition to be library-qualified.
Now, I'm getting error messages to the effect of "Column FOO not in
specified tables," where FOO is the library where the specific
instance of my DISPLAYVAL UDF lives, and with which I'm qualifying
the calls thereto, e.g., FOO/DISPLAYVAL(9106, BAR).
Have I just wasted my afternoon doing something not permitted in an
SQL SELECT?
Although I did not recall specifically the SQL0206 as effect, I do
recall never being able to use the slash for *SYS [system] naming for
invoking a function. That is what I recall for both DML and DDL; see
"UDF Concepts" doc reference later in this reply, mentioning only DML.
When both using system naming and the function was not in *LIBL, I
recall always having to SET PATH to include the desired library or
libraries which held the referenced functions, and then using *only*
unqualified function references. I am not aware of any changes from
what I experienced; surely the last I tried was on v5r4, and perhaps but
less likely, on v6r1.
Or is the UDF guaranteed to be the one found at view creation time,
making qualification unnecessary so long as the "right" one was the
first one that RUNSQLSTM found when it created the view?
In which case, only the hour I spent painstakingly qualifying the UDF
calls would have been wasted?
IIRC the function reference is "bound" at creation time. After the
CREATE VIEW, review the catalog and DSPFD for the VIEW definition.
Notice how the unqualified UDF reference is now qualified [in the *SQL
naming, with the period versus slash] in the statement text. While the
DSPFFD does not show the qualified function name in the Derived field
TEXT(), the DSPFD does, and I believe that is what is used to create the
file on restore. I was once warned that a user-defined function named
the same as a Built-in function could have a nuanced behavior for
restore of a VIEW with an unqualified reference, since those functions
are resolved again during the "create" that is performed for the
"restore" of a database *FILE; something I never verified, and I guess
would be an issue only if that same-named function was ahead of QSYS2
when RSTOBJ or RSTLIB was being performed.? I believe the restored file
either with the explicitly qualified name or the since qualified name
that became part of the VIEW definition, demands that the UDF [with
compatible parameters] be in that same library shown in the VIEW
definition. Since the "retrieved" DDL for the CREATE VIEW from either
the API [e.g. via iNav database] or from the catalog VIEW_DEFINITION of
SYSVIEWS would demand that, that the create for restore would operate
the same seems logical. However...
I can find no explicitly stated outcome for save\restore of a VIEW
with unqualified function references. While the VIEW could have saved
the PATH and reference that for restore-time create, that the qualified
name is stored\saved in the VIEW definition\SELECT-statement would seem
to imply that the name became implicitly qualified due to...
SQL Language elements - Functions
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstch2function.htm
"In a CREATE VIEW statement, function resolution occurs at the time the
view is created."
UDF Concepts
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/sqlp/rbafyudfduvc.htm
Full name of a function
The full name of a function using *SQL naming is
<schema-name>.<function-name>.
The full name of a function in *SYS naming is
<schema-name>/<function-name>. Function names cannot be qualified using
*SYS naming in DML statements.
You can use this full name anywhere you refer to a function. For example:
QGPL.SNOWBLOWER_SIZE SMITH.FOO QSYS2.SUBSTR QSYS2.FLOOR
However, you may also omit the <schema-name>., in which case, DB2 must
determine the function to which you are referring. For example:
SNOWBLOWER_SIZE FOO SUBSTR FLOOR
Path
The concept of path is central to DB2's resolution of unqualified
references that occur when schema-name is not specified. The path is an
ordered list of schema names that is used for resolving unqualified
references to UDFs and UDTs. In cases where a function reference matches
a function in more than one schema in the path, the order of the schemas
in the path is used to resolve this match. The path is established by
means of the SQLPATH option on the precompile commands for static SQL.
The path is set by the SET PATH statement for dynamic SQL. When the
first SQL statement that runs in an activation group runs with SQL
naming, the path has the following default value:
"QSYS","QSYS2","<ID>"
This applies to both static and dynamic SQL, where <ID> represents the
current statement authorization ID.
When the first SQL statement in an activation group runs with system
naming, the default path is *LIBL.
Regards, Chuck
midrange.com
