| || |
|Application overview||Page 1 of 5|
This tutorial walks you through the steps required to
develop the database schema and logic for a sample items inventory
application. The database objects are
created in an ad-hoc manner directly against the database using the IBM
Explorer as opposed to using the DB2 database project.
The application consists of the following database objects:
- Inventory table. This table is used to maintain the list of inventory items.
It defines their ID, name, warehouse
location code, current quantity, low quantity threshold, and high quantity
- InventoryHistory table. This table has a log
of all historical updates to the quantity column of the Inventory
table. It tracks such things as the
updated user ID and timestamp.
- InventoryName index.This index allows for
more efficient lookup of inventory items by name from the Inventory
- LowInventory view. This view lists all of the
inventory item entries in the Inventory table where the current
quantity is less than the low quantity threshold.
- InventoryTracker trigger. This trigger is
activated after every update to the quantity column of the Inventory
table. It inserts a log entry for
each update made to the InventoryHistory
table capturing the updater user ID and update timestamp.
- InventoryIndicator scalar function. This SQL
scalar function returns a quantity level indicator of Low, Medium, and
High, given current quantity, low quantity threshold, and high quantity
thresholds for an inventory item.
- InventoryWarehouse table function. This SQL
table function returns all inventory items from the Inventory table
having a specific warehouse location code.
This table function takes the desired warehouse location code as an
- InventoryAnalyzer procedure. This SQL
procedure will analyze the Inventory table and return the inventory
items ID, name, and stock level indicator for all inventory items for a
specific warehouse location. This
procedure takes the desired warehouse location code as an input parameter.
Although this tutorial shows you how to create these
database objects directly from the IBM Explorer, you can certainly adopt the
same method to create them using generated DB2 script files in a DB2 database
project. The advantage of using the
database project approach is that you can adopt a more iterative development