FREE DM Review Site Registration!
Sign-up today and access DM Review on the Web!

Your FREE registration entitles you to:
FREE email newsletters

FREE access to all DM Review content

FREE access to web seminars, resource portals, our white paper library and more!

   

Modern Database Administration, Part 2

In Part 1 of this three-part series, we discussed the impact of the Internet on database administrators (DBAs) and the challenges facing DBAs as they implement modern database systems. However, the Internet is but one facet of modern database systems. Indeed, database management systems are changing and growing more complex with each new version and release.

Database management systems (DBMSs) are taking on more functionality and being used for more than ever before. Today's databases do not just store data – they also store processes that act upon that data. Stored procedures, triggers and user-defined functions managed by the DBMS place new requirements on database administrators.

Procedural DBA

Until recently, a DBMS was used solely to store, manage and access data. Although this core capability is still required, modern DBMS products provide additional features to integrate procedural logic. Triggers, stored procedures and user-defined functions are examples of logic that is tightly coupled to the DBMS. As these newer features are exploited, the administration, design and management of these features usually is assigned to the DBA by default. Sometimes DBAs are assigned the task of coding these objects as well, but this is not always the best approach. What is required is a new type of DBA – a procedural DBA.

Let's quickly review the three types of database procedural logic.

Stored procedures can be thought of as "programs" that "live" in the DBMS. Deploying stored procedures moves application code from the client workstation to the database server. This minimizes overhead because one client can invoke a stored procedure which, in turn, can run multiple SQL statements, thereby minimizing network traffic. In order to be executed, a stored procedure must be specifically invoked by a command.

Triggers are event-driven procedures that are stored in and executed by the DBMS. Each trigger is attached to a specific table. Triggers can be thought of as an advanced form of rule or constraint written using procedural logic. A trigger cannot be directly called or executed; it is automatically executed (or "fired") by the DBMS as the result of an action – typically a data modification to the associated table. Once a trigger is created, it is always executed when its "firing" event occurs (update, insert, delete, etc.).

User-defined functions (UDFs) are programs that can be executed in place of standard, built-in SQL functions. A UDF provides a result based upon a set of input values. Once written, and defined to the DBMS, a UDF becomes available to be used in SQL statements just like any other built-in function.

Stored procedures, triggers and UDFs are controlled by the DBMS, just like other database objects such as tables and indexes. They are unlike other database objects because they are procedural, not declarative. Depending upon the particular DBMS implementation, these objects may or may not "physically" reside in the DBMS. They are, however, always registered to, and maintained in conjunction with, the DBMS.

The primary reason to use procedural database logic is to promote reusability. Instead of replicating code within multiple application programs, code can reside in a single place: the database server. This is preferable to cannibalizing sections of program code for each new application that must be developed. An additional benefit is increased consistency. If every user and every database activity with the same requirements is assured of using the database logic instead of multiple, replicated code segments, then the organization can be assured that everyone is running the same, consistent code.

Although the functionality provided by triggers, stored procedures and UDFs is unquestionably useful, these objects pose major administration challenges. DBAs must grapple with the issues of quality, maintainability and availability. How and when will these objects be tested? The impact of a failure is enterprise-wide, not relegated to a single application. This increases the visibility and criticality of these objects. Who is responsible if they fail? The answer must be a DBA. However, testing and debugging of code is not a typical role for DBAs.

Administering and managing data objects is the traditional and well-defined role of the DBA. However, data and database experts cannot be expected to debug procedures and functions written in C, COBOL or even procedural SQL. Even though many organizations rely upon DBAs to be the SQL experts in the company, oftentimes they are not experts – at least not data manipulation language experts. Simply because the DBA knows the best way to create a physical database design and data definition language does not mean he will know the best way to access that data.

A new type of DBA must be defined to accommodate procedural logic administration. This new role can be defined as a procedural DBA. Procedural DBAs require both a database and a programming background. They should be responsible for database management activities that require programming and similar activities. This includes primary responsibility for stored procedures, triggers and UDFs. Whether these objects are actually programmed by the procedural DBA will differ from shop to shop, depending on the size of the shop, the number of DBAs available and the scope of implementation. Minimally, the procedural DBA should lead code reviews and manage the use and reuse of database procedural logic. Additionally, the procedural DBA must be on call in the event of a stored procedure, trigger or UDF failure.

Other procedural administrative functions can be allocated to the procedural DBA including application code reviews, access path review and analysis, SQL debugging and complex SQL analysis. These are areas in which many DBAs are inadequately trained. It is a distinctly different skill to program than it is to create well-designed relational databases. Yet, DBAs quickly learn that they must be able to understand efficient application programming techniques. Off-loading some of these tasks to the procedural DBA will enable the traditional, data-oriented DBAs to concentrate on the actual physical design and implementation of databases. This should result in databases with much better design and better overall performance.

The first two installments of this three-part series analyzed the administrative requirements of implementing database systems using the Internet and procedural database logic. Both add complexity to the database environment and place additional skills requirements on DBAs.

Be sure to catch the third installment of this series next month which will cover additional challenges and provide advice on using intelligent automation to ease the burden on your DBA staff.


Craig S. Mullins is a data management strategist for NEON Enterprise Software, Inc. Mullins has extensive experience in the field of database management having worked as an application developer, a DBA and an instructor with multiple database management systems, including working with with DB2 for z/OS since Version 1. He is also an IBM gold consultant and is the author of two books DB2 Developer’s Guide and Database Administration: Practices and Procedures.You can contact him via email or at his Web site: http://www.craigsmullins.com.û

For more information on related topics, visit the following channels:



Industry Vendors