Connectivity and Data Access

You can use the Microsoft Data Link tool to create a data source definition, which can then be saved in the form of a universal data link (UDL) file. You can also use the Data Source Wizard in the Microsoft Data Access Tool to create a UDL file.

You can also create a new data link by clicking the Data Access Tool shortcut in the Microsoft OLE DB Provider for DB2 program folder. You can then modify the UDL using the Data Links tool by opening the file from Windows Explorer, which loads the standard OLE DB Data Links user interface.

To start the Data Access tool, click the Data Access Tool shortcut in the Microsoft OLE DB Provider for DB2 program folder or click Start, Programs, Microsoft OLE DB Provider for DB2 and then Data Access Tool.

The Data Source Browser lets you configure and manage your data sources. The window is divided into three parts:

• A tree view that contains the data sources,
  
  
• A list view that contains details of a selected node, and
  
  
• A result view that contains the text results of an action, such as a package creation.
  
  

Context-sensitive menu commands

Many commands are also accessed through a context-sensitive menu which appears when you right-click any section of the Browser window. In addition, the F5 key updates the tree view, the Delete key deletes the currently selected item, and the F1 key opens the online Help. The Data Source folder contains data sources and groups of data sources. Right-clicking a data source item lets you view, edit, test, delete, or rename it.

Clicking New Data Source starts the Data Source Wizard. Clicking New Data Description creates an empty data description file and adds it to the tree view.

1. In the Data Access Tool window, click the File menu.
   
   
2. Click New, and then click Data Source or Data Description.
   
   

Editing the configuration brings up the editor for the specific data source. When you select an OLE DB data source, the Data Access Wizard is displayed.

1. In the Data Source Browser window, click the Actions menu.
   
   
2. Click Edit Data Source. The Data Source Wizard will appear.
   
   
3. When you are finished editing, click Finish.
   
   

The Display Connection String option displays the native OLEDB initialization string in the output window. You can copy this string from the output window and paste it into another location, such as SQL Server Management Studio, for use in a Distributed Query linked server statement.

1. In the Data Access Tool window, click the Actions menu.
   
   
2. Click Display Connection String. The connection string appears for viewing or copying.
   
   

The Test Connection command lets you test the connection to the data source, displaying information such as the host platform and version. Output from testing a connection to a DB2 server resembles the following.

Successfully connected to data source 'DB2DSN1'.

Server class: DB2/MVS

Server version: 09.01.0005

If you did not persist the user name and password into the connection configuration, an Authentication dialog box will appear.

You can use the Create Packages command to create packages on a DB2 server.

1. In the Data Access Tool window, click the Actions menu.
   
   
2. Click Create Packages. The Create Packages dialog box will appear.
   
   

You can use the Sample Query command to execute a sample query against the host data source. The sample query retrieves a list of tables from the system catalogs using the default schema property configured in the data source. The data is displayed in the results pane as two tabs: an Output tab and a Grid tab.

1. In the Data Access Tool window, click the Action menu.
   
   
2. Click Sample Query.
   
   

You can use the Open Data Source command to select a .UDL using the standard Windows File Open dialog box. This opens the data source using the Data Source Wizard.

1. In the Data Access Tool window, click the File menu.
   
   
2. Click Open Data Source. The File Open dialog box appears.
   
   
3. Browse to the appropriate data source and then click Open.
   
   

You can use the Data Source Browser to import an IBM DB2 Connect file.

1. In the Data Source Browser window, click the File menu.
   
   
2. Click Import, and then click the appropriate file.
   
   

For more information about IBM DB2 Connect files, see the IBM DB2 Connect documentation.

In addition to the tasks described in the previous topics, you can also perform the following actions.

1. Use the Edit menu to Undo, Cut, Copy, or Paste strings, and to Delete or Remove data sources.
   
   
2. Use the View menu to Refresh the browser or view the Data Access Tool Options dialog box.
   
   
3. Use the Help menu to access the online Help.
   
   

Optionally, you can select the check box to omit displaying this welcome screen.

You can use the Data Source screen to configure the DB2 database server platform.

Data source platform

Optionally, to increase performance and reduce impact to the remote database, select the data source platform on which the remote DB2 database is deployed. The Data Provider uses this value to convert data types to a format supported by this platform.

The default value is DB2/MVS (which refers to DB2 for z/OS). Other values include DB2/400 (which refers to DB2 for i5/OS), DB2/NT (which refers to DB2 for Windows), and DB2/6000 (which refers to DB2 for Linux or UNIX).

Network type

The Data Provider supports TCP/IP network connections to remote IBM DB2 database servers.

The TCP/IP Network Connection screen must be used to configure required parameters. These include network address (or alias) and port number.

Address or alias

You must enter a valid IP address or alias in either IPv4 or IPv6 format.

Port

You must specify an IP port number. The default value is TCP/IP port 446.

Certificate common name

Optionally, to improve security by encrypting authentication credentials and data, you can configure Secure Sockets Layer (SSL) V3.0 or Transport Layer Security (TLS) V1.0 encryption by specifying a server certificate common name.

Distributed transactions

This property is disabled in the Microsoft OLE DB Provider for DB2 that is used with Microsoft SQL Server 2008 R2. It is enabled with the version of the provider that is used with BizTalk Server 2009.

The DB2 Database screen must be used to configure required database parameters, such as the initial catalog and package collection.

Initial catalog

The Data Provider uses this value to connect to an initial catalog on the DB2 database server.

• DB2 for z/OS accepts a 16 byte string (catalog is also known as a location).
  
  
• DB2 for i5/OS accepts an 18 byte string (catalog is also known as relational database).
  
  
• DB2 for LUW accepts an 8 byte string (catalog is also known as database).
  
  

Package collection

The package collection is required to instruct the Data Provider into which DB2 schema to create a set of packages. Each package is divided into sections with static SQL statements, such as CREATE CURSOR, used to retrieve data when querying the database.

• DB2 for z/OS accepts a 128 byte string (schema is also known as a collection).
  
  
• DB2 for i5/OS accepts a 10 byte string (schema is also known as a collection or library).
  
  
• DB2 for LUW accepts a 30 byte string.
  
  

The Data Provider creates packages in one of two ways.

1. Automatic for single-user environment.
   
   At runtime, the Data Provider creates and binds a single package for the current isolation level (the default is cursor stability). It then grants execute permissions to the current user.
   
   
2. Manual for multi-user environment.
   
   At design-time, when you use the Data Access Tool menu option, the Data Source Wizard, or Data Links, the Data Provider creates and binds 4-5 packages for DB2 for i5/OS using MSNC001. The Data Provider then grants execute permissions to the PUBLIC group.
   
   

The Data Provider creates 1-5 packages, depending on database server platform and environment. The packages and their isolation levels are described in the following table.

Default schema

DB2 database objects are organized into logical groups called schemas. The schema name is used to catalog SQL objects such as tables and views, using a two-part naming convention <SCHEMA>.<OBJECTNAME>. At design time, to construct SQL such as SELECT statements, SQL Server consumers can present to the user a list of all objects in the database catalog. Optionally, to improve performance and developer efficiency, the Data Provider can use the default schema value to return a list of objects for a single schema only.

• DB2 for z/OS accepts a 128 byte string (schema is also known as a collection).
  
  
• DB2 for i5/OS accepts a 10 byte string (schema is also known as a collection or library).
  
  
• DB2 for LUW accepts a 30 byte string.
  
  

Default qualifier

DB2 database objects are organized into logical groups called schemas. The schema name is used to identify SQL objects such as tables and views, using a two-part naming convention <SCHEMA>.<OBJECTNAME>. SQL Server consumers may issue SQL statements with one-part or unqualified object names. At connection time, the Data Provider can set an environment option to specify a default qualifier. This informs the DB2 server in which schema to locate the object. The value of default qualifier must match an existing DB2 schema name, or an error may be returned by the DB2 server.

• DB2 for z/OS accepts a 128 byte string (schema is also known as a collection).
  
  
• DB2 for i5/OS accepts a 10 byte string (schema is also known as a collection or library).
  
  
• DB2 for LUW accepts a 30 byte string.
  
  

When designing cubes for use with SQL Server Analysis Services, the tools generate SQL commands that contain long alias names that may exceed the maximum length supported by the DB2 server. Depending on the DB2 platform and version, you may not be able to use queries with alias names exceeding 18 characters. For example, many objects deployed in DB2 for z/OS use names of 18 characters. Refer to the DB2 SQL Reference for your DB2 platform and version and check with your DB2 database administrator. We recommend that the administrator or developer update the two SQL Server Analysis Service configuration cartridge files that contain the data type mapping support for DB2 by changing the identifier-length (limit-table-identifier-length) from 29 to 18. The following are the names and location of the two cartridge files that must be updated.

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\db2v0801.xs

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\DataWarehouseDesigner\UIRdmsCartridge\db2v0801.xs

SQL Server Analysis Services uses the updated configuration files to correctly name objects in SQL commands.

Database name

DB2 databases can be divided into multiple logical databases for administration purposes, each containing separate table spaces and index spaces. The optional database name instructs the Data Provider to use the IN DATABASE clause in SQL statements.

DB2 for z/OS accepts an 8 byte string.

Optionally, to increase performance and reduce the impact on the remote database, select the coded character set identifier (CCSID) for the remote DB2 database (host) and local SQL Server database (PC). The Data Provider uses these values to convert character strings to a code page supported by these databases. The Data Provider supports a combination of single byte character sets (SBCS), mixed-byte character sets (MBCS) double-byte character sets (DBCS), and Unicode - UTF8 [1208], which is an 8-bit Unicode transformation format. For more information, see SNA Internationalization Programmer's Reference (http://go.microsoft.com/fwlink/?LinkID=181017).

Host CCSID

The default Host CCSID value is EBCDIC – U.S./Canada [37].

Extended Binary Coded Decimal Interchange Code is used on z/OS and i5/OS platforms. For more information, see SNA Internationalization Programmer's Reference (http://go.microsoft.com/fwlink/?LinkID=181017).

PC Code Page

The default PC code page is ANSI – Latin I [1252].

American National Standards Institute is used on Windows and with most SQL Server databases, together with the Unicode standard. For more information, see SNA Internationalization Programmer's Reference (http://go.microsoft.com/fwlink/?LinkID=181017).

Process binary as character

The optional Process binary (CCSID 65535) as character instructs the Data Provider to convert DB2 bytes to and from SQL Server character strings, based on an optional Binary Code Page. For more information, see All Properties .

The SQL Server Integration Services Import and Export Wizards can be customized to support alternative default conversions.

When using the SQL Server Integration Services Import and Export Wizards from the Microsoft SQL Server Management Studio, you can customize the default data conversions by editing the XML mapping files. The XML files are located in the following folder.

C:\Program Files\Microsoft SQL Server\100\DTS\MappingFiles

SQL Server Replication may convert data incorrectly, based on the default mappings from SQL Server to DB2 data types. We recommend that the administrator and developer review and revise the Replication data type mappings using the following SQL Server system stored procedures.

sp_helpdatatypemap

sp_getdefaultdatatypemapping

sp_setdefaultdatatypemapping

For more information, see the System Stored Procedures (Transact-SQL) topic in SQL Server Books Online (http://go.microsoft.com/fwlink/?LinkID=180765).

The Security screen lets you configure one of three security methods: interactive sign-on, single sign-on, or Kerberos.

Interactive sign-on

Interactive sign-on security relies on a username and password that the user enters at runtime, or that is stored in a configuration file or data consumer configuration store, such as an Integration Services package.

User name

• DB2 for z/OS accepts an 8 byte string.
  
  
• DB2 for i5/OS accepts a 10 byte string.
  
  
• DB2 for Linux or UNIX accepts an 8 byte string.
  
  
• DB2 for Windows accepts a 30 byte string Password
  
  

Password

• DB2 for z/OS accepts an 8 byte string.
  
  
• DB2 for i5/OS accepts a 128 byte string.
  
  
• DB2 for Linux or UNIX accepts an 8 byte string.
  
  
• DB2 for Windows accepts a 32 byte string.
  
  

Authentication method

The authentication method property sets the authentication method for the connection, based the weak Data Encryption Standard (DES) technologies. The default values are Server using interactive sign-on, security relying on a username and password with no encryption.

• The Server_Encrypt_Pwd option instructs the Data Provider to encrypt the password only.
  
  
• The Server_Encrypt_UsrPwd instructs the Data Provider to encrypt both the username and password.
  
  
• The Data_Encrypt option instructs the Data Provider to encrypt the username, password, and user data.
  
  

Warning
We recommend that you use a security method that uses strong authentication encryption, such as Kerberos, SSL V3.0 TLS V1.0.

Save password

Optionally, users can decide to save the password in the OLE DB Universal Data Link (UDL) or text file by clicking the Allow saving password check box. Choosing this option saves the username and password in plain text. It is not possible to encrypt the user name or password using this method. Server security can be compromised if an attacker can gain access to the file share on which the UDL or text file is located.

Single sign-on

Single sign-on uses a username and password that are stored in an encrypted enterprise single sign-on database.

Affiliate Application

This property is required for use with Enterprise Single Sign-On.

Kerberos

Kerberos relies on a ticket that contains encrypted credentials. For more information, see Microsoft Kerberos (http://go.microsoft.com/fwlink/?LinkID=180764).

Principal name

This property is required for use with Kerberos authentication.

The Advanced Settings screen lets you configure additional optional settings.

Connection pooling

This property sets the provider-specific client-side connection pooling, which can be used together with max pool size, connection time-out, client application name, and other connection options.

Read Only

Determines whether the connection is read-only or if it enables modifications to the database.

Defer Prepare

The defer prepare setting is optional for the OLE DB data source initialization property. It instructs the Data Provider to optimize the processing of parameterized INSERT, UPDATE, DELETE, and SELECT commands. For the INSERT, UPDATE, and DELETE commands, the Data Provider combines prepare, execute, and commit commands into one network flow to the remote database. For the SELECT command, the Data Provider combines prepare and execute commands into one network flow. This optimization minimizes network traffic and frequently improves overall performance.

Derive Parameters

You can use the optional Derive Parameters setting with SQL Server Integration Services to determine the correct length of parameters defined as character data types. This parameter is not required when you are using SQL Server Replication Services or other SQL Server consumers.

The All Properties screen lets you configure more detailed and optional properties. These properties may be edited by selecting a property from the list, and then selecting or editing the value in the right column. You can edit the following properties from this screen.

• AutoCommit
  
  
• Binary Code Page
  
  
• Connection Timeout
  
  
• DateTime As Char
  
  
• DateTime As Date
  
  
• Encrypt Credentials
  
  
• Max Pool Size
  
  
• Network Type
  
  
• Rowset Cache Size
  
  
• Units of Work
  
  

You can use the Validation screen to validate your configuration by testing the connection. You can also use it to create DB2 packages and execute a sample query.

• Click Connect to perform a test connection.
  
  
• Click Packages to create the DB2 packages required to execute SQL statements in a multi-user environment.
  
  
• Click Sample Query to retrieve a list of tables in the default schema.
  
  

Use the Saving Information screen to name and save your configuration. Configurations are saved in the following location.

C:\Users\<username>\Documents\Host Integration Projects\Data Sources\

Data source name

The data source is a parameter that can be used to describe the data source. When creating a data link using the Data Source Wizard, the Data Source property is used to name the Universal Data Link (UDL) file or connection string file.

OLE DB or Managed group

The SQL Server Business Intelligence Development Studio (BIDS) presents a standard OLE DB Data Links property dialog, with which the user can browse to an UDL file. For other SQL Server consumers, you can save the configuration in a Managed initialization text string file format.

This screen displays a summary and status of your configuration. Click Finish to implement your actions.