Data Source Wizard (DB2)

You can use the Data Source Wizard to guide you through the steps to configure and save data source information that is required to connect the Data Provider for DB2, ODBC Driver for DB2, BizTalk Adapter for DB2, and ADO.NET Provider for DB2 (Data Provider) to remote IBM DB2 database servers. Consumers, such as Visual Studio and BizTalk Server, will load the Data Source Wizard for use in defining and re-configuring connections to IBM DB2 database servers. The Data Source Wizard helps to simplify configuring and testing network connections, working with packages, defining character string code page conversions, working with security and encryption, and validating and saving the configuration.

The following sections describe the Data Access Wizard screens and the actions that you can perform on each screen.

• Welcome

• Data Source

• TCP/IP Network Connection

• APPC Network Connection

• DB2 Database

• Locale

• Security

• Advanced Options

• All Properties

• Validation

• Saving Information

Welcome

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

Data Source

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/z/OS (which refers to DB2 for z/OS). Other values include DB2/400 (which refers to DB2 for IBM i), DB2/NT (which refers to DB2 for Windows), and DB2/6000 (which refers to DB2 for Linux or UNIX).

Network type

The following two connectivity options are supported for you to use:

• SNA LU6.2 APPC (Advanced Program to Program Communications using Systems Network Architecture) network connections to remote IBM DB2 database servers that are running on IBM mainframe and midrange host computers.

• TCP/IP network connections to remote IBM DB2 database servers that are running on Linux, UNIX, and Windows operating systems.

TCP/IP Network Connection

The TCP/IP Network Connection screen can be used to configure required and optional parameters.

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. For DB2/400, the default value is TCP/IP port 446. Other IBM DB2 platforms support multiple concurrent database instances, each with a unique TCP/IP port number.

Certificate Common Name

Optionally, you can specify a server certificate common name to instruct the Data Provider to utilize Secure Sockets Layer (SSL) V3.0 or Transport Layer Security (TLS) V1.0 encryption. If you use SSL or TLS, it will improve security by encrypting authentication credentials and data. By default, this value is set to empty string (no SSL or TLS).

Distributed transactions

Optionally, to enlist the Data Provider in distributed transactions, you can select this property to support two-phase commit protected DB2 DUW (distributed unit of work).

APPC Network Connection

The APPC Network Connection screen can be used to configure required and optional parameters.

Local LU alias

The Data Provider requires an APPC local LU alias, when you connect using SNA LU6.2. Select or enter the name of the APPC local LU alias configured in Host Integration Server.

Remote LU alias

The Data Provider requires an APPC remote LU alias, when you connect using SNA LU6.2. Select or enter the name of the APPC remote LU alias configured in Host Integration Server.

Mode name

The Data Provider requires an APPC mode name, when connecting via SNA LU6.2. Select or enter the name of the APPC mode name configured in Host Integration Server. A common value for DB2/z/OS is IBMRDB.

Security type

Optionally you can specify the APPC conversation security to identify the PC user to the DB2 database server. The following table describes security level settings.

Distributed transactions

Optionally, to enlist the Data Provider in distributed transactions, you can select this property to support two-phase commit protected DB2 DUW (distributed unit of work).

DB2 Database

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 requires this value to connect to an initial catalog on the DB2 database server. The following table describes the DB2 versions and accepted string types.

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. The following table describes the DB2 versions and accepted string types.

Default schema

Optionally, you can specify a string to instruct the Data Provider to restrict schema queries to a single database schema, which improves efficiency and performance. The default is an empty string.

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, Data consumers can present to the user a list of all objects in the database catalog. The following table describes the DB2 versions and accepted string types.

Default qualifier

Optionally, you can specify a string to instruct the Data Provider to set an environment option for a default qualifier, with which to inform the DB2 server in which schema to locate database objects. The default is an empty string.

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>. Data consumers may issue SQL statements with one-part or unqualified object names. The following table describes the DB2 versions and accepted string types.

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 for database name and an 8 byte string for table space name. You can specify the database name only or database name combined with table space name, for example DBASE1.TSPACE1.

Locale

Optionally, to increase performance and reduce the impact on the remote database, you can select the coded character set identifier (CCSID) for the remote DB2 database (host) and local data consumer (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 (https://go.microsoft.com/fwlink/?LinkID=181017).

Host CCSID

The Data Provider requires a value for Host CCSID (Coded Character Set Identifier) with which to perform code page conversions on string data. The default Host CCSID value is EBCDIC – U.S./Canada [37]. Typically, IBM DB2 database servers for z/OS and IBM i utilize EBCDIC (Extended Binary Coded Decimal Interchange Code). For more information, see SNA Internationalization Programmer's Reference (https://go.microsoft.com/fwlink/?LinkID=181017).

PC Code page

The Data Provider requires a value for PC Code Page with which to perform code page conversions on string data. The default PC code page is ANSI – Latin I [1252]. Typically, data consumers use either ANSI (American National Standards Institute) or Unicode. For more information, see SNA Internationalization Programmer's Reference (https://go.microsoft.com/fwlink/?LinkID=181017).

Security

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

Security method

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

Interactive sign-on

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

User name

The following table describes the DB2 database version and accepted string types.

Password

The following table describes the DB2 database version and accepted string types.

Password confirmation

You must enter the same value as Password.

Authentication method

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

The following table describes authentication options.

Save password

Optionally, you can 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 user name 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 user name 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 (https://go.microsoft.com/fwlink/?LinkID=180764).

Principal name

This property is required for use with Kerberos authentication.

Advanced Options

The Advanced Settings screen enables you to configure additional optional settings.

Connection pooling

Optionally, you can specify TRUE to instruct the Data Provider to use client-side connection pooling. The default is FALSE (no pooling).

Read-only

Optionally, the Data Provider can declare the read-only access method when connecting to the DB2 database server.

Defer Prepare

Optionally, you can specify TRUE to instruct the Data Provider to optimize the processing of parameterized database commands. The default value is FALSE.

• For the INSERT, UPDATE, and DELETE commands, the Data Provider can combine PREPARE, EXECUTE, and COMMIT commands into one network flow to the remote database.

• For the SELECT command, the Data Provider can combine PREPARE and EXECUTE commands into one network flow. This minimizes network traffic and frequently improves overall performance.

  Derive Parameters

  Optionally, you can specify TRUE to instruct the Data Provider to verify and correct parameter lengths for character data types, on behalf of data consumers such as SQL Server Integration Services package designer and import/export wizard. The default is FALSE.

  Alternate TP name

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

All Properties

The All Properties screen enables you to configure more detailed and optional properties. You can edit these properties 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.

• Affiliate Application

• Allow Saving Password

• Authentication

• AutoCommit

• Certificate Common Name

• Client Accounting

• Client Application Name

• Client User ID

• Connection Pooling

• Connection Timeout

• DateTime As Char

• DateTime As Date

• Default Qualifier

• Default Schema

• Defer Prepare

• Derive Parameters

• Host CCSIC

• Initial Catalog

• Max Pool Size

• Network Address

• Network Port

• Network Type (read-only)

• Package Collection

• Password

• PC Code Page

• Read-Only

• Rowset Cache Size

• Security Method

• Security Principal

• Units of Work

• User Name

Validation

Validation

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.

Connect

Click the Connect button to perform a test connection.

Packages

Click the Packages button to create the DB2 packages required to execute SQL statements in a multi-user environment.

Sample Query

Click the Sample Query button to retrieve a list of tables in the default schema.

Saving Information

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 you create a data link by 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 Visual Studio Server Explorer and 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 data consumers, you can save the configuration in a Managed initialization text string file format.

ODBC

The Microsoft Office Excel and other ODBC consumers present a standard ODBC Data Source Administrator dialog, with which the user can see the ODBC data sources.

Finish

The Completing the Data Source Wizard screen displays a summary and status of your configuration. Click Finish to implement your actions.

See Also

Data Integration (Configuration)