Skip to content

DBAzine.com

Sections
Personal tools
You are here: Home » Of Interest » Articles of Interest » What Is a DBA? - Part 6
Seeking new owner for this high-traffic DBAzine.com site.
Tap into the potential of this DBA community to expand your business! Interested? Contact us today.
Who Are You?
I am a:
Mainframe True Believer
Distributed Fast-tracker

[ Results | Polls ]
Votes : 3554
 

What Is a DBA? - Part 6

by Craig S. Mullins
From Craig Mullins' Database Administration: The Complete Guide to Practices and Procedures, Addison Wesley Professional; ISBN: 0201741296; 1st edition (June 14, 2002).

Part 1  |  Part 2  |  Part 3  |  Part 4  |  Part 5  |  Part 6

New Technology and the DBA

The DBA is at the center of the action whenever new ways of doing business and new technologies are introduced to the organization. Data is the lifeblood of modern business, data is housed by the database, and the DBA is the expert who understands database technology — and in particular, how databases can be integrated with other new technologies.

Let’s examine three specific newer technologies that rely on database administration — at least somewhat — to be effectively implemented: database-coupled application logic, Internet-enabled e-business development, and hand-held computing.

Procedural DBAs: Managing Database Logic

Until recently, the purpose of a database management system was, appropriately enough, to store, manage, and access data. Although these core capabilities are still required of modern DBMS products, additional procedural functionality is slowly becoming not just a nice feature to have, but a necessity. Features such as triggers, user-defined functions, and stored procedures provide the ability to define business rules to the DBMS instead of in separate application programs. These features couple application logic tightly to the database server.

Since all of the most popular RDBMS products provide sometimes-complex features to facilitate database-coupled procedural logic, additional management discipline is required to ensure the optimal use of these features. Typically, as new features are added, their administration, design, and management are as-signed to the DBA by default. However, without proper planning and preparation, chaos can ensue. First let’s examine how database logic is stored in a DBMS.

Stored Procedures

Stored procedures can be thought of as programs that live in a database. The procedural logic of a stored procedure is maintained, administered, and executed through the database commands. The primary reason for using stored procedures is to move application code from a client workstation to the database server. Stored procedures typically consume less overhead in a client/server environment because one client can invoke a stored procedure that causes multiple SQL statements to be run. The alternative, the client executing multiple SQL statements directly, increases network traffic and can degrade overall application performance.

A stored procedure is a freestanding database object; it is not “physically” associated with any other object in the database. A stored procedure can access and/or modify data in many tables.

Triggers

Triggers are event-driven specialized procedures that are attached to database tables. The trigger code is automatically executed by the RDBMS as data changes in the database. Each trigger is attached to a single, specified table. Triggers can be thought of as an advanced form of rule or constraint that uses procedural logic. A trigger cannot be directly called or executed; it is automatically executed (or “fired”) by the RDBMS as the result of a SQL INSERT, UPDATE, or DELETE statement issued on its associated table. Once a trigger is created, it is always executed when its firing event occurs.

User-Defined Functions

A user-defined function (UDF) provides a result based on a set of input values. UDFs are programs that can be executed in place of standard, built-in SQL scalar or column functions. A scalar function transforms data for each row of a result set; a column function evaluates each value for a particular column in each row of the results set and returns a single value. Once written and defined to the RDBMS, a UDF becomes available just like any other built-in database function.

Table 1 summarizes the differences between stored procedures, triggers, and UDFs.

Administering Stored Procedures, Triggers, and UDFs

Once developers begin to rely on stored procedures, triggers, and UDFs, DBAs need to take steps to manage them properly. DBAs must grapple with the issues of quality, maintainability, efficiency, and availability. How and when will these procedural objects be tested? The impact of a failure is enterprisewide, increasing the visibility and criticality of these objects. Who is responsible if they fail? The answer must be — the DBA.

Object Type Definition Executed How
Stored Procedure Program logic executed
on the database server
By request Explicit
Triggers Event-driven procedures
attached to database
tables
Automatically Implicit
UDFs Program logic extending
SQL functionality
By request in SQL Explicit

Table 1: Procedural Database Objects.

The role of administering procedural database logic should fall upon someone skilled in that discipline. A new type of DBA is required to accommodate the administration of database procedural logic. This new role can be defined as a procedural DBA.

The procedural DBA is responsible for those database management activities that require procedural logic support. He ensures that stored procedures, triggers, and user-defined functions are effectively planned, implemented, shared, and reused. The procedural DBA also takes primary responsibility for coding and testing all triggers. Stored procedures and user-defined functions, although likely to be coded by application programmers, should be reviewed for accuracy and performance by procedural DBAs. (See Figure 1.)

Figure 1: Procedural DBA duties.

The procedural DBA leads the review and administration of all procedural database objects: triggers, stored procedures, and UDFs. Although the procedural DBA is unlikely to be as skilled at programming as an applications programmer or systems analyst, he must be able to write and review program code reasonably well. The skill level required depends on what languages are supported by the DBMS for creating procedural objects, the rate and level of adoption within the organization, and whether an internal organization exists for creating common, re-useable programs. Table 2 provides a reasonable level of procedural DBA involvement for each type of procedural object. Additionally, the procedural DBA should be on call for any problems that occur with database procedural objects in production.

As shown in Figure 1, the procedural DBA requires communication skills as much as he requires technological acumen. In addition to managing and optimizing database procedural objects, the procedural DBA must inform the development community of new triggers, stored procedures, and UDFs. Furthermore, the DBA must promote reuse. If the programmers do not know that these objects exist, they will never be used.

Other procedural administrative functions can be allocated to the procedural DBA. Depending on the number of DBAs and the amount of application development needed, the procedural DBA can be assigned to additional functions such as the following:

      • Participating in application code design reviews
      • Reviewing and analyzing SQL access paths (from “EXPLAIN” or “SHOW PLAN”)
      • Debugging SQL
      • Writing and analyzing complex SQL statements
      • Rewriting queries for optimal execution

Object Type Level of Procedural DBA Involvement
Stored Procedure Not likely to write stored procedures; must review all code
before migration to production; communicates availability
and promotes reuse.
Triggers Likely to write, test, and debug triggers; communicates deployment
of triggers to ensure application awareness.
UDFs Not likely to write user-defined functions; works closely with
the development team to ensure UDF functionality and performance;
reviews all code before migration to production;
communicates availability and promotes reuse.

Table 2: Procedural DBA Involvement by Object.

Offloading coding-related tasks to the procedural DBA can help the other staff DBAs concentrate on the actual physical design and implementation of databases, resulting in much better designed databases. Procedural DBAs should have the same line of report as traditional DBAs to enable better sharing of skills between the groups. Of course, there will need to be a greater synergy between procedural DBAs and the application programmers. The procedural DBA should typically come from the application programming ranks because this is where the coding skill exists.

The Internet: From DBA to e-DBA

Companies of every size are using Internet technologies to speed up business processes. Indeed, e-business has evolved as a new term to describe the transformation of key business processes using Internet technologies. Modern organizations use the Web to communicate with their partners and customers, to connect with their back-end databases, and to conduct transactions (e-commerce). E-business is the integration of traditional information technology with the Internet. This integration creates a more nimble business, prepared for the trials and tribulations of conducting business in the 21st century.

E-businesses must be able to adapt and react to constant change. When a business is online, it never closes. People expect full functionality on Web sites they visit regardless of the time. And the Web is worldwide. It may be two o’clock in the morning in New York City, but it is always prime time somewhere in the world. An e-business must be available and prepared to engage with customers 24 hours a day, 365 days a year (366 during leap years). Failure to do so risks losing business. When a Web site is down, the customer will go elsewhere to do business because the competition is just a simple mouse-click away. Therefore, those who manage an e-business must be adept, proactive, and ever vigilant.

The frantic pace of an e-business makes extreme demands on those who keep it operational, and DBAs are much affected. The need to integrate the Web with traditional IT services, such as the DBMS, places high expectations on database administrators.

An e-DBA is a DBA who is capable of managing Web-based applications and their Internet-related issues. With all of the knowledge and training of a traditional DBA, the e-DBA adapts these skills to suit applications and databases that are Internet enabled. When the Web is coupled with traditional applications and databases, a complex infrastructure is the result. (See Figure 2.) The e-DBA must be capable of navigating this complex, heterogeneous infrastructure and providing expertise wherever databases interact within this infrastructure.

Figure 2: The complex infrastructure enabling Web-to-database capabilities.

Many factors impact database administration when you couple the Internet with database technology. Some of these issues include

      • 24/7 data availability
      • New technologies such as Java and XML
      • Web connectivity
      • Integration of legacy data with Web-based applications
      • Database and application architecture
      • Web-based administration
      • Performance engineering for the Internet
      • Unpredictable workload

The PDA DBA

Personal digital assistant devices, better known as PDAs, are fast becoming a necessity for modern executives and businessmen. A PDA is a handheld computing device. Whether your PDA of choice is a Palm Pilot or a PocketPC, your PDA may soon have a DBMS running on it. Why is that interesting? Does it change the way you will use your PDA? What will that mean to your IT department?

PDAs offer many benefits. The devices are small and therefore easily transportable. They enhance a mobile worker’s ability to be mobile. However, challenges must be faced as organizations incorporate PDAs into their infrastructure. Companies with remote workers such as a distributed sales force or delivery tracking services will most likely be the first impacted. The data on the PDAs must be managed professionally to ensure integrity and reliability. Because the device is remote, sharing of data can be difficult. The data on the PDAs must be reliably synchronized with existing enterprise systems and databases.

All major DBMS vendors provide small-footprint versions of their flagship products to run on PDAs. For example, IBM markets DB2 Everyplace, Oracle sells Oracle8i Lite, and Sybase offers Adaptive Server Anywhere. The general idea is to store a small amount of critical data on the PDA in a database; the local PDA database is later synchronized to long-term data stores on enterprise database servers. Each PDA DBMS provides technology to synchronize data back and forth from the PDA to the enterprise server platforms.

A database the size of those stored on PDAs should not require the in-depth tuning and administration required of enterprise database implementations. However, DBAs will be called upon to help design appropriately implemented databases for small-form-factor devices like PDAs. The biggest impact on the DBA will be the necessity for managing data synchronization from hundreds or thousands of PDAs. When should synchronization be scheduled? How will it impact applications that use large production databases that are involved in the synchronization? How can you ensure that a mobile user will synchronize his data reliably and on schedule?

These are not trivial issues. The DBA staff must be ready to support the organization’s inevitable request for this technology by understanding data synchronization technology and the need for remote database users at their organization. Pervasive computing and the mobile workplace are here to stay. The DBA staff must be ready to support these mobile workers with a valid, shared data infrastructure.

As new technology is introduced to the organization, the DBA group is typically the first to examine and use it. The preceding three technologies are merely examples of new trends and technologies that require database administration for efficient and effective implementation.

DBA Certification

Professional certification is a recent trend in IT and is available for many different IT jobs. The availability and levels of certification for database administration have been growing at an alarming rate. Certification programs are available for most of the popular DBMS platforms including IBM DB2, Microsoft SQL Server, and Oracle. The idea behind DBA certification is to ensure that an individual is capable of performing database administration tasks and duties.

This is a noble goal, but the problem is that passing a test is not a viable indicator of success with the complexities of database administration. Some things you just have to learn by doing. I’m not saying that certification is useless: Taking the test and focusing on the questions you miss can help to point out areas of weakness. But does anyone really believe that someone passing a formalized test is necessarily as capable as someone with several years of experience as a DBA? Organizations should hire DBAs based on experience that indicates a level of capability. Of course, a DBA with both experience and certification is even better.

That said, I do recommend that professional DBAs take the time to study and pass the certification exams. Not because certification will make you a better DBA, but because it will make you more employable. Some companies hire only certified professionals. The trend toward using certification to guide hiring practices will increase because of increasing IT complexity. If you think you might change jobs at some point in your career (and who among us will not?), then certification is a worthwhile pursuit.

Keep in mind that DBA certification tests sometimes ask arcane syntax questions that are not good indicators of a DBA’s skills. Getting the syntax 100 percent accurate is what manuals and design tools are for. Memorizing every detail about SQL syntax and structure is a waste of time because it is complex and changes all the time. It is better to know where to find the syntax, parameters, and answers to your questions when you need them — that is, which manuals and textbooks contain the needed information. DBAs should possess a broad, overarching knowledge of DBMS concepts, IT fundamentals, and the working of their organization’s database systems. In other words, it is better to know off the top of your head that something can (or cannot) be done than to know the exact syntax for how to accomplish it.

If you decide to pursue certification, take the time to prepare for the tests. Books and self-learning software titles are available that can be quite useful. These books and programs cover the most common test topics and provide sample questions to help you prepare. In many ways, it is like preparing for a college entrance exam like the SAT.

Finally, once you earn your certification, make sure you display it proudly on your resume and your business card (if your company allows it).

Table 3 lists Web sites that contain information about professional certification for the most popular DBMS products.

DBMS Web site
Oracle http://www.oracle.com/education/certification/
Microsoft SQL Server http://www.microsoft.com/trainingandservices/default.asp?PageID=training
IBM DB2 http://www.ibm.com/certify
Sybase Adaptive Server http://www.sybase.com/education/profcert/
Informix http://www.informix.com/informix/training/courses/certific/welcome.htm

Table 3: Sources of DBA Certification Information.

The Rest of the Book

This first chapter has introduced you to the world of the DBA. I hope that you have gained respect for the complexity of the position and the qualities required of a good DBA. The remainder of the book will examine the details of the tasks, roles, and responsibilities required of the DBA.

Review

      1. What are the primary high-level job responsibilities of a DBA?
      2. What is the single biggest problem faced by organizations using relational databases?
      3. What is the difference between a data administrator and a database administrator?
      4. What factors determine the number of DBAs needed to support an organization’s database environment properly?
      5. How does new technology impact the job of the DBA?
      6. What are the technologies that mandate the need for procedural DBAs?
      7. What is the difference between a database architect and a system administrator?
      8. Which staff member is most likely to be responsible for installing a new DBMS release?
      9. What are the three types of integrity that DBAs must understand?
      10. Is a certified DBA necessarily a qualified DBA? Why or why not?

Bonus Question

Why must the DBA be a jack-of-all-trades?

Previous — Multiplatform DBA Issues

--

Craig Mullins is an independent consultant and president of Mullins Consulting, Inc. Craig 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 DB2, Sybase, and SQL Server. Craig is also the author of the DB2 Developer’s Guide, the industry-leading book on DB2 for z/OS, and Database Administration: Practices and Procedures, the industry’s only book on heterogeneous DBA procedures. You can contact Craig via his web site at http://www.craigsmullins.com.


Contributors : Craig S. Mullins
Last modified 2006-01-16 07:23 AM
Transaction Management
Reduce downtime and increase repeat sales by improving end-user experience.
Free White Paper
Database Recovery
Feeling the increased demands on data protection and storage requirements?
Download Free Report!
 
 

Powered by Plone