by Roman B. Melnyk and Paul C. Zikopoulos
Today’s efficient businesses strive to balance the cost savings of a just-in-time (JIT) inventory system with the potential for lost sales resulting from stock-out situations. A common database design for a JIT procurement system is to have a “master” table that contains up-to-date information about a particular domain of the business. One example is a PARTS table that tracks inventory. When key performance indicators in this table fall below certain thresholds, business logic is invoked to replenish the stock, thereby avoiding stock-out conditions and lost sales. Another table (for example, an ORDERS table) handles transactions. This table contains the changes that must be applied to the master table to consolidate inventory levels for future sales. For example, a particular item was ordered and now the on-hand inventory must be decreased by one. The ORDERS table could also include returned product that was shipped from a different distribution center. This “not-on-hand” type of inventory must also be accounted for.
An application designed to manage these types of inventory must be able to:
- Accommodate changes to existing stock levels. This is handled through update operations.
- Add new items to the inventory as a result of returns that have not yet been buffered through a particular distribution center. This is handled through insert operations.
Traditionally, an application developer would have to code separate UPDATE and INSERT statements into an application to address these requirements. These functions are typically exposed through some type of graphical user interface (GUI) that a customer service representative uses to query the database and to modify the data when, for example, a customer picks up an order or returns a product. Combined update, insert and even delete operations (“merge” operations, in effect) would enable a user to work through the GUI while the underlying application efficiently “hardens” the changes in the database.
This article outlines SQL MERGE statement support in the IBM DB2 Universal Database (DB2 UDB) products as of Version 8.1.2. The MERGE statement combines conditional update, insert and delete operations on a target table or updatable view.
