AW: SQL *SYSTEM naming default schema rule? -- MIDRANGE-L

For more information about SQL and System Naming, you may check the
following articles:
1. SQL versus System Naming - Object authority and privileges for SQL
database objects
http://www.ibm.com/developerworks/ibmi/library/i-sqlnaming/index.html
2. SQL versus System Naming - Accessing database objects
http://www.ibm.com/developerworks/ibmi/library/i-system_sql2/index.html

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von
Birgitta Hauser
Gesendet: Saturday, 21.5 2016 10:03
An: 'Midrange Systems Technical Discussion'
Betreff: AW: SQL *SYSTEM naming default schema rule?

Hi Buck,

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

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"

-----Ursprüngliche Nachricht-----
Von: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von Buck
Calabro
Gesendet: Friday, 20.5 2016 21:33
An: midrange-l@xxxxxxxxxxxx
Betreff: SQL *SYSTEM naming default schema rule?

I have a situation on 7.2 where I want to create a view without qualifying
the name. That is, I want to use the library list. I've read the
documentation at
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzqualun1.htm
which says 'For system naming, the default schema is the job library list
(*LIBL).'

I don't know what that means.

cl: chglibl (libsql1 libsql2);

-- this goes into QGPL
create table table_master
(id int,
text char(25));

-- this goes into LIBSQL1
create view view_zero
(id, text)
as (select id, text from table_master);

-- make a child table in a different library in *LIBL create table
libsql2.table_child2
(id int,
text char(25));

-- this goes into LIBSQL2
create view view_two_subselect
(m_id, m_text, c_id, c_text)
as (select m.id, m.text, m.foreign_key, (select text from table_child2)
from table_master m);

Three separate CREATE statements, three separate destinations. Is there a
reference for what is supposed to happen?

This came up because a colleague is convinced that CREATE VIEW with an
unqualified view name will put the new view into the first library in the
library list. He determined this empirically some years ago, and got
surprised today that his script put a new view farther down in the *LIBL.

Full example script at http://code.midrange.com/c28c3aef48.html

--
--buck

Visit wiki.midrange.com and register for an account. Edit a page that helps
you, and because it's public, you'll help someone else, too!

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at http://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxx for any subscription related questions.