 Craig S. Mullins
& Associates, Inc.Database Performance Management |
|
| Summer 1994 |
|
|
DB2 for MVS: Triggers & Stored Procedures By Craig S. Mullins Edge Magazine asked experts to describe how four different relational database management systems (RDBMSs) approach some key features: triggers and stored procedures. The four approaches contrast sharply, as do the features themselves. DB2/6000 does not currently support triggers, for example; it does support a special form of stored procedures. Also, none of the procedures in these RDBMSs is compatible with one another, making migration from one RDBMS to another very difficult. Triggers are dynamic. Driven by events or by the clock, they start a stored procedure. Triggers are tied to specific tables and actions; they are invoked automatically whenever the table is the target of an UPDATE, DELETE, or INSERT statement. Stored procedures are passive — they must be initiated by a trigger or explicitly requested by a user or process. Stored procedures are not linked to specific tables; they may be invoked manually. Stored procedures allow an application program to be run in two parts: one on the client and one on the server. Also, stored procedures may be requested from a client application and executed from remote server workstations. Some experts believe that commonly used procedures should be stored with the database rather than with each application. Benefits include improved application reliability, lower development and maintenance costs, and reduced network traffic. In addition, they say, it is easier to enforce business rules from a single server than on many clients. DB2/MVS Version 3 of DB2/MVS does not provide inherent support for triggers and stored procedures. However, three "trigger-like" objects can be attached to DB2 tables and/or columns:
A FIELDPROC can be attached to columns in any DB2 table. Field procedures are programs that transform data on insertion and convert the data to its original format on subsequent retrieval. You can use a FIELDPROC to transform the following types of columns:
No FIELDPROCs are delivered with DB2, so they must be developed by the DB2 user. They are ideal for altering the sort sequence of values. Edit procedures An EDITPROC is functionally equivalent to a FIELDPROC, but it acts on an entire row instead of a column. Edit procedures are simply programs that transform data on insertion and convert the data to its original format on subsequent retrieval. One edit procedure for compressing data, DSN8HUFF, is supplied with DB2. Additional EDITPROCs must be developed by the DB2 user. They are ideal for implementing data compression routines and data encryption. Validation procedures A VALIDPROC receives a row and returns a value indicating whether LOAD, INSERT, UPDATE, or DELETE processing should proceed. A validation procedure is similar to an edit procedure, but it cannot perform data transformation; it simply assesses the validity of the data. A typical use for a VALIDPROC is to ensure valid domain values. For example, to enforce a Boolean domain, you could write a validation procedure to ensure that a certain portion of a row contains only T or F. DB2: The next version Of course, the ability to specify these three types of exit routines on DB2 tables is absolutely no replacement for comprehensive trigger support. Nor can the routines even remotely be considered any form of stored procedure. Truly, DB2/MVS lags behind the industry in providing trigger and stored procedure support. Rumor has it that the next release of DB2/MVS will include this support. However, IBM has not made any details available at this time. EDGE Magazine Online, published by PLATINUM technology, inc. © 1999 Craig S. Mullins &
Associates, Inc. All rights reserved.
|