× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.