Views on the Sample Tables

 <  Day Day Up  >  

When you install the sample database, there are also several views created on the sample tables. These views are listed in Table A.3. All of the views are qualified by DSN8810 (where the fifth letter indicates the version of DB2, in this case, 8 for V8).

Table A.3. Sample Views

View

Table(s)

Columns

Predicates?

VDEPT

DEPT

DEPTNO , DEPTNAME , MGRNO , ADMRDEPT

N/A

VHDEPT

DEPT

DEPTNO , DEPTNAME , MGRNO , ADMRDEPT , LOCATION

N/A

VEMP

EMP

FIRSTNME , MIDINIT , LASTNAME , WORKDEPT

N/A

VEMPLP

EMP

EMPNO , PHONENO

N/A

VPROJ

PROJ

PROJNO , PROJNAME , DEPTNO , RESPEMP , PRSTAFF , PRSTDATE , PRENDATE , MAJPROJ

N/A

VACT

ACT

ACTNO , ACTKWD , ACTDESC

N/A

VPROJACT

PROJACT

PROJNO , ACTNO , ACSTAFF , ACSTDATE , ACENDATE

N/A

VEMPPROJACT

EMPPROJACT

EMPNO , PROJNO , ACTNO , EMPTIME , EMSTDATE , EMENDATE

N/A

VDEPMG1

DEPT

DEPTNO , DEPTNAME , MGRNO ADMRDEPT

MGRNO = EMPNO

 

EMP

FIRSTNME , MIDINIT , LASTNAME

 

VEMPDPT1

DEPT

DEPTNO , DEPTNAME , MGRNO

MGRNO = EMPNO

 

EMP

SUBSTR(FIRSTNME,1,1) , MIDINIT , LASTNAME, WORKDEPT

 

VPHONE

EMP

LASTNAME , FIRSTNME , MIDINIT , PHONENO , EMPNO ,

WORKDEPT = DEPTNO

 

DEPT

DEPTNAME

 

VPROJRE1

PROJ

PROJNO , PROJNAME , DEPTNO MAJPROJ

RESPEMP = EMPNO

 

EMP

EMPNO , FIRSTNME , MIDINIT , LASTNAME

 

VSTAFAC1

PROJACT

PROJNO , ACTNO , ACSTAFF , ACSTDATE , ACENDATE ,

ACTNO = ACTNO

 

ACT

ACTDESC

 

VSTAFAC2

EMPPROJACT

PROJNO , ACTNO , ACSTAFF , ACSTDATE , ACENDATE , EMPTIME , EMSTDATE , EMENDATE

ACTNO = ACTNO

 

ACT

ACTDESC

 
 

EMP

EMPNO , FIRSTNME , MIDINIT , LASTNAME

EMPNO = EMPNO


There are also several views created on top of the sample views. The DDL for these views follows :

 

 CREATE VIEW DSN8810.  VASTRDE1  (DEPT1NO, DEPT1NAM, EMP1NO, EMP1FN, EMP1MI, EMP1LN, TYPE2,    DEPT2NO, DEPT2NAM, EMP2NO, EMP2FN, EMP2MI, EMP2LN) AS SELECT ALL       D1.DEPTNO, D1.DEPTNAME, D1.MGRNO, D1.FIRSTNME, D1.MIDINIT,       D1.LASTNAME, '1',       D2.DEPTNO, D2.DEPTNAME, D2.MGRNO, D2.FIRSTNME, D2.MIDINIT,       D2.LASTNAME FROM  DSN8810.VDEPMG1 D1,       DSN8810.VDEPMG1 D2 WHERE D1.DEPTNO = D2.ADMRDEPT; CREATE VIEW DSN8810.  VASTRDE2  (DEPT1NO, DEPT1NAM, EMP1NO, EMP1FN, EMP1MI, EMP1LN, TYPE2,     DEPT2NO, DEPT2NAM, EMP2NO, EMP2FN, EMP2MI, EMP2LN) AS SELECT ALL       D1.DEPTNO, D1.DEPTNAME, D1.MGRNO, D1.FIRSTNME, D1.MIDINIT,       D1.LASTNAME, '2',       D1.DEPTNO, D1.DEPTNAME, E2.EMPNO, E2.FIRSTNME, E2.MIDINIT,       E2.LASTNAME FROM  DSN8810.VDEPMG1 D1,       DSN8810.EMP E2 WHERE D1.DEPTNO = E2.WORKDEPT; CREATE VIEW DSN8810.  VPSTRDE1  (PROJ1NO, PROJ1NAME, RESP1NO, RESP1FN, RESP1MI, RESP1LN,      PROJ2NO, PROJ2NAME, RESP2NO, RESP2FN, RESP2MI, RESP2LN) AS SELECT ALL       P1.PROJNO, P1.PROJNAME, P1.RESPEMP, P1.FIRSTNME, P1.MIDINIT,       P1.LASTNAME,       P2.PROJNO, P2.PROJNAME, P2.RESPEMP, P2.FIRSTNME, P2.MIDINIT,       P2.LASTNAME FROM  DSN8810.VPROJRE1 P1,       DSN8810.VPROJRE1 P2 WHERE P1.PROJNO = P2.MAJPROJ; CREATE VIEW DSN8810.  VPSTRDE2  (PROJ1NO, PROJ1NAME, RESP1NO, RESP1FN, RESP1MI, RESP1LN,      PROJ2NO, PROJ2NAME, RESP2NO, RESP2FN, RESP2MI, RESP2LN) AS SELECT ALL       P1.PROJNO, P1.PROJNAME, P1.RESPEMP, P1.FIRSTNME, P1.MIDINIT,       P1.LASTNAME,       P1.PROJNO, P1.PROJNAME, P1.RESPEMP, P1.FIRSTNME, P1.MIDINIT,       P1.LASTNAME FROM  DSN8810.VPROJRE1 P1 WHERE NOT EXISTS        (SELECT *         FROM  DSN8810.VPROJRE1 P2         WHERE P1.PROJNO = P2.MAJPROJ); CREATE VIEW DSN8810.  VFORPLA  (PROJNO, PROJNAME, RESPEMP, PROJDEP, FRSTINIT, MIDINIT, LASTNAME) AS SELECT ALL       F1.PROJNO, PROJNAME, RESPEMP, PROJDEP,       SUBSTR(FIRSTNME,1,1), MIDINIT, LASTNAME FROM  DSN8810.VPROJRE1 F1 LEFT OUTER JOIN DSN8810.EMPPROJACT F2 ON    F1.PROJNO = F2.PROJNO; 

All the views outlined in Table A.3 and the previous DDL are used in the sample applications that are shipped with DB2.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net