Some folks think that DBAs and application developers inhabit different universes. At times this may seem true, but the successful DBA must understand application development and the issues involved in programming and design. Although database administrators usually are viewed as system “folk,” they most definitely must be tied into the application development and design teams within their organization.
Application code is written to access data in the database and, as such, the DBA better 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.
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 things to be mastered, though, must be thinking relationally (instead of the “flat file” mentality) and a sound understanding of SQL development technique.
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/programmer to rebind the application code to the DBMS.
But, there is no magic. Both the DBA and the developers need to ensure that appropriate procedures are in place to collect database statistics so that the optimizer will be able to formulate appropriately efficient access paths to the data.
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.
Furthermore, the DBA needs to be the champion of SQL. This means that 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 how the data is accessed. 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, and SQLJ.
One popular SQL API is ODBC (Open Database Connectivity). Instead of directly embedding SQL in the program, ODBC uses callable routines. ODBC provides routines to allocate and deallocate resources, control connections to the database, execute SQL statements, obtain diagnostic information, control transaction termination, and obtain information about the implementation. ODBC is basically a Call-Level Interface (CLI) for interacting with databases. The CLI issues SQL statements against the database using procedure calls instead of via direct embedded SQL statements. ODBC relies on “drivers,” which are optimized ODBC interfaces for a particular DBMS implementation. Programs can make use of the ODBC drivers to communicate with any ODBC-compliant database. The ODBC drivers enable a standard set of SQL statements in any Windows application to be translated into commands recognized by a remote SQL compliant database.
Another popular SQL API is JDBC (Java Database Connectivity). JDBC enables Java to access relational databases. Similar to ODBC, JDBC consists of a set of classes and interfaces that can be used to access relational data. There are several types of JDBC middleware, including the JDBC-to-ODBC bridge, as well as direct JDBC connectivity to the relational database. Anyone familiar with application programming and ODBC (or any call-level interface) can get up and running with JDBC quickly.
Another way to access DB2 data from a Java program is using SQLJ. SQLJ enables developers to directly embed SQL statements in Java programs. SQLJ provides static SQL support to Java. Developers can embed SQL statements into Java, and a precompiler is used to translate SQL into Java code. Then the Java program is compiled into bytecodes, and a database bind operation creates packaged access routines for the SQL.
And there are still many COBOL programmers writing SQL statements embedded directly into their code, which requires pre-compiling to “grab” the SQL out of the program source and then binding it into executable packages.
Of course, applications also interface with other types of system software. Application servers, transaction servers, message queueing 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. And every DBA should at least be able to read and understand every programming language that is being used to access the databases they administer. If you are a DBA and you do not possess such skills, start lobbying management today to obtain them. For example, maybe you can code in COBOL and Assmebler, but have no Java skills…
The bottom line is this: without programming skills, DBAs are not likely to be very effective at their job.
Data and Technology Today 
Craig, I see your point of view, but didnt we try this before ? what I mean is there was a push for developers to write good code, and the DBAs would open up there databases with good connection types… and where are we… the DBA is still the one who has to bend.
Data is now king, always has. But with the advent of Project Zero, Hibernate, iBatis, and such, now developers have this abstraction layer that will write their SQL for them. So that begs the question: is it still the DBA who has to learn programing now that the developers dont have to know anything about SQL or even how to write queries ?
I think if the enterprise goes with this data / app abstraction layer over JDBC connection, then let the DBA focus on what is really important, model the data correctly, make sure it is accessible, and keep those servers running smoothly.
Sorry, I had to vent there, to many times I have sat across from the table of Development, and they say it is our problem, when in fact, it is the CODE that is the issue. So glad companies like ORCL and IBM have offerings to prove out code is the issue, or even the application server is the issue, or in some cases 😛 the Data Server is not tuned correctly.
thanks for sharing, I look forward to more of your posts… and hope you dont mind me venting for the DBAs… they are good people, they work miracles, and are the unsung heros of most enterprise environments… 🙂
You make a lot of very good points. My posting was not meant to disparage DBAs, but to point out that knowledge of the development languages (and, yes, environments) that are being used to access the database is needed if you hope to be able to understand what might be wrong. A DBA saying it is the code without understanding the code is just as problematic as a programmer saying it is the database without understanding the DBMS…
That said, I absolutely agree that DBAs are the hard-working unsung heroes of most IT shops.
Craig, good that we agree on the DBA role.
Tools like Optim (specifically Data Developer, and the Performance Expert / Extended Inight, with Query Tuner) will only help the DBAs speak the language of the developers..
While at learning about the Optim Suite, it is a good foundation that the leadership in Data Management understand the need for DBAs to be part of the development process, if not propose suggested changes. While you are correct that the “DBAs are the keeper of the SQL” tools like Optim will only make it easier for them to find, monitor, fix and suggest to better tune the queries.
I apologize if I came across harshly, but to many of my customers are facing the up hill battle, where applications are still considered the end all say all, but it is really the data that the business needs, the right data at the right time, for the right reason… and that is why I am starting to see a new trend, where Data is now the Asset, and the applications are the tools… 🙂
Looking forward to reading your other posts, and thanks for letting share my POV.
-Garnie
Glad you took the time to share your thoughts on the matter. The more comments we can get on the blog the more knowledge we’ll gain and the more points of view we can all benefit from hearing… so keep the comments and thoughts coming!
Pingback: Tweets that mention DBAs Must Understand Application Development | Data and Technology Today -- Topsy.com
i’ve been a programmer in the past, and that sure helps: but you can’t know everything. we interact with: Cobol, IMS, .NET, JAVA, VB6, c#, QMF and a range of software from different vendors. The best help we have is tooling that gives us (a hint) of the culprit: this SQL statement from that server is causing this.
Yes, of course, you cannot know everything. But when your shop is of a medium size or larger then you’ll probably have more than one DBA. It can be a good course of action to staff up with DBAs having diverse backgrounds. Perhaps one DBA has COBOL and IMS background; another has Java and C#; and maybe a third VB and .NET… QMF? That is easy.
Knowing the basics of several programming languages has helped me a lot, both in tuning the system as in giving them performance tips.
But it’s a two-way street.
When a java-developer came to ask me if we could reboot the mainframe, because that probably would solve their sudden performance degradation, I simply gave up.
Thanks for all the wonderful information it was great !
Just want to say what a great blog you got here!
I’ve been around for quite a lot of time, but finally decided to show my appreciation of your work!
Thumbs up, and keep it going!
Cheers
Christian, iwspo.net
Pingback: Future Work by the Indigenous Youth Caucus | Watch Movies | Educational Rhode Island