|
Hmmm... The comment suggesting that "although files named in a DML
statement can be redirected to another file, the file named in an
existing VIEW can not be redirected during run-time" seems to have
caused confusion.? I will try to explain.
The DML statement SELECT * FROM ORIGVIEW will redirect to NEWVIEW if
there was a prior OVRDBF ORIGVIEW TOFILE(NEWVIEW). However even if
there was instead, a prior OVRDBF ORIGTABL TOFILE(NEWTABL), the SELECT *
FROM ORIGVIEW would not be impacted; the ORIGTABL.mbrname that was bound
in the VIEW at create-time will still be referenced at run-time. The
file name ORIGTABL is not referenced in the DML statement, so any
override to that unreferenced name will not impact the statement.
The DDL statement to CREATE VIEW ORIGVIEW AS SELECT * FROM ORIGTABL
ignores overrides to ORIGTABL during the create, and the first member of
the named [based on] file ORIGTABL will be _bound_ into the VIEW object
at creation time; DSPFD ORIGVIEW will show the based-on file ORIGTABL
and the resolved name for member *FIRST. So even if that CREATE VIEW
was preceded by OVRDBF ORIGTABL TOFILE(NEWTABL), there is no change in
behavior.
If those words perhaps still seem a bit unclear, perhaps an example:
crtsrcpf qtemp/file1 rcdlen(13)
addpfm qtemp/file1 mbr(one)
inzpfm qtemp/file1 mbr(one) totrcds(1) records(*dft)
addpfm qtemp/file1 mbr(two)
inzpfm qtemp/file1 mbr(two) totrcds(2) records(*dft)
crtsrcpf qtemp/file2 mbr(only) /* zero records */
ovrdbf file1 tofile(*file) mbr(two) ovrscope(*job)
strsql
create view qtemp / two_vw as
( select * from file1 )
dspfd qtemp/two_vw *mbr /* file.mbr accessed: QTEMP/FILE1.ONE */
Based on file . . . . . . . . . . . . . . : FILE1
Library . . . . . . . . . . . . . . . . : QTEMP
Member . . . . . . . . . . . . . . . . : ONE
/* override to FILE1 still in effect */
runqry *none qtemp/two_vw /* or SQL SELECT * FROM QTEMP/TWO_VW */
SRCSEQ SRCDAT SRCDTA
000001 .00 0
****** ******** End of report **
That only one row appears in the above query indicates the result
data is from the first member of the FILE1, just as DSPFD indicates.
Thus the override had no effect on what based on file is referenced
during run-time. The override of FILE1 to access member two is ignored
because FILE1 is the base-on file, bound into the object; i.e. FILE1 is
*not* the file being referenced in the DML. The file that is named in
the DML statement is the file being opened, so the file TWO_VW is the
only file name that can be overridden. Continuing the example...
ovrdbf two_vw tofile(qtemp/file2) ovrscope(*job)
strsql
insert into two_vw values(1 ,070707 ,'ABC')
select * from qtemp/file2
SRCSEQ SRCDAT SRCDTA
1.00 70,707 ABC
******** End of data ****
The report shows that the row was inserted into the FILE2 due to the
override. Nothing special here; the file named in the DML was TWO_VW,
and TWO_VW was overridden to FILE2, so the INSERT applied to FILE2.
Regards, Chuck
--
All comments provided "as is" with no warranties of any kind
whatsoever and may not represent positions, strategies, nor views of my
employer
CRPence wrote:
An override will not influence the creation-time reference in SQL
DDL. Overrides are for run-time and open. Albeit some functions honor
listoverrides [by request] to assist with open; e.g. asking for file
definitions against which an override would redirect an open for that
file name, enables extracting attributes about a file that is about to
be opened. The SQL CREATE will look for [resolve to] the named file
object and first member without regard to any overrides. For the
run-time opens, although the files named in a DML statement can be
redirected to another file, the file named in an existing VIEW can not
be redirected during run-time; the already resolved file.mbr is always
file.*first. Neither ALIAS nor DDS LF can be referenced in a VIEW.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
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.
As an Amazon Associate we earn from qualifying purchases.
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.
midrange.com
