Chapter 16. The Procedural DBA

IN THIS CHAPTER

• The Classic Role of the DBA

• The Role of the Procedural DBA

In the past, DBMS products were designed only to manage and store data in an optimal manner. Although these core capabilities are still required of modern DBMS products, the purview of the DBMS is no longer limited just to data. With the advent of client/server computing and active databases, procedural logic also is being stored and managed by the DBMS.

DB2 is maturing and gaining more functionality. The clear trend is that more and more procedural logic is being stored in the DBMS. Triggers, user -defined functions, and stored procedures enable developers to use the DBMS to accomplish programming tasks that used to be outside the domain of DB2.

NOTE

But DB2 has been able to store logic in its databases for years using exit routines. How do the more modern procedural database techniques differ from exit routines?

An exit routine, such as an EDITPROC , FIELDPROC , or VALIDPROC , is usually coded in Assembler language. This code is then attached to a specific database object and is executed at a specified time, such as when data is inserted or modified. Exit routines have been available in DB2 for many years; typically, the DBA or systems programmer is responsible for coding and maintaining them. Exit routines, however, are primitive when compared with the procedural logic support provided by a modern DBMS. Exit routines are structured to accomplish only one type of task.

• A FIELDPROC transforms data on insertion and converts the data to its original format on subsequent retrieval.

• An EDITPROC is functionally equivalent to a FIELDPROC , but it acts on an entire row instead of a column.

• A VALIDPROC receives a row and returns a value indicating whether a data modification is valid, and therefore should proceed. It simply assesses the validity of the data.

Triggers, user-defined functions, and stored procedures, all examples of modern database-administered procedural logic, can be programmed to accomplish many different types of tasks. They are not limited in scope like exit routines.

This chapter discusses the management and administrative challenges of implementing and controlling procedural logic in the database. For details on DB2's support and implementation for procedural database objects, refer to Chapter 4, "Using DB2 User-Defined Functions and Data Types," for user-defined functions, Chapter 8, "Using DB2 Triggers for Integrity," for triggers, and Chapter 15, "Using DB2 Stored Procedures," for stored procedures. In this chapter, you will learn the reasoning and requirements for the Procedural DBA.