Re: Creating a view

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