× 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 5/21/2016 4:03 AM, Birgitta Hauser wrote:

When creating a view with System naming conventions, all unqualified
specified tables and views used in the SELELECT statement are resolved (at
compile time) from the current library.
If the view itself is specified unqualified, it is created within the QGPL
library.

When using SQL naming conventions, all unqualified specified tables and
views used in the SELECT statement are resolved from the CURRENT or DEFAULT
schema. If the current schema is not explicitly set with SET SCHEMA, the
schema with the same name as the SESSION_USER is used.
If the view itself is specified unqualified it is create with in the
CURRENT/DEFAULT schema

Yes, this is how DB2 finds tables & views referenced in CREATE VIEW.

I was trying to put the new view into a library without specifying which
library.

The business problem is to create several views in multiple
environments. DEV, QA, PROD. The normal way I do that is to use SQL
naming, SET SCHEMA at the top of the script, and run the script. Then
change the SET SCHEMA to the next library and re-run the script.

A colleague told me that if I were to use SYSTEM naming, the script need
not mention a library at all, that CREATE VIEW puts the view in the
first library on the library list. I thought that was crazy, but it
seemed to work! And for simple projections, it does.

When it came to a more complex view, the view got created in a lower
library in the library list, and so I started looking to find the rule
in the documentation that explains where an unqualified CREATE VIEW gets
put.

As Charles points out, the documentation for CREATE VIEW describes the
target schema as the schema of the first table that is physically
referenced in the SQL statement. So if I have

create view new (...) as
(select... from {libr1/}mytable)

the rules say 'find the first table reference. Here, it is mytable.
What library is it in? Here, it is in libr1. DB2 will put the new view
in libr1.

If I have

create view new (...) as
(with cte as (select ... from {libr2.}mychild)
select... from {libr1/}mytable)

the rules say 'find the first table reference. Here, it is mychild.
What library is mychild in? Here, it is in libr2. DB2 will put the new
view into libr2.

As Chuck notes, the documentation seems more for resolving references to
tables and views, not to explain /where to put/ the newly created view.


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.