• Article

Data Access Configuration Properties

This topic describes data access configuration properties for the Microsoft OLE DB provider for DB2 (Data Provider). This topic contains the following sections.

  • Provider

  • Connection

  • Advanced

  • All

Provider

Use the Provider tab to select the Microsoft OLE DB Provider for DB2 (the provider name string) from a list of possible OLE DB providers.

Connection

Use the Connection tab to configure the basic properties required to connect to a data source. This section describes the properties that are specific to Microsoft OLE DB Provider for DB2 connections.

Data Source

An optional parameter that is used to describe the data source. When you create a data link file using the Data Source Wizard, the Data Source property names the Universal Data Link (UDL) file or connection string file.

Network

You must select TCP/IP Connection from the drop-down list. The Microsoft OLE DB Provider for DB2 V3.0 does not support an APPC connection.

Once you select TCP/IP Connection, click the ellipsis (…) to open the dialog box for configuring TCP/IP network settings.

You must configure the IP address of the DB2 database server (or a host name alias for the database server) and the Network Port (TCP/IP port) used to communicate with the DB2 database instance. The default value for the Network Port is 446.

IBM DB2 database servers can support multiple concurrent database instances. Each is configured for access by using a TCP/IP network through a separate TCP/IP port number.

When using Secure Sockets Layer (SSL) or Transport Layer Security (TLS) encryption you must enter a value for Certificate common name.

Security method

You can select one of the following authentication options for the Security method property.

  • Interactive sign-on security, which relies on a username and password stored in a configuration file or data consumer configuration store.

  • Single sign-on, which uses a username and password stored in an encrypted enterprise single sign-on database. Single sign-on allows the Data Provider to obtain the username and password from an encrypted Enterprise Single Sign-On database.

  • Kerberos, which relies on a ticket that contains encrypted credentials.

The configuration controls in the Security options group change depending on which option that you select.

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

  • 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.

You can save the password in a UDL or text file by clicking the Allow saving password check box.

Warning

Authentication information, such as user names and passwords, is saved in plain text in a UDL or text file. Encryption of UDL or text files is not supported.

Affiliate application

Required for use with Enterprise Single Sign-On.

Principal name

Required for use with Kerberos authentication.

Initial catalog

Required. 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, that are 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 using one of the following options.

  • 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). The Data Provider grants execute privileges to the current user.

  • 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 the database server platform and environment. The following table describes the packages and isolation levels.

Microsoft Package Name

DB2 Isolation Level Name

OLE DB Isolation Level Name

MSNC001

NO COMMIT

N/A (no corresponding transaction)

MSUR001

UNCOMMITTED READ

ISOLATIONLEVEL_READUNCOMMITTED

MSCS001

CURSOR STABILITY

ISOLATIONLEVEL_READCOMMITTED

MSRS001

READ STABILITY

ISOLATIONLEVEL_REPEATABLEREAD

MSRR001

REPEATABLE READ

ISOLATIONLEVEL_SERIALIZABLE

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, employing 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.

The Connection tab also includes a Test Connection button that instructs the Data Provider to connect to the remote IBM DB2 database server by using the defined TCP/IP network connection.

  • 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 Connection tab also includes a Test Connection button that instructs the Data Provider to connect to the remote IBM DB2 database server by using the defined TCP/IP network connection.

Advanced

This section describes the properties that you can configure in the Advanced tab.

DBMS Platform

Used to optimize performance of the Data Provider when executing operations such as data conversion. The default value is DB2 for z/OS.

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 is used to inform 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.

Host CCSID

To increase performance and reduce impact to the remote database, you can 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 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 (https://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 (https://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 default is false.

Distributed transactions

Disabled in the Microsoft OLE DB Provider for DB2 that is used with Microsoft SQL Server 2008 R2.

Enabled with the version of the Data Provider that is used with BizTalk Server 2009.

All

The All tab lets users configure more detailed and optional properties by selecting a property from the drop-down list and then selecting Edit Value.

Alternate TP Name

Used to specify a DB2 transaction program (TP) name other than the default, which is 07F6C4C2.

APPC Local LU Alias

Disabled in the Microsoft OLE DB Provider for DB2 that is used with Microsoft SQL Server 2008 R2.

Enabled with the version of the Data Provider that is used with BizTalk Server 2009.

APPC Mode Name

Disabled in the Microsoft OLE DB Provider for DB2 that is used with Microsoft SQL Server 2008 R2.

Enabled with the version of the Data Provider that is used with BizTalk Server 2009.

APPC Remote LU Alias

Disabled in the Microsoft OLE DB Provider for DB2 that is used with Microsoft SQL Server 2008 R2.

Enabled with the version of the Data Provider that is used with BizTalk Server 2009.

Cache Authentication

Determines whether the OLE DB Provider for DB2 caches authentication information. The default is false.

Data Source

An optional parameter that can be used to describe the data source. There is no default value.

Extended Properties

A string that contains provider-specific, extended connection information.

Integrated Security

Sets the security method for authentication. The following options are available.

  • SSPI, which instructs the Data Provider to obtain credentials that are stored in an encrypted enterprise single sign-on database.

  • Kerberos, which relies on a ticket that contains encrypted credentials.

The default is blank, which indicates that interactive sign-on security will be used. Interactive sign-on requires a user name and password.

Mode

A bitmask specifying access permissions. The bitmask can be a combination of zero or more of the following.

  • DB_MODE_READ. Read-only.

  • DB_MODE_READWRITE. Read/write (DB_MODE_READ | DB_MODE_WRITE).

  • DB_MODE_SHARE_DENY_NONE. Neither read nor write access can be denied to others.

  • DB_MODE_SHARE_DENY_READ. Prevents others from opening in read mode.

  • DB_MODE_SHARE_DENY_WRITE. Prevents others from opening in write mode.

  • DB_MODE_SHARE_EXCLUSIVE. Prevents others from opening in read/write mode (DB_MODE_SHARE_DENY_READ | DB_MODE_SHARE_DENY_WRITE).

  • DB_MODE_WRITE. Write-only.

The Data Provider supports the following values for Mode.

  • DB_MODE_READ

  • DB_MODE_READ/WRITE

The default value is Read/Write.

When the Read Only parameter is checked in the Advanced tab, the Data Provider creates a read-only data source by setting the Mode property to DB_MODE_READ. In this case, a user has read access to objects such as tables, but cannot perform data modification operations such as INSERT, UPDATE, or DELETE.

Network Address

Represents the IP address or IP alias of the DB2 database in either IPv4 or IPv6 format.

Network Port

Represents the TCP/IP port number of the DB2 database. The default value is TCP/IP port 446.

Network Transport Library

TCPIP in the Microsoft OLE DB Provider for DB2 that is used with Microsoft SQL Server 2008 R2.

Both TCP and SNA are supported with the version of the Data Provider that is used with BizTalk Server 2009.

Persist Security Info

Indicates whether the data source object can persist sensitive authentication information, such as a password, together with other authentication information. The default is false.

Units of Work

The Microsoft OLE DB Provider for DB2 that is used with SQL Server 2008 R2 supports a value of RUW, defined as Remote Unit of Work. The Data Provider that is used with BizTalk Server 2009 supports both RUW and DUW, which is defined as two-phase commit protected Distributed Unit of Work.

DateTime As Char

Optional OLE DB data source initialization property that instructs the Data Provider to expose DB2 DATE, TIME, and TIMESTAMP columns as character columns using IdbSchemaRowsets::GetSchemas (DBSCHEMA_COLUMNS). This instructs the Data Provider to treat DB2 DATE, TIME, and TIMESTAMP column values as string literals.

You must use the optional DateTime As Char connection option to enable Distributed Query Processor and other SQL Server consumers to select a DB2 default DATE value (0001-01-01) in a DATE or TIMESTAMP column.

The default value for this Boolean property is false. You can set this property in the initialization string DateTime As Char=True or on the Data Links All tab. This property is exposed in the Data Source Wizard All Properties screen.

Warning

You cannot use both DateTime As Char=True and DateTime As Date=True in the same connection. To use these two features, you must use separate connections.

DateTime As Date

Optional OLE DB data source initialization property that instructs the Data Provider to delete the time information in the value of the SQL Server datetime data value, passing only the date information to the IBM DB2 database.

You must use the optional DateTime As Date connection option to allow the distributed query processor and other SQL Server consumers to write SQL Server datetime data values using INSERT and UPDATE statements, or to use SQL Server datetime data values in parameters using SELECT, INSERT, UPDATE, and DELETE statements.

The default value is false. You can set this property in the initialization string DateTime As Date=True or on the Data Links All tab. This property is exposed in the Data Source Wizard All Properties screen.

You cannot use both the DateTime As Char=True and DateTime As Date=True in the same connection. To use these two features, you must use separate connections.

Defer Prepare

Optional OLE DB data source initialization property that 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 minimizes network traffic and frequently improves overall performance.

The default value is false. You can set this property in the initialization string Defer Prepare=True, the Data Links All tab, or the Data Source Wizard Advanced dialog box.

RowSetCacheSize

Optional OLE DB data source initialization property that instructs the Data Provider to pre-fetch rows from DB2 while concurrently processing and returning rows to the data consumer on calls to IRowset::GetNextRows. This feature may improve performance in bulk read-only operations on multiprocessor computers.

The default value for this property is 0, which indicates that the optional pre-fetch feature is off. We recommend setting a value between 50 and 200, with an initial recommended value of 100. This instructs the Data Provider to pre-fetch up to the specified number of row batches, which are stored in the Data Provider's rowset cache. The size of the row batches is automatically determined based on the value for cRows on the OLE DB IRowset::GetNextRows interface specified by the consumer.

You can set this property from the Advanced Options page of the Data Source Wizard, or from the All tab of the Data Links dialog box. You can also specify this property in an OLE DB initialization string or connection string by setting Rowset Cache Size=100.

Max Pool Size

Optional OLE DB data source initialization property that specifies the maximum number of connections that can exist in the connection pool when connection pooling is enabled for the data source.

The default is 100. There is no upper limit for the Max Pool Size property. If you configure a value that is less than 0 for the Max Pool Size property, the default value of 100 is used.

Auth Encrypt

Instructs the Data Provider to encrypt authentication credentials sent to the remote IBM DB2 database server computer across a TCP/IP network connection using Kerberos.

Authentication

Sets the authentication method for the connection.

The default value is Server, which is authentication based 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 data.

AutoCommit

Instructs the Data Provider to automatically commit each command, such as INSERT statements, to reduce the network flows and improve overall performance.

Binary Codepage

Specifies the Host CCSID to use when the Data Provider converts DB2 bytes to and from SQL Server character strings, based on the optional Process binary as character property.

Client Application Name

Instructs the Data Provider to send a custom client application name to the IBM DB2 server for each connection, allowing the DB2 administrator to use this name for accounting, logging and troubleshooting purposes. DB2 for z/OS accepts a 32 byte string.

Connect Timeout

Optional OLE DB data source initialization property that specifies how long a new connection request waits when all the connections in the connection pool are in use. If a connection does not become available before the value of the Connect Timeout property expires, the new connection request fails, and a "connection not available" exception occurs.

The default is 15 seconds.

There is no upper limit for the Connect Timeout property. If you want an infinite time-out period, configure the Connect Timeout property by using the -1 value.

Connection Pooling

Sets the Data Provider-specific client-side connection pooling used in conjunction with max pool size, connection timeout, client application name, and other connection options.

Database Name

DB2 databases can be divided into multiple logical databases for administration purposes, each containing separate tables 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.

Derive Parameters

Optional parameter used with SQL Server Integration Services to help determine the correct length of parameters defined as character data types.

This parameter is ignored when using SQL Server Replication Services and SQL Server Distributed Query Processor.

Use Early Metadata

Optional parameter used with SQL Server Distributed Query Processor and linked server queries that may contain user-defined data types.