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.