On 22-May-2016 06:23 -0500, Birgitta Hauser wrote:
On 21-May-2016 19:09 -0500, CRPence wrote:
<<SNIP>>
Setup: CREATE VIEW with no table-references, (*SYS) system-naming
in effect, current schema = DEFAULT [i.e. *LIBL], alternative name
without FOR SYSTEM name [long-name] so a system-name must be
generated, no schema name as qualifier [i.e. unqualified] view-name
so view should be created in the current library (*CURLIB) for the
job.
Actual statement:
create view curlib_trickery
(intcol) as ( values(1) )
;
<<SNIP>>
As an aside the current schema does NOT match the *CURLIB.
Understood. They are different concepts; Current Library being an
IBM i OS concept, and the CURRENT SCHEMA library [list] being a DB2 for
i SQL concept to mimic the ANS SQL standards.
The *CURLIB is set by executing the CHGCURLIB CL command. The
current library is added to the library before the user part of the
library list.
Yep; that is understood too. Change Library List (CHGLIBL) and
equivalent Change Library List (QLICHGLL) API, or the CURLIB parameter
of the Command (*CMD) object [CRTCMD|CHGCMD], or the Change Current
Library (CHGCURLIB), ¿and…?. And the Library List (LIBL) is divided
into separate portions, in order descending precedence of a *LIBL
search, these /portions/ are: SYS, PRD, CUR, USR
Unqualified specified objects are created in the QGPL library when
System Naming is used, except <ed: when> a CURRENT SCHEMA is set.
I will accept that can be the case, esp. when CURLIB(*CRTDFT) is in
effect, but generally, and rather than surmised, empirically shown, that
statement, at face-value, is hokum;
If that statement were even generally true [which prior CREATE VIEW
discussion has shown great evidence to the contrary], few of my test
cases [or actual code] ever would have functioned after a CREATE. I
almost never have a job run with the Q [system] Garbage Pile Library
(QGPL) in my library list, so how any of my statements that followed a
CREATE seemed never to have any problem finding\resolving-to the
previously created objects in *LIBL, I could never guess. And even when
I do have QGPL in my library list, I have never had an object get
created there [unexpectedly] per use of a CREATE.
The effect I have experienced generally [for most CREATE statements
when] using system-naming (*SYS) for the SQL, is that, other than when
the SQL insists on placing the object into the same library as one of
the dependent objects [e.g. INDEX or VIEW], the object is created into
my Current Library (*CURLIB) [the /current/ as system concept, not the
/current/ as SQL concept]. Specifically, I *never* have had a CREATE
TABLE unqualified-name [or CREATE PROCEDURE or many other CREATE xxx]
create into QGPL with a default *SYS-naming setup; not unless either I
had previously done a SET CURRENT SCHEMA QGPL [or effected the
equivalent via a client\client-like environment\options interface to the
SQL], or the dependent object was in QGPL -- also implying I had QGPL in
my *LIBL or had SET the current schema to QGPL.
Regardless, the specific setup I gave, including the actual statement
matches identically to the scenario in the doc for CREATE VIEW that
says: "If no table or user defined table function is referenced in the
fullselect, [then] the current library (*CURLIB) will be used."
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/db2/rbafzhcview.htm]
For setting the CURRENT SCHEMA the SET CURRENT SCHEMA or SET SCHEMA
command must be performed.
If a CURRENT SCHEMA is explicitly set, the library list is no longer
searched, instead all unqualified specified objects are taken from
the CURRENT SCHEMA, which not even have to be in the library list.
Seems similar to what I infer is a problem with the docs. That is,
the function of /search/ for object references via the library list and
the separate topic of the /create-target/, when current_schema='*LIBL',
are being conflated. Doing so surely leads great confusion.
The docs on /unqualified names/ for one group of object types
discusses only _searching for_ [resolving-to] those object references.
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzqualun1.htm].
Whereas at least the docs for the other object types specifically
suggests that "If an unqualified name is the main object of an […]
CREATE […] statement, [then] the name is implicitly qualified using the
same rules as for qualifying unqualified TABLE names."
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_71/db2/rbafzqualun2.htm]
Sadly, that reference is made to the aforementioned docs, for which
there was no clear statement, that with system-naming in effect, the
unqualified TABLE object gets created into the *CURLIB of the job,
unless the CURRENT SCHEMA is set to a specific name [i.e. is not *LIBL]
whereupon instead, the TABLE object gets created into that specific-name
schema [aka library]. <-- Again, I suggest that is *not* QGPL, else the
apparatus is clearly FUBAR.
midrange.com
