Re: SQL *SYSTEM naming default schema rule?

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.