Skip to main content

skip to main content

developerWorks  >  Information Management | WebSphere  >

Extending Portals with DB2 Information Integrator

developerWorks
Document options

Document options requiring JavaScript are not displayed


Rate this page

Help us improve this content


Level: Introductory

C. M. Saracco, Senior Software Engineer, IBM　

19 Feb 2004

Using Enterprise Information Integration (EII) technology with Web portals can extend the reach of pre-built components and make it easier to develop custom components. In this article, we'll explain why as well as show you how to get off to a quick start.

The growing use of enterprise portals is forcing many developers to confront a decades-old technology challenge: how to present business users with a coherent, consolidated view of critical business data that is dispersed throughout their enterprise. A simplistic option -- using different areas of a Web page to display content from different data sources -- isn't always sufficient because it can place the burden of correlating and aggregating this data on the user. Hand-coding a portal application to integrate all the necessary data and present a consolidated view is possible but often costly.

Is there a better alternative? This article describes how combining Enterprise Information Integration (EII) technology found in IBM® DB2® Information Integrator (DB2 II) with portal development, runtime, and deployment services found in IBM WebSphere® Portal and WebSphere Studio Portal Toolkit can provide a viable solution. Together, these offerings can cut programming costs and shorten development cycles for portal applications that need to consolidate and integrate data from multiple sources to support business analysis activities. This article discusses why as well as how to get started.

Business benefit

Pairing DB2 II with WebSphere Portal development projects can minimize -- or even eliminate -- the need to write custom code to present a single, unified view of disparate data. This is because DB2 II's federated data services, which we'll discuss shortly, transparently extends the reach of ready-made portal application components originally designed to work with one data source at a time. DB2 II presents a virtual database image to applications and end users, enabling portal components to connect to DB2 II and access a wide variety of data as though it were all available locally. Indeed, later in this article you'll see just how to do that. You'll learn how to extend IBM My Query Reports and JDBC Business Object Builder components to access and integrate data from multiple sources into a single report, all without writing any Java code.

You'll probably agree that anytime you can reuse software, you'll save time and money. After all, it's rarely cheap to design, test, code, and maintain new applications or software components. But what if your needs aren't readily satisfied with pre-built software? DB2 II can simplify your custom development efforts as well. This is because you can delegate many tedious and expensive tasks to DB2 II. Such tasks include connecting to various data sources, retrieving appropriate data using source-specific APIs, handling data type translations, compensating for capabilities not supported by a given data source, and performing joins, unions, aggregations, and other data-related functions as needed.

Furthermore, DB2 II relieves you from determining and implementing an efficient data access strategy for retrieving disparate data. This is particularly important, as a poor data access strategy can lead to poor performance. If you were to develop your own data access strategy, you'd need to consider issues such as what data can (and should) be filtered at each data source, the usefulness of available access methods (such as indexes) at each data source, the manner in which data of interest is distributed (or skewed towards certain values) at each source, the quantity of data likely to be retrieved from each source, the appropriate techniques for joining or correlating data retrieved from different sources, and so on.

The DB2 II global query optimizer automatically considers issues such as these to develop an efficient access strategy for your query. Of course, data management staff should undertake reasonable administrative efforts, such as maintaining current statistics about remote data sources, defining appropriate views that join and union remote data, using materialized query tables (MQTs) when appropriate to cache remote data, and so on. But this is the kind of work that database administrators have been doing for years when managing individual systems. Rather than force portal application programmers to dedicate considerable effort to performance tuning issues, a DB2 II-based architecture offloads this work: much is handled automatically by the query optimizer, and important tuning activities are undertaken by data management staff with the appropriate skills.

Thus, using EII technology available with DB2 II can reduce portal application development costs, speed time to delivery of new applications, extend the reach of existing portal components to a wide variety of data sources (including mainframe sources), and minimize the skills required for portal programmers and administrators to build and deploy applications that provide sophisticated data integration capabilities. Indeed, the combination of DB2 II and WebSphere Portal can help firms develop portal-based "executive dashboards" for integrated business analysis. Many business intelligence software vendors have already partnered with IBM to support DB2 II, and some are offering ready-made portal application components to enable developers to quickly build sophisticated reports for drill-down analysis of key business problems. Again, hand-coding this capability can be cumbersome and costly.



Back to top


Technology tutorial

To help you understand how DB2 II and WebSphere Portal can be paired to realize the benefits just discussed, we'll give you a quick tutorial of key aspects of their underlying technologies.

Understanding DB2 Information Integrator

DB2 II provides programmers with a single-site image of disparate data that may be maintained in different formats, retrieved via different application programming interfaces (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 the DB2 II 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, IMSTM, 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.

Understanding WebSphere Portal

WebSphere Portal provides users with an extensible framework and key infrastructure services for deploying portals -- Web sites that give users a single access point to a variety of Web-based resources. Such Web sites may support business-to-business (B2B), business-to-consumer (B2C), or business-to-employee (B2E) applications.

Portal applications typically consist of multiple portlets associated with one or more Web pages. Portlets are a specialized form of servlet technology, which is part of the Java 2 Enterprise Edition (J2EE) platform. Although complete applications themselves, portlets are more commonly used as components of larger, more complex applications. They may also be combined with other server-side Java objects, such as Enterprise JavaBeans (EJBs) and Java Server Pages (JSPs), to support applications that conform to a Model-View-Controller (MVC) design pattern.

Portlets use WebSphere Portal infrastructure services to access user profiles, communicate with other portlets, respond to window events, and access remote content. Portal administrators can manage authorization issues, tailor the appearance of a portal's pages (by altering its "skins" and "themes"), personalize content for users, and manage multiple portal applications.



Back to top


The case for EII-based portals

Although portal applications can readily provide a single access point to critical data, they usually do so by displaying content from different data sources on different areas of a page. For some applications, this may be sufficient. For example, a business traveler may find it quite reasonable to display corporate travel guidelines in one area, a weather forecast for his destination in another, and a city map for his destination in a third.

However, some applications require greater levels of correlation and integration of disparate data. Consider the simple case of a purchase order clerk who needs to search for products of a certain type and determine the low-cost supplier who can fulfill the order within one business day. Ideally, a consolidated report that takes all conditions into account should be displayed. But if product data, supplier data, supplier inventory levels, and supplier pricing information are spread across two or more sources, this can pose a challenge for simple data access portlets. And this is where DB2 II offers value. It complements WebSphere Portal's display-based integration with back-end data integration to provide a comprehensive solution.



Back to top


Putting it all together

Now that you understand what DB2 II and WebSphere Portal can do, let's explore how these products can be deployed together. While a variety of architectural topologies are possible, we'll review one general example here.

Fig. 1 illustrates a runtime architecture in which WebSphere Portal supports portal applications for an enterprise. These applications include one or more portlets -- either pre-built or custom-made -- that need to present an integrated view of disparate corporate data. WAS provides infrastructure services to WebSphere Portal and hosts J2EE components that some portlets may invoke, such as session EJBs and entity EJBs.

DB2 II provides data access and integration services, enabling portlets and J2EE components to work at a higher level of abstraction than would otherwise be possible. In doing so, DB2 II can transparently extend the reach of portlets and other server-side Java objects. Later in this article, you'll see how DB2 II enables programmers to use the pre-built My Query Reports portlet to create individual reports spanning multiple data sources, including data sources that aren't natively accessible through JDBC. Without DB2 II, this wouldn't be possible.


Figure 1. Sample WebSphere Portal Configuration with DB2 Information Integrator
Sample WebSphere Portal Configuration with DB2 Information Integrator

For custom portal development work, the architecture is conceptually similar. We use WebSphere Studio Application Developer with the WebSphere Portal Toolkit for our projects. This environment includes a test version of WAS and WebSphere Portal, so the two top components shown in Fig. 1 are readily available through WebSphere Studio when the portal development plug-in is installed.

The following section explains how to use pre-built portlets from IBM to create portal applications that work with DB2 II.



Back to top


Getting off to a quick start

To get started, you'll need to install and configure DB2 II, WebSphere Portal, and any pre-requisite products. The next section ("Setting up the Environment") describes how to do that. Once you have your base environment working, it won't take you long to deploy your first portlet that integrates data from multiple sources into a single report. The sections on "Using the My Query Reports Portlet" and "Using the JDBC Business Object Builder Portlet" will describe this in more detail.

Setting up the environment

The first step involves installing and configuring the appropriate products. For our project, we installed DB2 II V8.1, WebSphere Portal V5, and WebSphere Application Server V5 all on a Windows 2000 system. Because we also wanted to code our own custom portlets, we also installed an appropriate development environment: WebSphere Studio Application Developer V5 and the WebSphere Portal Toolkit V5. Finally, because we wanted to incorporate some advanced report writing capabilities into our final portal applications, we installed products from an IBM business partner (Crystal Reports V9 and Crystal Enterprise V9, both from Business Objects).

However, we won't describe our Java development or business intelligence environment here. Instead, we'll just cover the basics -- DB2 II, WebSphere Portal, and WAS. That simple architecture will be enough to get you started building portal applications that integrate data from multiple sources and present a consolidated result to users.

We chose to install and configure DB2 II first. In this way, we could be sure that access to all required remote data sources was functioning properly before we tried to develop integrated reports or complex portal applications. We won't go through the details of this work here, as these vary depending on your network configuration, remote data sources, and other factors. The DB2 II online product library (or InfoCenter), covers these topics well. In addition, the "Resources" section at the end of this article points you to an IBM developerWorks tutorial and an IBM Redbook with specific configuration examples.

With DB2 II working, we then installed and tested WebSphere Portal. Portal's installation process automatically installed two pre-requisite products (WAS and IBM HTTP Server) as well.

With a base WebSphere Portal environment working, we pursued additional configuration issues. For example, we launched the WAS Administrative Console to create a DataSource object for managing pooled connections to DB2 II. If you don't know how to define a DataSource, it's best to consult the WAS manuals or InfoCenter. However, we take you quickly through the process in Step 1 in our section on "Using the JDBC Business Object Builder Portlet." Depending on your security requirements, you may also want to define a J2C (Java 2 Connector) alias for your DB2 II user account. Use the WAS Administrative Console to do so, following the standard process. Again, consult the product documentation if you need help.

With this basic architecture in place, you're now ready to put your new environment to work. The following two sections will help you build simple reports using two portlets provided with WebSphere Portal: My Query Reports and JDBC Business Object Builder.

Using the My Query Reports portlet

Perhaps the simplest way for your portal application to integrate data from multiple sources into a single report is to use IBM My Query Reports portlet with DB2 II. This portlet is one of many that ship with WebSphere Portal. It enables users to establish unpooled connections to a DB2 II database, write a SELECT statement, and save the results of this report definition for later execution. When executed, the report displays the query's result in one area of the portal's Web page.

Here's how to use this portlet with DB2 II in your portal application:

  • Add the My Query Reports portlet to a new or existing page following the standard WebSphere Portal process for adding a pre-built portlet. (See the following figure.) When complete, click OK and then Done.



  • Locate the My Query Reports portlet on your page and edit this portlet following the WebSphere Portal standard process. (Click on the portlet's Edit icon in the upper right corner.)
  • Click Create to create a new query. Specify the required information. This includes a name for your query, a valid user name and password for your DB2 II database, the URL for your DB2 II database, and the SQL query itself.



  • Test your work and correct any errors. When complete, this query will be available to your portal users.

Using the JDBC Business Object Builder portlet

The JDBC Business Object Builder portlet also ships with WebSphere Portal. It provides more advanced data access support than the My Query Reports portlet, including the use of pooled connections (DataSource objects) for greater system efficiency, a wizard for generating SQL data manipulation language statements (SELECT, INSERT, UPDATE, DELETE) based on your specifications, the ability to tailor final report output based on user-supplied search predicates, and greater control over the final appearance of generated reports.

This portlet is actually a tool that enables you to generate other portlets, based on "business objects" that you specify. These business objects correspond to database tables or views in relational DBMSs. With DB2 II, your business objects can span multiple data sources, including non-relational data sources not natively supported by WAS.

Here's how to use this portlet builder with DB2 II:

  • Define and test a DataSource connection to your DB2 II database. Follow the standard WAS process for defining a DB2 UDB 8.1 database connection to do so. If you're not familiar with this process, consult the WAS InfoCenter. Here's a very quick overview:
    • Launch the WAS Administrative Console.
    • Click Resources -> JDBC Provider -> New.
    • After clicking OK, specify a name for your provider object and be sure the Classpath includes the appropriate DB2 file(s). (We selected a driver type of DB2 Legacy CLI-based Type 2 JDBC Driver and specified the db2java.zip file in our Classpath. Separately, we created a second driver of type DB2 Universal JDBC Driver Provider and included the following files in its Classpath: db2cc.jar, db2cc_license_cu.jar, db2cc_license_cisuz.jar.)
    • Create a DataSource object for your DB2 II database.
    • Click DataSource in the Additional Properties box of your JDBC Provider object, and then click New.
    • Specify a Name and associated JNDI Name for your DataSource. If necessary, specify authentication aliases as well.
    • Under Custom Properties, specify the DB2 II database name and any other additional properties appropriate to your environment.
    • Finally, Apply all your work and Save your changes.
    • Test the DataSource connection and correct any errors. Exit the WAS Administrative Console and launch a browser with WebSphere Portal.
  • Add the JDBC Business Object Builder to a new or existing page following the standard WebSphere Portal process. (See the following figure.) When complete, click OK and then Done.



  • Click New portlet and specify the required information. This includes a name for your portlet and the DataSource name for your DB2 II database (created in Step 1). In addition, choose from the three authentication options available. For our initial tests, we selected the second option of specifying a valid user ID and password that will be reused automatically on each invocation of our portlet. Click Next when completed.



  • Define a new business object for this portlet. Click New business object and follow prompts on subsequent screens to specify:
    • The database schema of interest (DEMO, in our example).
    • The table within this schema that you want to query.
    • A name for your new business object, and the types of actions (queries) allowed against this object. Valid options include search (SELECT), update, create (INSERT), and delete.
    • The fields (columns) you want your object to contain.
    • The order in which you want these fields to appear when displayed.
  • Deploy and test your portlet.



Back to top


Summary

We hope you've learned how EII technology in DB2 II can speed development of portal applications that need to integrate and consolidate data from multiple data sources. By providing a virtual database image of data that's managed by different servers or dynamically generated by Web services or proprietary applications, DB2 II can transparently extend the reach of pre-built data access portlets (such as IBM My Query Reports and JDBC Business Object Builder), as well as dramatically simplify the development of custom data access portlets.

Acknowledgement

Thanks to Holly Hayes and Dan Wolfson for their comments on this article.



Resources



About the author

C. M. Saracco is a senior software engineer at the IBM Silicon Valley Laboratory. She has written two books on database management topics (one co-authored with Charles J. Bontempo) and taught seminars in North America, South America, and Europe.




Rate this page


Please take a moment to complete this form to help us better serve you.



 


 


Not
useful		Extremely
useful
 


Share this....

digg Digg this story del.icio.us del.icio.us Slashdot Slashdot it!



Back to top