DBAs Must Understand Application Development Issues
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 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.
Furthermore, the DBA needs to be the champion of SQL. This means that programmers should be encourage 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.
One of the most popular SQL APIs 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.
Of course, these we are discussing issues here merely at the interface level. 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. If that is not the case, that DBA is not likely to be overly effective at their job.
© 2005, Mullins Consulting, Inc.
