A view that shows all existing manufacturers in our catalog

create view MANUFACTURER as

(select distinct MANUFACTURER from ITSODB.CATALOG);

   A view that shows the inventory of each item in the catalog

create view INVENTORY as

(select ID, SKU, STOCK from ITSODB.CATALOG);

   A view that shows all products in the catalog

create view TYPES as

(select ID, MANUFACTURER, MODEL, TYPE from ITSODB.CATALOG);

View 

GROUPS

This view shows all existing groups of products in our sample catalog and the 

number of items associated to them. Granting privileges to users just on this 

view is a way to limit access for these users just on this data and not on the 

whole catalog.

View 

MANUFACTURER

This view provides information about the different manufacturers whose 

products are in our catalog. This view provides a fast way to find out all these 

manufacturers.

View 

INVENTORY

This view shows the inventory of all items in the catalog. Again, granting 

privileges on this view could be used for limited access to the catalog data 

(e.g., users having access to this table may see the inventory of the item, but 

not the price for which it is bought and sold).

View 

TYPES

This view is a qualified overview of all products in the catalog that could be 

used for a limited view on the catalog.

Example 10 2 shows the sample output of these views. Please notice that the 

SELECT

 statements on the views are much simpler than the 

SELECT

 statements on 

the tables the view is based on.

Example 10 2   Sample view data

db2> select * from GROUPS

PR CNT

10          16

85           5

 Chapter 10. Advanced DB2 UDB features 

319