Re: Creating a view

[CRPence <crp@xxxxxxxxxxxxxxxxxxxx>]

  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