Introduction

There is a saying many IT architects and developers know all too well:

"The devil's in the details."

First, there's the business directive: provide decision makers and support professionals with the right information in the right way at the right time. It is simple enough to say, but seldom easy to do.

Second, there is the business reality. Rarely is the "right" information all in one place, stored in the same format, or even accessible through one application programming interface (API). Furthermore, the "right" presentation style often varies with the target audience -- executives may prefer dashboard-style displays that convey critical summary data at a glance, while customer service staff and financial analysts may prefer tables, spreadsheets, and complex charts that contain considerable detail. And finally, in today's fast-paced business culture, the "right" time often means immediate access to the latest information.

To be sure, there is no magic bullet. But a combination of two technologies -- enterprise information integration (EII) and inline analytics -- can help many IT organizations achieve these objectives more quickly than they may realize. Together, these technologies enable developers to rapidly assemble Web-based analytic applications that present an integrated view of disparate corporate data through interactive reports, charts, and graphics. These capabilities can easily be deployed as independent, Web-based applications or incorporated into existing Web applications to provide added value.

This article describes how IBM's DB2 Information Integrator (DB2 II) and DB2 Alphablox can be used together to develop business analysis components that integrate data from a variety of sources. Below is a quick overview of each of these products.

Back to top

Understanding DB2 Information Integrator

DB2 II provides users with a single-site image of disparate data that may be stored in different formats, retrieved using different APIs, and managed by different remote servers. Such servers may be owned by different lines of business within a single enterprise, by business partners, or by third parties providing services to the public. Programmers can work transparently with data from these various sources, relying on DB2 II's global query optimizer to select an efficient data access strategy for such work.

These capabilities are sometimes referred to as "federated data services." With such services, DB2 II supports access to a variety of relational DBMSs, spreadsheets, message queues, XML documents, Web content, Web services, and file systems. Another member of the DB2 II family, DB2 II Classic Federation for z/OS®, can be added (or used separately) to offer comparable access to mainframe data sources, such as VSAM, IMS™, CA-IDMS, CA-Datacom, and Adabas. Partner offerings provide DB2 II with access to other sources as well.

In addition to these federated data services, DB2 II also supports local caching of remote data (usually for performance reasons), replication of data between two or more sources, and local persistent storage of user data. However, this article focuses on its federated data services.

Back to top

Understanding DB2 Alphablox

DB2 Alphablox provides a set of analytic components and supporting services to make it easy to rapidly assemble analytic applications using Java™ Server Pages (JSP) tags. These components, known as "Blox" (as in "building blocks"), are based on the Java 2 Enterprise Edition (J2EE) architecture and are deployed on a Web application server. Visual Blox include highly interactive graphs, charts, and reports. These visual Blox work with data Blox to support analysis of both relational and multidimensional data.

DB2 Alphablox developers use Blox to connect to databases, retrieve information, and tailor the presentation to suit users' needs. Administrators deploy DB2 Alphablox applications into Web application servers, such as IBM's WebSphere® Application Server, BEA's® Weblogic, or the Apache Jakarta Project's Tomcat servlet engine.

Each of the major Blox enables users to interactively explore and analyze data. For example, users can export data to Adobe PDF files or Microsoft® Excel spreadsheets, hide columns in a report, create traffic-light style reports based on specified column values, alter the format of a displayed chart, and so on. The services Blox can be used to provide guided analysis, personalization, customization, and collaboration facilities. Blox users can customize these capabilities and services through JSP tags to provide the right function to a wide range of business users and analysts.

Back to top

Reference architecture

By now, you may have guessed how DB2 II and DB2 Alphablox can be used together to improve the productivity of Web application developers. DB2 II provides transparent access to a wide variety of data. DB2 Alphablox provides a sophisticated tag library to quickly access, retrieve, and present relational data to Web users. Together, these technologies extend existing Web applications or build new Web applications with reports, charts, and "dashboards" that give users an integrated view of disparate business data.

Figure 1 depicts a sample environment in which DB2 Alphablox is running on a J2EE Web application server and using DB2 II for accessing and integrating data from multiple sources.

DB2 II brings two key advantages to DB2 Alphablox users: it extends the reach of ready-made Blox components to many data sources not supported natively, and it minimizes the code required to retrieve, integrate, and present consolidated information to users.

Back to top

Getting started

Now that you understand what DB2 II and DB2 Alphablox can do, let's explore how you can get started. The following examples are based on my configurations, which include DB2 Alphablox V5.6, Tomcat V3.2.4, and DB2 II Advanced Edition V8.2.

It's best to set up and test each product individually before putting all the pieces together. For instance, configure DB2 II first and follow with DB2 Alphablox.

Configuring DB2 II

DB2 II requires no special configuration to work with DB2 Alphablox or Tomcat. Follow the standard process for installing and configuring the product, and verify that you can access the data important to your environment. For details, consult the online product manuals or InfoCenter, IBM Redbooks, or one of the online tutorials.

I created a number of DB2 II federated database objects to support my work. Generally, I like to write scripts to do this; you will find an example below. You can also launch DB2 II's administrative facility, the DB2 Control Center, and let wizards guide you through this process.

. . . 

CREATE WRAPPER "orca1" LIBRARY 'db2net8.dll';

CREATE SERVER ORACLESERVER TYPE ORACLE VERSION '8' 
WRAPPER "orca1" OPTIONS( ADD NODE 'leopard.ibm.com', PASSWORD 'Y');

CREATE USER MAPPING FOR "saracco" SERVER "ORACLESERVER" 
OPTIONS ( ADD REMOTE_AUTHID 'demo', 
ADD  REMOTE_PASSWORD 'demopwd') ;

CREATE NICKNAME BOBJECT1.CUSTOMER for 
ORACLESERVER.BOBJECT1.CUSTOMER;

CREATE NICKNAME BOBJECT1.PRODUCT for 
ORACLESERVER.BOBJECT1.PRODUCT;

. . . 

Whether you use wizards or write your own SQL, you need to create a few basic types of objects to get DB2 II to interface to a remote data source. For example, you must define wrappers for each type of data source you want to access. In other words, if you need to access three Oracle DBMS instances and one Teradata instance, you would define two wrappers: one for Oracle® and one for Teradata®. My sample script creates one wrapper for Oracle.

Next, you need to specify servers for each wrapper; these identify the location of the desired data, such as the host or TCP/IP name. My sample script creates one server for an Oracle DBMS on the "leopard.ibm.com" system.

In most production environments, administrators also define user mappings to translate a DB2 II user ID and password to a valid account on the remote data source. My sample script creates one user mapping for the "saracco" account; on the remote Oracle system, this ID will be mapped to "demo" with a password of "demopwd".

And finally, you need to create nicknames to identify collections of data (e.g., tables, views, files) of interest at each server. My script creates two nicknames -- one for Oracle's BOBJECT1.CUSTOMER table, and one for Oracle's BOBJECT1.PRODUCT table.

And, although I don't show it here, I also configured my DB2 II environment to access data in local XML files, a local MySQL DBMS, and a remote Microsoft SQL Server DBMS.

Configuring DB2 Alphablox

DB2 Alphablox requires little effort to work with DB2 II. We'll discuss what's needed shortly. However, DB2 Alphablox has certain prerequisites, including a specific version of the Java 2 platform and a supported Web application server. You must download, install, and test these prerequisites separately before you install DB2 Alphablox. If you're not sure what you need, consult the DB2 Alphablox documentation that ships with the product. I used J2SE (Java 2 Standard Edition) SDK V1.4.2 from Sun Microsystems and Tomcat V3.2.4 from Apache's Jakarta project.

Once you have the prerequisite software configured, follow the DB2 Alphablox documentation to install the product. During the installation process, you will be prompted to identify the location of JDBC drivers that you want DB2 Alphablox to use. Specify the DB2II_installation_root/java subdirectory.

Assuming the installation completes without error, you're ready to configure DB2 Alphablox to work with DB2 II. This involves creating a data source definition to point to your DB2 II federated database.

Launch the DB2 Alphablox Analytics Administrative Pages. You should see a panel similar to Figure 2.

Click on the Data Sources tab and elect to Create a new data source definition. Complete the form that's displayed. Figure 3 shows how I defined a "Cubetest" data source on my local DB2 II system; this data source pointed to the "cubetest" database in which I'd created wrappers, servers, user mappings, and nicknames for remote data. Note that I selected the DB2 Type 4 JDBC driver for this source.

Back to top

Creating a aimple report

With DB2 II and DB2 Alphablox installed and configured, you can now create server-side Java components that display reports, graphics, and other data for Web users. I will show you how to use a minimum number of Blox tags to create a simple "relational" report. Actually, this report is not based on relational data at all. The SQL statement references a DB2 II nickname I defined for an XML file. But because DB2 II presents a single-site, virtual database image of disparate data to programmers, I could use DB2 Alphablox's relational Blox tags for my work.

DB2 Alphablox ships with a number of sample JSPs that you can modify to get started. I modified a simple relational reporting sample to create the report shown in Figure 4.

As I mentioned earlier, DB2 II makes local and remote data of many types appear to be local tables within a single relational database. For example, the real data source for the report shown in Figure 4 is an XML file; I've shown part of its contents in Figure 5. In my environment, this XML file is one of several critical data sources for tracking information related to products. This file is mapped to a DB2 II nickname of CUSTCOMMENT. In a minute, you will see me reference that nickname in a SQL statement that I provide to a Blox tag to create the report in Figure 4.

For my simple report, I created the JSP page shown in Figure 6. It contains a small number of available DB2 Alphablox tags for relational reporting, including ReportBlox, SQLDataBlox, and DataSourceConnectionBlox. ReportBlox (beginning with "bloxreport:report") wraps all the other Blox used for retrieving, manipulating, and formatting a report. SQLDataBlox ("bloxreport:sqlData") specifies the SQL query to be executed. For my simple report, I selected the entire contents of CUSTCOMMENT, the DB2 II nickname for the XML file in Figure 5. Finally, the DataSourceBlox ("bloxreport:dataSourceName") specifies the data source on which to execute the SQL query. Note that I reference "Cubetest" here. This is the data source for my DB2 II database that I created when configuring DB2 Alphablox earlier.

Of course, there is a lot more I could have done with this JSP to tailor the format of the report, handle errors in a reasonable fashion, and perform other functions. But I wanted to show how existing Blox can easily reference DB2 II objects. This enables you to create reports for a wide variety of data sources not natively supported by DB2 Alphablox without additional programming effort.

Back to top

Integrating disparate data with Blox

Now that you've seen how DB2 II can be integrated with Blox components, let's consider a more aggressive scenario. In this section, I'll show you how I used Blox to build an interactive JSP that uses DB2 II to integrate Oracle and XML data, displaying the results in a table ("grid") and a pie chart, both of which a Web user can customize. Figure 7 shows the output of the JSP we will review.

One way to build this is to use PresentBlox, a DB2 Alphablox component that combines the functions of several other Blox, including those for charts, grids (tables), toolbars, and so on. I used DB2 Alphablox's DHTML Query Builder to generate this Blox code for me. Then I pasted this code into a JSP skeleton I created.

You can launch the DHTML Query Builder from DB2 Alphablox's administrative console. Click on the Administration tab, select Assembly and then Workbench. Launch the DHTML Query Builder and establish a connection to a valid data source, as shown in Figure 8.

Next, write and execute your query. Mine joined Oracle and XML data to report the number of customer comments received for each product. Executing the query will cause a tabular report and a chart to be displayed, as shown in Figure 9. I altered the default chart type to display a pie chart.

Once you're satisfied with the display, click the Generate Blox Tag link in the PresentBlox display. In Figure 9, it is just above the pie chart to the right. This will cause a new window to display that includes Blox source code for the table, grid, and toolbar shown in the PresentBlox output. You can cut and paste this into a JSP page. Figure 10 shows my source code. The code delimited by "blox:present" tags was generated by the DHTML Query Builder.

It is worth considering how this same task might be approached without DB2 II. One way involves developing multiple Blox-based JSPs, each of which would connect to a different data source and issue SQL statements specific to those sources to retrieve the correct data. This will work if all of your data sources are natively supported through DB2 Alphablox. Unfortunately, none of mine are.

Moreover, developing multiple separate JSPs, or even different reports within a single JSP, places the burden of integrating the data on the user. For complex business analysis applications requiring sophisticated correlations, aggregations, and joins, this is rarely acceptable.

Another alternative is to write JavaBeans or even Java scriptlets to manually connect to each data source, retrieve the necessary data using native APIs, and perform any joins, unions, aggregations, or other functions internally to create a single, consolidated result set. This result set could then form the basis for a single grid and/or chart.

Unfortunately, such work is typically complex and error-prone. It requires programmers to cope with data type translations, compensate for differences between various back-end data sources, and hard-code data access strategies into their components. The latter can be particularly challenging; to determine an efficient access strategy, developers need to consider available access methods on each back-end source, how data values are skewed in different tables or files, how much data resides in tables or files of interest, and so on.

Because DB2 II performs these functions transparently, programmers do not need to hand-code such work. Indeed, studies have shown that using DB2 II for Java development projects that require integrated access to disparate data can reduce coding requirements by 40 to 65% and shorten development cycles considerably.

Back to top

Summary

Using DB2 II and DB2 Alphablox enables you to quickly create server-side Java components for integrated business analysis activities. DB2 II provides a single-site image of disparate data, while DB2 Alphablox offers building blocks ("Blox") for creating complex reports that users can display and customize through their Web browsers. Together, these products enable IT professionals to build reports that join and consolidate data from many critical corporate data sources, including relational DBMSs, non-relational DBMSs, Web services, XML files, mainframe sources, and others.

This article described a reference architecture for combining DB2 II and DB2 Alphablox, as well as provided some simple coding examples to help you get started. You will find more information about DB2 II and DB2 Alphablox in the resource list at the end of this article.

Acknowledgements

Thanks to Gary Robinson for his comments on this article.

Resources

• Bruno, Paolo, and Francis Amaudies, Amanda Bennett, Susanne Englert, Gerhard Keplinger, Data Integration with DB2 Information Integrator V8.1, IBM Redbook, IBM publication number SG24-7052-00, October 2003.
  
  
• Developing Analytic Applications with DB2 Alphablox, IBM technical white paper, IBM publication number G507-1010-00, July 2004.
  
  
• Inside IBM DB2 Alphablox: An In-Depth Technical View of IBM DB2 Alphablox, IBM technical white paper, IBM publication number G507-1012-00, July 2004.
  
  
• Saracco, C. M. Coping with Disparate Data in Web Applications, IBM developerWorks, August 2002.
  
  
• Saracco, C. M. and Jacques Labrie, Julien Muller. Building Portals with Enterprise Information Integration Technology, IBM developerWorks, March 2004.
  
  
• Saracco, C. M., and Susanne Englert, Ingmar Gebert. Using DB2 Information Integrator for J2EE Development: A Cost/Benefit Analysis, IBM developerWorks, May 2003.
  
  
• Saracco, C. M and T. F. Rieger. Accessing Federated Databases from Application Server Components, IBM developerWorks, February 2003.
  
  

About the author

Comments (Undergoing maintenance)

Back to top

Trademarks  |  My developerWorks terms and conditions

Table of contents

• Introduction
• Understanding DB2 Information Integrator
• Understanding DB2 Alphablox
• Reference architecture
• Getting started
• Creating a aimple report
• Integrating disparate data with Blox
• Summary
• Resources
• About the author
• Comments

Next steps from IBM

• Try: DB2 Enterprise
• Demo: DB2 serving ATOM feed
• Webcast: A smarter way to successful SOA - Information as a trusted strategic asset
• Briefing: Information on Demand
• Buy: DB2 Enterprise

My developerWorks community

Dig deeper into Information Management on developerWorks

DB2 pureScale

See how true transparent scaling architecture can be achieved on distributed platforms

Special offers