Integrating SQL Server with DB2

By Steven Baker

Most organizations have critical business data used by enterprise applications stored in a variety of databases and file systems. Furthermore, these data stores likely reside on a variety of computer and operating systems. Some of this system and data diversity may be from historical circumstances resulting from mergers and acquisitions. Some of this variety undoubtedly resulted from the gradual increase in computerized systems over time and the selection of different best-of-breed applications (and consequently an associated data store) for a particular purpose. In any case, it is not uncommon for large enterprises to have critical business data stored in Relational Database Management Systems (RDBMS) from different vendors, stored as host data in Virtual Sequential Access Management (VSAM) files on IBM mainframes or minicomputers, and stored in small database systems on personal computers using Microsoft FoxPro, Microsoft Access, or FileMaker Pro.

Now the charge is to use Enterprise Application Integration (EAI) to improve productivity and tie these disparate applications together into a more cooperative whole. The integration of data is a natural choice for many tasks. Data integration can bypass the necessity of making intrusive and costly changes to the existing application logic, user interface, or data structures.

Organizations often need to share business information among disparate applications. For example, custom reports, projections, and other analysis of sales or manufacturing data may be needed for making future business decisions. This critical sales and manufacturing data is often stored on host systems (IBM mainframe or IBM AS/400 systems) or on UNIX systems in a relational database management system. In fact, estimates are that the majority of corporate business data is still maintained on host systems. Yet the popular tools and personal productivity applications (Microsoft Word, Excel, and PowerPoint, for example) most commonly used to create custom reports and presentations run on the desktop. In some businesses, data used on the desktop for analysis and custom reporting may have to be manually entered from mainframe reports or files. So the challenge becomes integrating business applications using host data with productivity applications on the desktop. This application integration can enhance the options for custom reporting and analysis as well as eliminate costly and error-prone manual data entry. A popular approach for providing this integration bypasses the business applications and goes directly to the data source as the point of integration.

Microsoft provides a number of products and technologies that can be used to implement a data integration solution. Microsoft has developed two popular technologies that have become standards used for database middleware:

• Open Database Connectivity (ODBC)
• Object Linking and Embedding Database Providers (OLE DB)

Microsoft SQL Server 2000 and the older SQL Server 7.0 product include Data Transformation Services (DTS), a feature that can use ODBC Drivers or OLE DB Providers from Microsoft and other vendors to connect to heterogeneous database systems and transform and move data between systems. SQL Server also includes other features such as the Query Designer in Query Analyzer that make it relatively easy to analyze information and generate custom reports from the data stored in SQL Server.

A number of native ODBC drivers and OLE DB providers are included with SQL Server 2000 and with Microsoft Data Access Components (MDAC) on Windows. These OLE DB providers and ODBC drivers provide access to a variety of data sources including SQL Server, Oracle, Excel spreadsheets, Access databases, FoxPro/dBase database files, and flat text files. Microsoft Host Integration Server 2000 includes an OLE DB Provider for DB2 and an ODBC Driver for DB2 for accessing IBM DB2 on IBM mainframes, AS/400 computers, AIX, and Windows NT/2000 systems. A number of companies provide ODBC drivers and OLE DB providers for accessing other popular RDBMS and other database systems.

This chapter focuses on how these Microsoft products and technologies can be used together to provide a low cost data integration solution. This solution can be used for the simple task of generating custom reports and projections using desktop applications based on information in remote data sources. This solution can also be scaled and used for establishing a comprehensive data warehouse for online analytical processing (OLAP).

The chapter is divided into the following four sections:

• Scenario. This section describes the business problem to be solved in greater detail than the summary provided above.
• Solution. This section describes how Microsoft SQL Server 2000 Data Transformation Services in combination with ODBC drivers and OLE DB providers can be used to solve this business problem.
• Tools and Technology. This section focuses on the various tools and technologies involved in solving the business problem.
• Implementation. This section drills deeper into select portions of the implementation of the proposed solution, to give the reader a sense of the level of technical expertise required to implement the solution.

Previous  Next