The Role of the Procedural DBA

 <  Day Day Up  >  

Administering and managing data structures is the traditional duty of the DBA, and is well-defined in the industry. But most DBAs are experts in database design, DDL implementation, and database utilities. It is unreasonable to expect them to be able to code and debug procedures and functions written in C, COBOL, Java, or even procedural SQL (SQL/PSM). To implement and manage DB2 triggers, user -defined functions, and stored procedures effectively, a new type of DBA ”a Procedural DBA ”should be created.

The infrastructure required to manage procedural objects is different from that required to manage data alone. These new, procedural objects are a mixture of application program and traditional database objects (such as tables and indexes). Procedural objects, therefore, need to be managed like both database objects and application programs.

The creation of the new Procedural DBA role will have an impact on the roles and responsibilities of the DBA and programming staff. But effective implementation of the Procedural DBA function will result in an optimal environment for supporting procedural logic in the database.

The role of the DBA is expanding to encompass too many responsibilities for a single job function to perform the job capably in most shops . The Procedural DBA can be used to offload some of the duties of the traditional DBA. Start by splitting the DBA's job into two separate parts based on the database object to be supported: data objects and procedural objects (like triggers, UDFs, and stored procedures).

The traditional scope of the DBA role does not involve issues like debugging and testing. When triggers, UDFs, and stored procedures are implemented, you must treat them like any other program and make sure that they have been coded properly and then thoroughly tested and debugged . DBAs do not normally perform these tasks when they create database objects. The DBA may need to tweak some parameters or change syntax, but no testing and debugging is required of DDL CREATE statements for databases, tables, table spaces, and indexes.

The role of supporting procedural objects should fall to a group of professionals skilled in program development and procedural logic, as well as SQL and database administration.

The manner in which your shop implements Procedural DBA functionality will depend on the size of your organization and the degree to which you implement triggers, UDFs, and stored procedures. For smaller shops, or those not heavily implementing procedural code in the database, you may be able to get by with current staff if you train them accordingly .

Procedural DBA Tasks

The Procedural DBA should be defined to support and manage stored procedures, triggers, and UDFs, as well as other code- related DBA tasks, such as the following:

  • DBMS Logic Support ” Reviewing, supporting, debugging, tuning, and possibly even coding stored procedures, triggers, and user-defined functions. This task must include "on call" support.

  • Application Program Design Reviews ” Reviewing every application program completely before migrating the code to a production environment. This must include both traditional application programs and program logic required to implement stored procedures and user-defined functions.

  • Access Path Review and Analysis ” Using EXPLAIN and other tools to determine, review, and tune the access paths chosen by DB2. Additionally, the procedural DBA needs to understand how to tweak SQL for optimal performance and how to specify optimization hints using PLAN_TABLE to direct DB2 to use different access paths.

  • SQL Debugging ” Assisting developers with difficult SQL syntax and structures.

  • View Analysis and Design ” Assisting DBAs in creating optimal SQL for view definitions.

  • Complex SQL Analysis and Rewrite ” The Procedural DBA should be skilled in coding and developing complex SQL statements.

The role of the Procedural DBA is depicted graphically in Figure 16.1. Preferable, the Procedural DBA should report through the same management unit as the traditional DBA and not through the application programming staff. Reporting this way enables better skills sharing between the two distinct DBA types. Of course, your shop's needs may differ causing you to place the Procedural DBA functionality elsewhere in the organization. At any rate, synergy is required between the Procedural DBA and the application programmer/analyst. In fact, the typical job path for the Procedural DBA is most likely from the application programming ranks because the coding skill-base exists there.

Figure 16.1. Procedural DBA tasks.
graphics/16fig01.gif

When the procedural tasks are off-loaded from the traditional, data-oriented DBAs, the DBAs will be free to concentrate on the actual physical design and implementation of databases. The result should be much better database design and enhanced performance.

The Political Issues

After stored procedures and UDFs are coded and made available to DB2, applications and developers will begin to rely on them. Now that procedural logic is being managed by DB2, 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 objects fail? The answer must be "a DBA" ”preferably a Procedural DBA who understands the implementation and operation of procedural database objects.

Establishing a Procedural DBA function ensures that the political aspects of trigger, stored procedure, and UDF creation, use, and support have been adequately determined and documented prior to implementation. Failure to do so will cause a multitude of questions that are not easy to answer without a centralized support group.

For example, who will code stored procedures and UDFs, DBAs or application programmers? This decision can vary from shop to shop based on the size of the organization, the number of DBAs, and the commitment of the organization to stored procedures. A credible case can be made that the task should be a centralized function in order to promote reuseability and documentation.

After the decision is made as to who develops the stored procedures and UDFs, the next decision that needs to be made is who supports them. Stored procedure support must encompass design and code review, QA testing, documentation review, reuseability testing, and "on call" support.

If a centralized group is not "on call" for stored procedure failures, organizational in- fighting can occur. Consider, for example, a stored procedure developed by a Marketing application staff that modifies customer information. The stored procedure is developed, tested, documented, and migrated to production. Because proper reuseability guidelines were followed, the Sales application staff calls the same stored procedure in their code. Once it is in production, the Sales application fails at 2:00 a.m. Who gets called in to fix the problem? The Sales staff argues that the stored procedure was created by Marketing and no one on the Sales staff understands how the stored procedure works. The Marketing staff argues that their application did not bomb, the Sales application did. Without a centralized support function, the argument could go on all night. These issues need to be addressed before reuseable, procedural logic is implemented in the database.

Procedural DBAs must be technically astute and aware of the intricate details of implementing triggers, UDFs, and stored procedures. For example, the Procedural DBA must understand the firing order for triggers.

The Technical Issues

The Procedural DBA is not just a political role. Technical acumen is required to do the role justice . A thorough knowledge of DB2's implementation of procedural objects is required. Consider, for example, managing schemas.

DB2 triggers, UDFs, and stored procedures are created within a schema. A schema is a logical grouping of procedural database objects. By default, the schema name is the authid of the process that issues the CREATE statement for the procedural object.

NOTE

In addition to triggers, stored procedures, and UDFs, user-defined data types (UDTs) also are created within a schema. For this reason, consider also assigning the creation and management of UDTs to the Procedural DBA.


The Procedural DBA needs to understand schemas including:

  • How procedural objects are created in a schema

  • How the schema factors into execution of the procedural objects

  • How to set the SQL path and how the SQL path influences execution

  • How functions are resolved at run time

But schemas are not the only technical issue complicating the Procedural DBA role. Recall from Chapter 6, "DB2 Indexes," that when multiple triggers are coded on the same table, the order in which the triggers were created can impact their operation and subsequently data integrity. The rule for order of execution is: Triggers of the same type are executed in the order in which they were created. So, when triggers are dropped and re-created, the order of creation is important. This level of detail most likely will elude programmers that do not specialize in procedural objects ”another reason to implement Procedural DBAs.

As DB2 matures, more and more procedural logic will be managed by, stored in, and administered by the DBMS, causing database administration to become more complex. The role of the DBA is rapidly expanding to the point at which no single professional can be reasonably expected to be an expert in all facets of the job. Without a Procedural DBA function, supporting the DBMS- coupled logic used by DB2 applications will be difficult.

Procedural SQL

Procedural objects in DB2 are written using 3GL programming languages or a procedural dialect of SQL. Most of the other major DBMS vendors only support procedural SQL ”Oracle uses PL/SQL, Sybase and Microsoft use Transact-SQL, and Informix uses SPL. Each of these languages is proprietary, and they cannot interoperate with one another.

But what is procedural SQL? One of the biggest benefits derived from SQL (and relational technology in general) is the capability to operate on sets of data with a single line of code. By using a single SQL statement, you can retrieve, modify, or remove multiple rows. However, this capability also limits SQL's functionality. A procedural dialect of SQL eliminates this drawback through the addition of looping, branching, and flow of control statements. Procedural SQL has major implications on database design. For more details on SQL procedures language, DB2's version of procedural SQL, refer to Chapter 15.

The Procedural DBA needs to understand the various methods of creating procedural objects in DB2. Furthermore, the Procedural DBA must be ready to support all of these development methods . Therefore, the Procedural DBA should understand the traditional programming languages in use at their shop (such as COBOL, C, and Java), as well as procedural SQL.

In a heterogeneous environment, where more than one DBMS is used, the Procedural DBA needs to understand the methods for creating procedural objects in each of the DBMSs being used. There are many similarities between DB2 and Oracle triggers, for example, but there are also stark differences. The Procedural DBA group needs to be knowledgeable about these differences in order to support heterogeneous procedural database objects.

 <  Day Day Up  >  


DB2 Developers Guide
DB2 Developers Guide (5th Edition)
ISBN: 0672326132
EAN: 2147483647
Year: 2004
Pages: 388

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net