The DBA and Application Design

Application design is more than just writing efficient database requests in application programs. Of course, application design includes database concerns such as interfacing SQL with traditional programming languages and the type of SQL to use. But every aspect of the way the program is coded 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, of course, performance must be treated as a design issue.

Many organizations erroneously assume that the DBA should not be involved in application design. The common line of thought goes something like this: “DBAs manage the database software and programmers manage applications so I don’t really want DBAs mucking around with programs.” Maybe this would work in some perfect world where all programmers understood the DBMS well enough to program efficient database access – but not in this world.

The DBA must promote the concept of designing applications with an understanding of the database at the forefront. It is unacceptable to allow programmers to design and code applications without considering how the programs will perform as they interact with databases. The uninformed will approach database application development with no proactive performance engineering – the assumption being that any performance problems can be resolved after development by the DBA. But it may be impossible to tune an improperly designed application program without rewriting it. So why not write it correctly the first time? 

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. Indeed, 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. In order to properly design an application that relies on databases for persistent data storage the system designer at a minimum will need to understand the following issues:

·         How data is stored in a relational database

·         How to code SQL statements to access and modify data in the database

·         How SQL differs from traditional programming languages

·         How to embed SQL statements into a host programming language

·         How to optimize database access by changing SQL and indexes

·         Programming methods to avoid potential database processing problems

In general, the developer 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 different than most high-level programming languages.

SQL is designed such that programmers specify what data is needed – not how to retrieve it. That is, 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.

Most programmers are accustomed to hard-wiring data navigation instructions into their programs. Making the switch to SQL can be troublesome without some training. At any rate, programmers will need to be trained in these high-level differences between non-database and database programming techniques. This job falls to the DBA. Of course, there are many more details at a lower level that the database programmer needs to know, such as SQL syntax, debugging and testing methods, optimization techniques, and program preparation procedures (e.g. compilation, bind, etc.).

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, which stands for 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, the DBA will need to clearly describe the database interfaces to that are available for the programming languages in use in their organization. And the DBA must provide proper guidance and training such that the correct interface is used. Performance can vary greatly for different interfaces and versions due to issues such as dynamic versus static SQL support, optimization of the interface, and the use of proper compilation and binding parameters and techniques.

Summary

Of course, these are just a few of the issues where the DBA can help to improve application design. Others include publishing up-to-date SQL coding techniques and tips, ensuring proper transaction coding including database commit frequency, interaction with TP systems and middleware, and coding to minimize locking problems. Application design and development is the core responsibility of systems analysts and application programmers, but the DBA must be involved in the process when programs are being written to access databases.