Database-Oriented Middleware and Application Integration

To a large extent, application integration depends upon database access. This is particularly true for data-oriented application integration. Databases were once proprietary and therefore difficult to access. Now, so many solutions for accessing data exist that we rarely have a problem when we need to retrieve information from or place it into any database. The solutions not only make application integration a much easier proposition, but they also speak directly to the idea that the capability of modern middleware drives the interest in application integration.

However, even with many simplified database access solutions, databases and database-oriented middleware quickly grow complicated. Although database-oriented middleware was once simply a mechanism to "get at" data, it has matured into a layer for placing data in the context of a virtual database a particular, common database model or format. For example, if we want to view data in a relational database as objects, the database-oriented middleware can map the data so it appears as objects to a source or target application. The same thing can be done "the other way around" mixing and matching such models as hierarchical, flat files, multidimensional, relational, and object-oriented (see Figure 7.15).

Figure 7.15. Database-oriented middleware allows viewing of data using any model, no matter how the data is stored.

Database-oriented middleware also provides access to any number of databases, regardless of the model employed or the platform upon which they exist. This access is generally accomplished through a single common interface such as ODBC or JDBC, which we will discuss in detail later in this section. Thus, information stored in Oracle, Informix, or DB2 databases can be accessed at the same time through a single interface (see Figure 7.16). By taking advantage of these mechanisms, we can map any difference in the source and target databases to a common model. As a consequence, they are much easier to integrate. (This process also supports the idea of a common enterprise metadata model, presented earlier in this book.)

Figure 7.16. Database-oriented middleware provides access to a number of databases at the same time.

These examples should make it clear that database-oriented middleware is a major player in the world of application integration. It allows a large number of enabling technologies to process information coming to and going from source and target systems. This ability makes database-oriented middleware the logical choice if a message broker or an application server requires information in a database. Vendors also recognize the advantage of database-oriented middleware. As a result, many application integration products, such as message brokers and application servers, already contain the necessary database-oriented middleware to access the most popular databases. In fact, most message brokers and application servers come prepackaged with the appropriate adapters to access most relational databases, such as Oracle, Sybase, and Informix. To a large degree, database access is now a "problem solved," with many inexpensive and proven solutions available.

Nevertheless, we can benefit from understanding the role of database-oriented middleware in the context of application integration in order to get the larger picture. Databases will serve as the primary point of integration for most application integration solutions over the next few years. The bottom line: Your choice of mechanism to move information in and out of databases can make or break your application integration project. That alone makes our examination of database-oriented middleware worthwhile. More than that, integration with more modern middleware solutions is essential and carries with it its own complexities and opportunities.

What Is Database-Oriented Middleware?

Database-oriented middleware provides a number of important benefits (see Figure 7.17), including:

• An interface to an application

• The ability to convert the application language into something understandable by the target database (e.g., SQL)

• The ability to send a query to a database over a network

• The ability to process a query on the target database

• The ability to move a response set (the results of the query) back over the network to the requesting application

• The ability to convert a response set into a format understandable by the requesting application

Figure 7.17. Functions of database-oriented middleware.

In addition to these processes, database-oriented middleware must provide the ability to process many simultaneous requests, along with scaling features such as thread pooling and load balancing. These features must be packaged with management capabilities and security features. As in other contexts, vendor approaches to these benefits vary greatly from vendor to vendor and technology to technology.

Types of Database-Oriented Middleware

Again, I have no desire to repeat information that is easily available from other sources. My goal is to provide you with an overview of information that applies to your application integration problem domain.

So, where do we begin? Ultimately, database-oriented middleware is "all the software that connects an application to a database." Any application. Any database. Anytime. Like primitive middleware layers, database-oriented middleware allows developers to access the resources of another computer in this case, a database server that uses a single, well-defined API. Database-oriented middleware appears straightforward in its architecture, yet each of the many products and standards that make up this market accomplish the basic task in very different ways.

Although several types of database middleware exist, they are all basically native middleware CLIs and database gateways. For our purposes, native middleware is simply middleware created for a specific database. For example, middleware provided by Sybase to access the Sybase databases from C++ is native database-oriented middleware. Native database-oriented middleware provides the best performance and access to native database features (such as stored procedures and triggers), because the middleware was specifically created for that particular database. The downside to native database middleware is that, once links to a database have been created using native middleware, major renovations are required to change databases.

CLIs, such as ODBC and JDBC, provide a single interface to several databases. CLIs translate common interface calls into any number of database dialects. They also translate the response sets into a common response set representation (see Figure 7.18) that is understandable to the application making the request to the database.

Figure 7.18. CLIs use a single common interface to access several different databases.

Database gateways provide access to data that was once locked inside larger systems, such as mainframes. They integrate several databases for access from a single application interface. They remap archaic database models (flat files, ISAM, VSAM, and so on) so they appear more traditional, and they translate queries and information as they move in and out of the database gateway software (more on this in "Database Gateways" later in this section).

Going Native

In addition to ODBC, JDBC, and other database translation interfaces, many other native database-oriented middleware products exist. These are APIs provided by a database vendor or some third party with access to a particular database. In the past, these APIs tended to be older C and C++ libraries. Now, most application integration development tools ship native database-oriented middleware with their products.

Native database-oriented middleware has an advantage over ODBC or JDBC in its ability to provide high-performance database access along with the ability to access features native to a specific database. That's the upside. The downside is that native database-oriented middleware binds the user to that particular middleware vendor, because the application integration application uses calls specific to that particular database.

Database Gateways

Database gateways (also known as SQL gateways) are APIs that use a single interface to provide access to most databases that reside on many different types of platforms (see Figure 7.19). They are similar to virtual database middleware products, providing developers with access to any number of databases residing in environments typically difficult to access, such as a mainframe. For example, using an ODBC interface and a database gateway, developers can access data that resides in a DB2 database on a mainframe, in an Oracle database running on a minicomputer, and in a Sybase database running on a UNIX server. The developer simply makes an API call, and the database gateway does all the work.

Figure 7.19. Database gateway.

Database gateways translate the SQL calls into a standard format known as the Format and Protocol (FAP), the common connection between the client and the server. FAP is also the common link between very different databases and platforms. The gateway can translate the API call directly into FAP, moving the request to the target database, and translating the request so that the target database and platform can react.

A number of gateways are on the market. These include Information Builders' Enterprise Data Access/SQL (EDA/SQL) and standards such as IBM's Distributed Relational Data Access (DRDA) and ISO/SAG's Remote Data Access (RDA).

EDA/SQL

EDA/SQL is a wonderful general-purpose database gateway for several reasons. Among them is its ability to work with most database servers and platforms, bridging many enterprises where dozens of servers might be running on dozens of different platforms, all needing to be accessed from a single application a perfect fit for application integration. EDA/SQL also has the advantage of using ODBC as the interface rather than a proprietary API. By using ODBC, it can access more than 50 relational and nonrelational database servers.

There are several EDA/SQL components, including the API/SQL, EDA/Extenders, EDA/Link, EDA/Server, and EDA/Data Drivers. API/SQL provides the CLI (ODBC), which allows the developer to access the EDA/SQL resources. EDA/Extenders are redirectors of SQL calls, which route the request across a network. EDA/Link provides the network connections by supporting more than 12 communication protocols. EDA/Server resides on the target database, processing the requests on behalf of the requesting application or integration server. Finally, the EDA/Data Drivers, like ODBC drivers, provide access to more than 50 different target databases.

Ready for Prime Time

The strongest point in support of database-oriented middleware is that the technology is very mature, well tested, and ready for most application integration applications. In other words, database access should not be a major concern for most application integration projects.

Among the problems that remain to be solved for database-oriented middleware is the ability to make it more scalable. Currently, a TP monitor or application server must be employed to multiplex the database connections on behalf of the application or application integration solution. Multiplexing (or connection pooling) the ability to remove the one-connection-per-request restriction from database-oriented middleware is becoming part of many database-oriented middleware layers, including JDBC and ODBC.

With renewed interest in nonrelational database models (such as multidimensional, hierarchical, and object-oriented), middleware is learning how to emulate and translate data from model to model. Today we can view a relational database using the object-oriented model and a hierarchical database as a relational database. These emulation and translation services make application integration a much easier proposition because they make it possible to map very heterogeneous environments to a common database model, thus providing an easier starting point for integration. This capability adds the most value to data-oriented application integration.

The world of database-oriented middleware isn't about to undergo drastic change even as application integration grows in popularity. The simple fact is that solutions to most database access problems already exist.

Isn't it nice to come upon an area of technology where most problems are already solved?