Transcription
Microsoft OLE DB Provider for DB2Version 4.0Product Documentation(Last updated 31st January, 2012)The Microsoft OLE DB Provider for DB2 V 4.0 documentation provides information for installingand using version 4 of the Microsoft OLE DB Provider for DB2 (Data Provider).
CopyrightInformation in this document, including URL and other Internet Web site references, is subject tochange without notice. Unless otherwise noted, the companies, organizations, products, domainnames, e-mail addresses, logos, people, places, and events depicted in examples herein arefictitious. No association with any real company, organization, product, domain name, e-mailaddress, logo, person, place, or event is intended or should be inferred. Complying with allapplicable copyright laws is the responsibility of the user. Without limiting the rights undercopyright, no part of this document may be reproduced, stored in or introduced into a retrievalsystem, or transmitted in any form or by any means (electronic, mechanical, photocopying,recording, or otherwise), or for any purpose, without the express written permission of MicrosoftCorporation.Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectualproperty rights covering subject matter in this document. Except as expressly provided in anywritten license agreement from Microsoft, the furnishing of this document does not give you anylicense to these patents, trademarks, copyrights, or other intellectual property. 2012 Microsoft Corporation. All rights reserved.Microsoft, Windows, Windows Server, SQL Server, Active Directory, Hyper-V, and Visual Studioare trademarks of the Microsoft group of companies. All other trademarks are property of theirrespective owners.
Table of ContentsGetting Started. 7In This Section. 7Overview of Data Provider . 7Data Provider Features . 7What’s New in Version 4.0 . 8Installing Data Provider . 8Installing Data Provider . 8Supported Operating Systems . 8Prerequisite Software . 9Upgrade from Previous Version . 9To Install the Product . 9To install the product unattended . 10To repair the product installation . 10To uninstall the product . 11To uninstall the product unattended . 11Planning and Architecture . 11In This Section. 11Planning . 12Planning various components . 12Data Provider . 12DB2 Servers . 12SQL Server Products . 12SQL Server Data Consumers . 13Data Conversion . 13Code Page Conversion . 13Security . 13Architecture. 14Data Provider . 14Data Provider Tools . 14Data Access Tool with Data Source Wizard . 14Data Links . 14Trace Utility . 15SQL Server Tools . 15SQL Server Data Tools (SSDT). 15
SQL Server Management Studio . 15SQL Server Data Consumers . 15Integration Services . 15Query Processor . 15Analysis Services . 16Reporting Services. 16Replication . 17Deployment. 17In This Section. 17Data Access Tool. 17Data Access Tool User Interface . 17Data Access Tool windows . 17Menu commands and toolbar . 18Data Access Tool Common Tasks . 18Creating a Data Source . 18Opening a Data Source . 18Importing a Data Source . 18Editing a Data Source . 19Testing a Connection . 19Running a Sample Query . 19Creating Packages. 19Displaying a Connection String . 20Changing a Password . 20Locating a Connection Definition . 20Setting Options . 20Obtaining Help . 20Completing Other Tasks . 20Data Source Wizard . 21Welcome . 21Data Source . 21TCP/IP Network Connection . 22DB2 Database . 22Locale . 24Security . 25Security method - Interactive sign-on . 25Security method - Single sign-on . 26Security method - Kerberos . 26Advanced Options . 27All Properties . 27Data Access Library . 40
Data Link Tool. 40Creating a Data Link . 40Provider . 41Connection . 41Network . 41Security . 41Security method - Interactive sign-on. 42Security method - Single sign-on . 42Security method - Kerberos . 42Database . 43Connection Actions . 44Advanced Options . 44All Properties . 45Operations . 58In This Section. 58Code Page Conversions . 58Host CCSID . 58PC Code Page . 59Process Binary as Character . 59Binary Code Page . 59Data Type Mapping . 59DB2 to OLE DB Data Type Mapping. 59Data Types by DB2 Platform . 61DB2 for z/OS V9R1 . 61DB2 for i5/OS V6R1 . 62DB2 for LUW V9.7 . 63SQL Server Integration Services . 64SQL Server Replication Services . 64Performance . 65Configuring for Performance . 65Pool OLE DB resources to reduce connection startup time . 65Pool provider resources to reduce connection startup time . 65Optimize the rowset cache when getting data . 65Deferring preparing of commands with parameters until execution . 66Retrieving schema information from DB2 shadow catalog . 66Sending multiple rows in a single unit of work . 66Command time-out to terminate long-running queries . 67Measuring Performance . 67Security and Protection . 67In This Section. 67
Security . 67Protection . 68Encryption standards for DB2 . 68Configuring for Protection . 68The Data Provider grants execute on DB2 package to the DB2 public group . 68The Data Provider stores the user name in plain text in the Universal Data Link (UDL) orconnection string file . 69The Data Provider supports weak encryption based on DES and Diffie-Hellman . 69The Data Provider connects using unencrypted, plain text, user name and password . 69The Data Provider sends and receives unencrypted data . 69The Data Provider send additional network flow to support Defer Prepare . 69Troubleshooting . 70In This Section. 70Data Consumer Issues . 70SQL Server Integration Services . 70Enterprise Single Sign-On . 70Data Type Mapping. 70Data Type Mapping Files . 71Customizing Data Flow Components . 74SQL Server Replication . 74Derive Parameters . 74Data Type Mapping. 75SQL Server Analysis Services . 78Data Provider Error Codes . 78DRDA Protocol Errors . 79TCPIP Network Errors . 97Common Errors . 105Tracing . 113SQL consumer tracing using SQL Server Profiler . 113DB2 provider tracing using Provider Trace Utility . 113Network tracing using Network Monitor . 113DB2 server tracing using IBM tools . 113Windows Server events using Event Viewer . 113
Getting StartedThe following sections provide help for learning about, installing and configuring the MicrosoftOLE DB Provider for DB2 Version 4.0 (Data Provider).In This Section1. Overview of Data Provider2. What’s New in Version 4.03. Installing Data ProviderOverview of Data ProviderThe Microsoft OLE DB Provider for DB2 Version 4.0 (Data Provider) lets you create distributedapplications targeting IBM DB2 databases. The Data Provider takes advantage of Microsoft SQLServer data access architecture together with a Microsoft network client for DB2 that functions asa Distributed Relational Database Architecture (DRDA) application requester. The Data Providerconverts Microsoft Component Object Model (COM) OLE DB commands and data types to DRDAprotocol code points and data formats.Data Provider FeaturesThe Data Provider offers the following features: Installation using an interactive and scriptable program. Support for Windows 32-bit x86 and 64-bit x64 operating systems. Access to DB2 server computers across a TCP/IP network connection.7
Execution of DB2 dynamic SQL commands within remote unit of work transactions. Tools for creating, testing, modifying and storing data source definitions. A trace utility for recording flows and commands to enable troubleshooting of problems. Help through on-line user documentation.The Data Provider is designed and tested for use with Microsoft SQL Server 2012 and MicrosoftSQL Server 2008 R2. The Data Provider is based on technology in Microsoft Host IntegrationServer 2010. For more information about HIS 2010, see Host Integration Server 2010(http://go.microsoft.com/fwlink/?LinkID 180445).What’s New in Version 4.0The Microsoft OLE DB Provider for DB2 version 4.0 offers the following improvements: Simplified installation program Connectivity to DB2 for z/OS V10 Connectivity to DB2 for i5/OS V7R1 Client Transaction Load Balancing OpenRowset using FastLoad when connecting to DB2 for z/OS and DB2 for LUW Reading DB2 Binary Large Object (BLOB) and Character Large Object (CLOB) data types Writing DB2 BLOB and CLOB data types using Command with Parameters DB2 Decimal as OLE DB Numeric data type mapping SQL Server DATETIME2 data type DB2 long object identifiers Accessing DB2 tables with large number of columns and parameters Reading schema information stored in DB2 shadow catalog tables Session and command source identifiers Reason codes included in error information Extended error documentationInstalling Data ProviderThis topic provides instructions to install the Data Provider.Installing Data ProviderSupported Operating SystemsThe Data Provider (32-bit x86 or 64-bit x64) will install on these Microsoft operating systems. Windows Server 2008 R2 SP18
Windows 7 SP1 Windows Server 2008 SP2Prerequisite SoftwareThe Data Provider requires the following software products as installation prerequisites. Microsoft SQL Server 2012 (Enterprise, Developer or Evaluation Edition) or Microsoft SQLServer 2008 R2 (Datacenter, Enterprise, Developer or Evaluation Edition) Microsoft .NET Framework 4.0 Microsoft Visual C 2010 Redistributable Package (x86) Microsoft Visual C 2010 Redistributable Package (x64)NoteWhen installing on a 64-bit (x64) operating system, you must install both x86 and x64of Visual Studio 2010 C packages.Upgrade from Previous VersionMicrosoft OLE DB Provider for DB2 V 4.0 does not upgrade previous releases. If you have thefollowing previous versions installed, then you must remove them prior to installing the MicrosoftOLE DB Provider for DB2 V 4.0. Microsoft OLE DB Provider for DB2 V1.0 Microsoft OLE DB Provider for DB2 V1.0 with SP1 Microsoft OLE DB Provider for DB2 V2.0 Microsoft OLE DB Provider for DB2 V3.0To Install the ProductThere are two options for installing the Data Provider, including interactive installation andunattended installation. The following steps guide you through interactive installation.1. Go to the Microsoft Download Center.2. Download either the x86 (32-bit) or the x64 (64-bit) version of DB2OLEDB4 x64.msiinstallation program.3. Double-click the .msi file to start the Installation Wizard.4. Click Next to get started.5. On the License Agreement page, review the license terms, click the I accept the terms inthe license agreement option, and then click Next.6. On the Registration Information page, enter your Name and Company, and then clickNext.9
7. On the Feature Selection page, optionally click Browse to change the Folder name inwhich to install the product, optionally click Disk Cost to space required to install the product,and then click Next.8. On the Ready to Install the Program page, click Install.9. When prompted by Windows User Account Control, click Yes.10. On the Installing page, view the status of the installation process.11. On the Completion page, click Finish.To install the product unattendedThere are two options for installing the Service for DRDA, including interactive installation andunattended installation. The following steps guide you through unattended installation.1. On the Start menu, point to All Programs, point to Microsoft Visual Studio 2010, point toVisual Studio Tools, right click Visual Studio x64 Win64 Command Prompt (2010), andclick Run as administrator. The User Account Control dialog will appear. Click Yes tocontinue.2. In the Visual Studio x64 Win64 Command Prompt (2010) window, locate the installationfolder in which you downloaded the installation program, enter DB2OLEDB4 x64.msi /quiet.3. To verify the installation, locate the installed product in C:\Program Files\Microsoft OLE DBProvider for DB2.NoteOptionally, to generate a log, add /l log file name to the command string. To verifythe installation, enter notepad log file name , and then click Enter.To repair the product installationYou can use Windows Programs and Features to launch the Program Maintenance to repairthe installation.1. Click Control Panel, click Programs, and then click Programs and Features. The Uninstallor change a program dialog appears.2. In the Name list, double click Microsoft OLE DB Provider for DB2 Version 4.0. The DataProvider Installation Wizard appears.3. Click Next to get started.4. On the Program Maintenance dialog, click Repair.5. On the Ready to Repair the Program dialog, click Repair.6. When prompted by Windows User Account Control, click Yes.7. On the Completion page, click Finish.10
To uninstall the productYou can use Windows Programs and Features to remove the product.1. Click Control Panel, click Programs, and then click Programs and Features. The Uninstallor change a program dialog appears.2. In the Name list, double click Microsoft OLE DB Provider for DB2 Version 4.0. The DataProvider Installation Wizard appears.3. Click Next to get started.4. On the Program Maintenance dialog, click Remove.5. On the Remove the Program dialog, click Remove.6. When prompted by Windows User Account Control, click Yes.7. On the Completion page, click Finish.To uninstall the product unattendedYou can use an unattended command to uninstall the product.1. On the Start menu, point to All Programs, point to Microsoft Visual Studio 2010, point toVisual Studio Tools, right click Visual Studio x64 Win64 Command Prompt (2010), andclick Run as administrator. The User Account Control dialog will appear. Click Yes tocontinue.2. In the Visual Studio x64 Win64 Command Prompt (2010) window, locate the installationfolder in which you downloaded the installation program, enter DB2OLEDB4 x64.msi/uninstall /quiet, and then click Enter.3. To verify the removal, locate the installed product in C:\Program Files\ Microsoft OLE DBProvider for DB2.NoteOptionally, to generate a log, add /l log file name to the command string. To verifythe installation, enter notepad log file name , and then click Enter.Planning and ArchitectureThe following sections provide help for planning and architecting solutions using Data Provider.In This Section1. Planning2. Architecture11
PlanningEnterprise developers who are using on-line transactional processing (OLTP) and businessintelligence (BI) technologies can take advantage of the SQL Server data access architecture toconnect IBM DB2 databases to new solutions built by using SQL Server integration, analysis,reporting, replication and distributed query technologies. The Data Provider supports SQLcommands. This allows for interoperability between COM OLE DB-enabled consumer servicesand tools in Microsoft SQL Server and remote IBM DB2 relational database managementsystems. You can execute data definition language (DDL) or data manipul
In the Name list, double click Microsoft OLE DB Provider for DB2 Version 4.0. The Data Provider Installation Wizard appears. 3. Click Next to get started. 4. On the Program Maintenance dialog, click Repair. 5. On the Ready to Repair the Program dialog, click Repair. 6.
