On 20-May-2016 16:21 -0500, Buck Calabro wrote:
The Fine Manual at CREATE VIEW describes the situation - if I
ignore the bit about SQL names. <<SNIP>>
Best guess? Where it says 'SQL names were specified...' really means
'*SQL NAMING was specified...'
Indeed. That nonsense for use of "SQL names" vs "SQL naming" has
been in the documentation for as long as I can recall. So a reader
might understand to what the above refers:
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/db2/rbafzhcview.htm]
IBM i 7.2->Database->Reference->SQL reference->Statements->CREATE VIEW
"...
view-name
If system names were specified, the view will be created in the schema
that is specified by the qualifier. If not qualified and there is no
default schema, the view name will be created in the same schema as the
first table specified on the first FROM clause (including FROM clauses
in any common table expressions or nested table expression). If no
tables are referenced in the fullselect, the view will be created in the
same schema as the first user defined table function. If no table or
user defined table function is referenced in the fullselect, the current
library (*CURLIB) will be used.
..."
<<SNIP>> the production view needs to use tables in two different
libraries (thus *SYSTEM naming and *LIBL). SET SCHEMA won't help
because I will then have to qualify one of the tables referenced in
the view, and the whole point of this exercise was to avoid that.
The plan was to make it portable across test and production.
Having cross-library based-on-file\dependencies can be problematic
for testing when duplicating [CRTDUPOBJ] or restoring [RSTOBJ] of such a
logical [view] file as well.
But the CREATE VIEW effect, I found to be much more frustrating; esp.
because at some point, eons ago, the effect changed; perhaps changed as
part of a correction to properly reflect either some standard or some
other flavor of DB2, or to revert to that as prior effect. I recall
that the current library had been the target for my creates while using
system-naming, up until that /fix/; since however, my test-cases started
to fail, because the creation scripts hence created some VIEW objects
into an unexpected library rather than into the *CURLIB as had been the
prior effect. I never understood why the proprietary *SYS naming rules
did not actually mimic /normal/ CRTxxx effects whereby unqualified names
would create into *CURLIB :-(
I also recall that those documented effects for the specification of
an unqualified view-name in a CREATE VIEW statement was my impetus for a
change to use REXX SQL dynamic scripting [a change from using static
scripts like with RUNSQLSTM] to create my test objects. My creation
scripts for test-case setup always knew both, what libraries to
establish for the library list to find\resolve the test objects, and
what library into which to create any test objects. The create-into
library name was passed-in, as input to the dynamic scripting. That
particular change to more often use REXX is what later had me
learning\experiencing that CREATE ALIAS does not function properly in
REXX; though I forget now what was the issue {easily enough searched on
the web; e.g. found somewhere in the thread of this message:
[
http://archive.midrange.com/midrange-l/201004/msg00206.html]}, but I do
recall that they refused to fix the issue, per the issue not having been
reported by a /customer/.
Using REXX with *SYSTEM naming, I was dynamically library-qualifying
the VIEW-name on any CREATE VIEW [with cross-library dependencies] to
ensure that the creation was into the test-library rather than being
created into the library of the first table-reference. That
test-library typically had been [created and then] established earlier
within the script, as the current library (*CURLIB). So the
create-into-library was ensured by use of a library-qualifier [passed as
an argument to the REXX SQL], yet any unqualified table-references
elsewhere in the AS-clause still would be resolved using the library
list (*LIBL) due to the *SYSTEM naming.
p.s. I could have sworn this identical issue had been discussed in
the past; also specifically addressing the problem arising, due to
static scripts should remain identical between test and production. I
could not find any topics when searching the web. I expect if I had
replied, I would have mentioned REXX [or STRREXPRC] vs RUNSQLSTM; e.g.
like in [
http://archive.midrange.com/midrange-l/201110/msg00493.html],
but that topic was not specific to lack of a schema name as qualifier
nor even CREATE VIEW.
midrange.com
