©  2021  Mullins Consulting, Inc.   All Rights Reserved   Privacy Policy   Contact Us

The DBA’s Guide to Application Development

by Craig S. Mullins

Some folks think that DBAs and application developers inhabit different universes.

At times this may seem to be the case, but the successful DBA must understand application development and the issues involved in programming and design. Although DBAs are usually are viewed as system "folk," they most definitely must be tied into the application development and design projects of their organization. Application code is written to access data in the database; the DBA must have a sound understanding of how that is happening, as well as ways to improve it.

Application design includes database concerns such as interfacing SQL with traditional programming languages and the type of SQL to use. But every aspect of program coding will affect the usability and effectiveness of the application. Furthermore, each application program must be designed to ensure the integrity of the data it modifies – and that means understanding transaction integrity and the concept of “unit of work” and appropriate COMMIT logic. Not only does this impact data integrity, but the scope of the unit of work can have a significant impact on the ability for other concurrent workloads to access (or modify) the same data. And that can impede performance.

Designing a Proper Database Application System

Designing a proper database application system is a complex and time-consuming task. The choices made during application design will impact the usefulness of the final delivered application. An improperly designed and coded application may need to be redesigned and re-coded from scratch if it is inefficient, ineffective, or not easy to use. To properly design an application that relies on databases for persistent data storage, the system designer must match the application development languages and tools to the physical database design and the functionality of the DBMS being used. The first thing to be mastered, though, must be a sound understanding of SQL.

SQL is coded without embedded data-navigational instructions. The DBMS analyzes each SQL statement and formulates data-navigational instructions "behind the scenes." The DBMS understands the state of the data it stores, and so it can produce efficient and dynamic access paths to the data. The result is that SQL, used properly, provides a quicker application development and prototyping environment than is available with corresponding high-level languages. Furthermore, the DBMS can change access paths for SQL queries as the data characteristics and access patterns change, all without requiring the actual SQL to be changed in any way. Of course, doing so can require action on the part of the DBA to rebind the application code to the DBMS.

SQL sometimes can get very complex. DBAs are needed to help unravel the complexity and assure that the SQL is written as effectively as possible. Although programmers should be able to examine plan table or show plan information, the nature of doing so often falls to the DBA… especially in a production environment.

The DBA Needs to be the Champion of SQL and Understand the “Framework” Being Deployed

The DBA needs to be the champion of SQL. Programmers should be encouraged to do the work in the SQL, instead of breaking it apart and putting it into host language code. By putting the work into the SQL, the DBMS can control access paths. When the volume or nature of the data changes significantly all that is required to access the data differently is re-optimizing the SQL using DBMS commands. If the work instead is in the program code, a programmer would have to re-write the code to get the access paths to change... and who among us really believes that will ever happen?

Additionally, application programs require an interface for issuing SQL to access or modify data. The interface is used to embed SQL statements in a host programming language (such as COBOL, Java, C, or Visual Basic). Standard interfaces enable application programs to access databases using SQL. There are several popular standard interfaces, or APIs (Application Programming Interfaces), for database programming, including ODBC, JDBC, SQLJ, and OLE DB. DBAs need to understand these APIs and how they are being used to develop database applications in their shops.

The DBA also needs to understand the “framework” being deployed. For the Java programmer, J2EE offers a set of coordinated specifications and practices that together enable solutions for developing, deploying, and managing multitier enterprise applications. The J2EE platform simplifies enterprise applications by basing them on standardized, modular components. J2EE provides a complete set of services to those components and handles many details of application construction without requiring complex programming. So J2EE is not exactly a software framework, but a set of specifications, each of which dictates how various J2EE functions must operate.

And then there is the Microsoft .NET framework, which provides a comprehensive development platform for the construction, deployment, and management of applications. The .NET framework provides CLR (common language run time) and a class library for building components using a common foundation. From a data perspective, the most important component the .NET framework is ADO.NET, which provides access to data sources such as a database management system. ADO.NET enables .NET developers to interact with data in standard, structured, and predominantly disconnected ways.

A Good DBA Should Know ...

Of course, there are additional development issues outside the application interface level that DBAs need to be aware of. Applications also interface with other types of system software. Application servers, transaction servers, message queuing software, and the like can complicate the development cycle - and interfere with performance. A good DBA will have an understanding of how this software interacts with the DBMS and be able to lend guidance to the application development team.

Now, I am not saying that the DBA has to be a whiz-bang programmer, but he or she should at least have done the job in the past - and be comfortable interacting with those currently doing the job. If that is not the case, that DBA is not likely to be overly effective at their job.

From Database Trends and Applications, October 2013.

© 2013 Craig S. Mullins,  

October 2013