June 05, 2012

The Blurry Line Between DBAs and Developers

While DB2 for z/OS is the focus of this blog, I feel it's important to occasionally look at DB2 for LUW. More and more, mainframe DBAs must support DB2 for LUW in addition to DB2 for z/OS. As part of this mainframe DBA's own education, I've been reading the "IBM DB2 9.7 Advanced Application Developers Cookbook."

As the title spells out, this book is written for application developers who work with DB2 for LUW. Here's the chapter list:

Chapter 1: Application Development Enhancements in DB2 9.7

Chapter 2: DB2 Application Techniques

Chapter 3: General Application Design

Chapter 4: Procedures, Functions, Triggers, and Modules

Chapter 5: Designing Java Applications

Chapter 6: DB2 9.7 Application Enablement

Chapter 7: Advanced DB2 Application Features and Practices

Chapter 8: Preparing and Monitoring Database Applications

Chapter 9: Advanced Performance Tuning Tips

DBAs aren't really the primary audience here. This book is written by developers for developers. Nevertheless, many of the examples are intermixed with tasks that I would normally associate with a DBA's responsibilities. As a DBA myself, I expect a formal separation of duties, so I find this problematic.

Here's an example of what I mean: The first chapter features a discussion of how to alter and rename a column on a table. But the title of Chapter 1 is "Application Development Enhancements in DB2 9.7." That surprised me, because I consider DDL operations to be a DBA's responsibility.

On the other hand, I also realize that developers these days don't want to rely on DBAs to create tables or change table structures. And certainly a DBA's skillset isn't required to alter a table, only the authority to issue the ALTER TABLE RENAME COLUMN command. Continued improvements in database technology also blur the lines between DBAs and developers. For instance, as of DB2 9.7, the once-complicated task of altering a table can be accomplished through a single command:

 DB2 ALTER TABLE schema.tabname RENAME loc TO location

The final few chapters of the book examine application performance. I credit the author for covering this, because I believe many developers don't take the time to understand SQL coding techniques. This underscores my point about the respective duties of DBAs and developers as well.

Developers, understandably, are focused on understanding the language and APIs they work with. They don't have time to learn everything about databases, whether it's DB2 for z/OS or DB2 for LUW. And they don't necessarily understand physical table and index design options, and how even minor design changes can impact application performance. That's why I think it's important that DBA and developer tasks are strictly defined.

That said, developers can greatly benefit by learning the performance basics outlined in this book. For instance, if you're coding SQL statements, Chapter 9 ("Advanced Performance Tuning Tips") can help you. In addition to providing general rules for writing more efficient SQL statements, this chapter explains predicates in an SQL statement and offers ways to improve INSERT performance.

Despite my differences with the book's organization, I think the author does a great job of providing real-world examples that illustrate how to implement new DB2 9.7 features. There are also instructions for downloading sample code.

Chapter 3 offers a valuable look at how to take advantage of DB2's built-in encrypt/decrypt functions. I'll also highlight Chapter 6, which includes several sections dedicated to running Oracle application on DB2 for LUW. The first section discusses how to enable DB2 to run in Oracle compatibility mode. Other sub-chapters discuss using ROWNUM, support for PL/SQL exception handling and detailed steps on using IBM Migration Toolkit as well as Data Movement Tool to migrate a 4-5 terabyte Oracle database to DB2 for LUW in just two days. Finally, there's a chapter dedicated to porting multi-action triggers.

Any developers who've read this book, I'm interested in your thoughts. Please post a comment or send me a message.