Share via

  • Article

Overview

This topic contains the following sections.

  • Product Overview

  • SQL Server 2008 Consumers

Product Overview

Version 3.0 of the Microsoft OLE DB Provider for DB2 (Data Provider) lets you create distributed applications targeting IBM DB2 databases. The Data Provider takes advantage of SQL Server 2008 R2 data access architecture together with a Microsoft Network client for DB2 that functions as a Distributed Relational Database Architecture (DRDA) application requester. The Data Provider converts Microsoft Component Object Model (COM) OLE DB commands and data types to DRDA protocol code points and data formats.

Enterprise developers who are using on-line transactional processing (OLTP) and business intelligence (BI) technologies can take advantage of the SQL Server data access architecture to connect IBM DB2 databases to new solutions built by using SQL Server integration, analysis, reporting, replication and distributed query technologies. The Data Provider supports SQL commands. This allows for interoperability between COM OLE DB-enabled consumer services and tools in Microsoft SQL Server 2008 R2 and remote IBM DB2 relational database management systems. You can execute data definition language (DDL) or data manipulation language (DML) SQL statements that include read and write operations based on dynamic SQL in addition to stored procedures within remote unit of work (RUW) transactions.

The following illustration shows the connections between a client computer, an instance of SQL Server, the OLEDB2v3 provider, and DB2.

OLE DB Provider for DB2 v3 architecture

Data Provider Features

The Data Provider offers the following features.

  • Installation using an interactive and scriptable Setup program.

  • Support for 32-bit x86, 64-bit x64, and 64-bit ia64 operating systems.

  • Help through on-line user documentation.

  • Access to DB2 servers across a TCP/IP network connection.

  • Remote unit of work (RUW) transactions.

  • Execution of dynamic SQL commands (DDL and DML), including CALL statements for stored procedures (with multiple result sets).

  • Assistance in creating, modifying and storing data source definitions using the Data Provider-specific Data Access Tool and Data Source Wizard, or by using common OLE DB Data Links.

  • A trace utility for recording flows and commands to enable efficient troubleshooting of problems.

You can use the Data Provider to connect to IBM DB2 database servers on the following platforms.

  • IBM DB2 for z/OS V8.1 and DB2 for z/OS V9.1

  • IBM DB2 for i5/OS V5R4 and V6R1

  • IBM DB2 UDB for Windows, AIX, HP-UX, Solaris, Linux V9.1, V9.5, and V9.7

The Data Provider is designed and tested for use with Microsoft SQL Server 2008 R2. The Data Provider is based on technology in Microsoft Host Integration Server 2009. For more information about HIS 2009, see Host Integration Server 2009 (https://go.microsoft.com/fwlink/?LinkID=180445).

SQL Server 2008 Consumers

The Data Provider supports integration with COM+ enabled OLE DB and ADO.NET consumers. SQL Server Integration Services, SQL Server Analysis Services, and SQL Server Reporting Services interact indirectly with the Data Provider through the Microsoft ADO.NET Data Provider for OLE DB. Distributed query processing interacts with the Data Provider directly through OLE DB. SQL Server Replication requires a DQP-defined linked server for specifying the initial connectivity information, but will use ADO.NET to OLE DB integration at run time when synchronizing data.

You can use the Data Provider with the following SQL Server consumers.

  • Integration Services

  • Analysis Services

  • Reporting Services

  • Replication

  • Query Processor

SQL Server provides a rich array of tools that you can use to create DB2 solutions with SQL Server consumers.

Business Intelligence Development Studio

Business Intelligence Development Studio is the primary development environment for creating business solutions using Analysis Services, Integration Services, and Reporting Services. Business Intelligence Development Studio provides templates, designers, tools, and wizards that are specific to each consumer. For more information, see Introducing Business Intelligence Development Studio (https://go.microsoft.com/fwlink/?LinkID=180755.

SQL Server Management Studio

SQL Server Management Studio is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. You can use the graphical tools and script editors in SQL Server Management Studio to work with DB2 data and SQL Server data. In addition, SQL Server Management Studio works with all components of SQL Server such as Reporting Services and Integration Services. For more information, see Using SQL Server Management Studio (https://go.microsoft.com/fwlink/?LinkID=180759).

Integration Services

You can use the Integration Services project type in Business Intelligence Development Studio to create data extraction, transformation, and loading (ETL) applications. It contains templates for packages, data sources, and data source views, and provides the tools for working with these objects. For more information, see Integration Services in Business Intelligence Development Studio (https://go.microsoft.com/fwlink/?LinkID=180757).

You can also use the Namespaces in the https://go.microsoft.com/fwlink/?LinkID=180760 (https://go.microsoft.com/fwlink/?LinkID=180760) to programmatically create and manage packages. For more information about how to create Integration Services solutions, see the Integration Services Developer InfoCenter (https://go.microsoft.com/fwlink/?LinkID=180761).

For the documentation on SQL Server Integration Services, see SQL Server Integration Services (https://go.microsoft.com/fwlink/?LinkID=180424).

Analysis Services

You can use the Business Intelligence Development Studio to develop Online Analytical Processing (OLAP) cubes and data mining models in SQL Server Analysis Services. This project type includes templates for cubes, dimensions, mining structures, data sources, data source views, and roles, and provides the tools for working with these objects.

For more information, see Analysis Services in Business Intelligence Development Studio (https://go.microsoft.com/fwlink/?LinkID=180756).

For the Analysis Services documentation, see SQL Server Analysis Services - Multidimensional Data (https://go.microsoft.com/fwlink/?LinkID=180426) and SQL Server Analysis Services - Data Mining (https://go.microsoft.com/fwlink/?LinkID=180427).

Reporting Services

You can use the Report Server projects in Business Intelligence Development Studio for developing Reporting Services solutions that access DB2 data. The Report Model project type includes the templates for report models, data sources, and data source views, and provides the tools for working with these objects. The Report Server project includes the templates for working with reports and shared data sources. For more information, see Reporting Services in Business Intelligence Development Studio (https://go.microsoft.com/fwlink/?LinkID=180758).

For the Reporting Services documentation, see SQL Server Reporting Services (https://go.microsoft.com/fwlink/?LinkID=180428).

Replication

Administrators can move data from SQL Server to DB2 using Replication wizards in SQL Server Management Studio, as part of either snapshot or transactional replication operations. For Replication, SQL Server uses linked servers for connectivity and Integration Services for synchronizing data with DB2.

For the SQL Server Replication documentation, see SQL Server Replication (https://go.microsoft.com/fwlink/?LinkID=180425).

Query Processor

Administrators and developers can use distributed queries to access data from multiple heterogeneous data sources including DB2. See the Connectivity and Data Access topic for information about how to configure DB2 data sources.

For more information about SQL Server distributed queries, see Distributed Queries (https://go.microsoft.com/fwlink/?LinkID=180429).