Creating views of payroll operations

Examples

Example: PAYDEPT view

The PAYDEPT view shows all the columns of the employee table except for job, salary, bonus, and commission. The view does not show the rows for members of the payroll operations department.

The WORKDEPT value for the payroll operations department is P013. The owner of the employee table uses the following statement to create the PAYDEPT view:

CREATE VIEW PAYDEPT AS
  SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, 
         PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE 
    FROM DSN8D10.EMP
    WHERE WORKDEPT<>'P013'
  WITH CHECK OPTION; 

The CHECK OPTION ensures that every row that is inserted or updated through the view conforms to the definition of the view.

Example: Example: PAYMGR view

A second view, the PAYMGR view, gives Spiffy payroll managers access to any record, including records for the members of the payroll operations department.

The owner of the employee table uses the following statement to create the PAYMGR view:

CREATE VIEW PAYMGR AS
  SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, 
         PHONENO, HIREDATE, JOB, EDLEVEL, SEX, BIRTHDATE 
    FROM DSN8D10.EMP
  WITH CHECK OPTION; 

Neither PAYDEPT nor PAYMGR provides access to compensation amounts. When a row is inserted for a new employee, the compensation amounts remain null. An update process can change these values at a later time. The owner of the employee table creates, owns, and grants privileges on both views.