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. |