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 thresholds.
• 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 table.
• 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 input parameter.
• 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 approach.