About Connect ODBC for DB2

Connect ODBC for DB2 (the “DB2 driver”) supports the following database systems:

DB2 for Windows NT

DB2 Common Server

The file name for the DB2 driver is IVDB2nn.DLL, where nn denotes the revision level.


DataDirect Connect ODBC for DB2

Copyright 1998 INTERSOLV Inc. All rights reserved. INTERSOLV and DataDirect are registered trademarks of INTERSOLV, Inc. Connect ODBC is a trademark of INTERSOLV, Inc. Other company or product names mentioned herein may be trademarks or registered trademarks of their respective companies.


DB2 System Requirements
System Requirements

The server requirement for all platforms is the same:

The DB2 Server must be installed as the Server Version (not the Local Version).

To access the DB2 family of databases, you must have one of the following software packages:

IBM DB2 Client Application Enabler (CAE) for Windows 9x and Windows NT, version 2.1 or higher

IBM DB2 SDK for Windows 9x and Windows NT, version 2.1 or higher


Configuring DB2 Data Sources
Configuring Data Sources

To configure a DB2 data source, do the following:

1	Start the ODBC Administrator to display list of data sources.

2	If you are configuring an existing data source, select the data source name and click Configure to display the ODBC DB2 Driver Setup dialog box.

If you are configuring a new data source, click Add. A list of installed drivers appears. Select IBM DB2 and click Finish to display the ODBC DB2 Driver Setup dialog box.

3	Specify a data source name and database name. Specifying a database description is optional. Click Apply.

4	Click the Advanced tab to configure additional, optional settings for the data source. Click Apply.

5	You must catalog any database you want to access. Click the Catalog Dbtab.

On this tab, type an alias name for the remote database, the name of the remote database, and an alias for the node (the node name could describe the remote node name and the communication protocol used to access it). Optionally, you can enter a comment. Click Add to add the alias name to the list in the Database Name drop-down list on the Logon dialog box.

The Catalog Db tab also lets you uncatalog remote databases. Select the alias you want to delete from the Alias Name drop-down list and click Delete.

Click Apply.

6	To provide information about the remote site for the DB2 databases, click the Catalog Node tab. On the Catalog Node tab, select a node name (only names of previously cataloged nodes are available), the name of the server or number of the adapter (for NetBIOS nodes), the name of the remote node where the DB2 server is installed, and the type of communication protocol to use to access the node. Optionally, enter a comment. Click Add.

The Catalog Node tab also lets you uncatalog nodes. Select the alias you want to delete from the Node Name drop-down list and click Delete.

Click Apply.

7	Click the Advanced tab, then Translate to display the Select Translator dialog box, whch lists the translators specified in the ODBC Translators section of the system information. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set.

Select a translator, then click OK to close this dialog box and perform the translation.

8	Click OK or Cancel. If you click OK, the values you have specified become the defaults when you connect to the data source. You can change these defaults by using this procedure to reconfigure your data source. You can override these defaults by connecting to the data source using a connection string with alternate values.


Connecting to DB2 Using a Logon Dialog Box
Connecting to a Data Source Using a Logon Dialog Box

Some ODBC applications display a logon dialog box when you are connecting to a data source. In these cases, the data source name has already been specified.

In the logon dialog box, do the following:

1	Type the name of the remote database or select the name of the remote database from the Database Name drop-down list.

You must have cataloged any database you want to access from the client. (See the topic Configuring Data Sources for information on how to do this.)

2	If required, type your user name (authorization ID).

3	If required, type your password.

4	Click OK to complete the logon and to update the values in the system information.


Connecting to DB2 Using a Connection String
Connecting to a Data Source Using a Connection String

If your application requires a connection string to connect to a data source, you must specify the data source name that tells the driver which section in the system information to use for the default connection information. Optionally, you may specify attribute=value pairs in the connection string to override the default values stored in the system information. These values are not written to the system information.

You can specify either long or short names in the connection string.  The connection string has the form:

DSN=data_source_name[;attribute=value[;attribute=value]...]

An example of a connection string for DB2 is

DSN=DB22 TABLES;DB=PAYROLL;UID=JOHN;PWD=XYZZY;GRP=ACCTNG

The paragraphs that follow give the long and short names for each attribute, as well as a description. The defaults listed are initial defaults that apply when no value is specified in either the connection string or in the data source definition in the system information. If you specified a value for the attribute when configuring the data source, that value is your default.

ApplicationUsingThreads (AUT): ApplicationUsingThreads={0 | 1}. Ensures that the driver works with multi-threaded applications. The default is 1, which makes the driver thread-safe. When using the driver with single-threaded applications, you may set this option to 0 to avoid additional processing required for ODBC thread safety standards.

CursorBehavior (CB): CursorBehavior={0 | 1}. Determines whether cursors are preserved or closed at the end of each transaction. Valid values are 1 (preserve) and 0 (close, the initial default). Set this attribute to 1 if you want cursors to be held at the current position when the transaction ends. Doing so may impact the performance of your database operations.

This attribute is not valid for SQL/DS.

When you select Preserve, the driver returns SQL_CB_PRESERVE from SQLGetInfo (SQL_CURSOR_COMMIT_BEHAVIOR). But only Select statements and prepared Update or Delete . . . Where Current of Cursor statements are preserved when the transaction ends. All other prepared statements are closed and deleted.

Database (DB): Name of the database to which you want to connect.

DataSourceName (DSN): Identifies a DB2 data source configuration in the system information. Examples include "Accounting" or "DB2-Serv1."

Groups (GRP): Determines which tables you can access. Your system administrator may have placed you in a "group" of users and granted table access to the entire group. If this is the case, set Groups to the names of any groups to which you belong; separate each name with a comma. Alternatively, Groups=ALL lets your application see all table names even if you cannot access the table.

LogonID (UID): Default logon ID used to connect to your DB2 database. A logon ID is required only if security is enabled on your database. If so, contact your system administrator to get your logon ID.

Password (PWD): Password.

Sysibm (SI): On most DB2 systems, SYSIBM is the owner of the catalog system tables. If you have read access to the system tables, you do not need to change this option.

If  you do not have read access, the database administrator must create a view of the system tables in another account and give you permission to use that view. In this case, specify the Authorization ID for the account that owns the views of the system tables.


DB2 Data Types
Data Types

The DB2 data types map to the standard ODBC data types as follows.

DB2	ODBC
Char	SQL_CHAR
Char() for Bit Data	SQL_BINARY
Date	SQL_TYPE_DATE
Decimal	SQL_DECIMAL
Float	SQL_DOUBLE
Integer	SQL_INTEGER
Long Varchar	SQL_LONGVARCHAR
Long Varchar for Bit Data	SQL_LONGVARBINARY
Smallint	SQL_SMALLINT
Time	SQL_TYPE_TIME
Timestamp	SQL_TYPE_TIMESTAMP
Varchar	SQL_VARCHAR
Varchar() for Bit Data	SQL_VARBINARY
Note The Graphic, Vargraphic, and Long Vargraphic data types are not supported.


Isolation and Lock Levels Supported by DB2
Isolation and Lock Levels Supported

DB2 supports isolation levels 0 (read uncommitted), 1 (read committed), and 2 (repeatable read). It supports record-level locking.


DB2 ODBC Conformance Level
ODBC Conformance Level

The API functions supported are listed in “Supported ODBC Functions,” found in the General Help on DataDirect ODBC Drivers. In addition, the following X/Open functions are supported:

SQLProcedures

SQLProcedureColumns

The driver supports the minimum SQL grammar.


Number of Connections and Statements Supported by DB2
Number of Connections and Statements Supported

The DB2 database system supports a single connection and multiple statements per connection.


ODBC DB2 Driver Setup Dialog Box

Use the ODBC DB2 Driver Setup dialog box to create new DB2 data sources or configure existing data sources.

Data Source Name: A string that identifies this DB2 data source configuration in the system information. Examples include "Accounting" or "DB2-Serv1."

Description: An optional long description of a data source name. For example, "My Accounting Database" or "DB2 on Server #1."

Database Name: The name of the database to which you want to connect by default.

Advanced Tab

Displays the Advanced tab, where you can configure additional options for this data source.

Catalog Db Tab

Displays the Catalog Db tab, where you can catalog databases.

Catalog Node Tab

Displays the Catalog Node tab, where you enter information about the remote site for DB2 databases.

OK

Cancel

Apply


Advanced tab, ODBC DB2 Driver Setup Window
Advanced Tab, ODBC DB2 Driver Setup Dialog Box

Use the Advanced Tab on the ODBC DB2 Driver Setup dialog box to specify optional settings when you create new DB2 data sources or configure existing data sources.

Owner of Catalog Tables: On most DB2 systems, SYSIBM is the owner of the catalog system tables. If you have read access to the system tables, you do not need to change this option.

If you do not have read access, the system administrator must create a view of the system tables in another account and give you permission to use that view. In this case, specify the Authorization ID for the account that owns the views of the system tables.

Groups: A value to determine which tables you can access. Your system administrator may have placed you in a "group" of users and granted table access to the entire group. If this is the case, specify the names of any groups to which you belong; separate each name with a comma. Alternatively, specify the word ALL so that you see all table names even if you cannot access the table.

Default Authorization ID: The default Logon ID used to connect to your DB2 database. A Logon ID is required only if security is enabled on your database. Your ODBC application may override this value or you may change this value in the logon dialog box.

Cursor Behavior: Select Preserve if you want cursors to be held at the current position when the transaction ends. Otherwise, leave this set to Close.  Selecting Preserve may impact the performance of your database operations. This setting does not apply to SQL/DS.

When you select Preserve, the driver returns SQL_CB_PRESERVE from SQLGetInfo (SQL_CURSOR_COMMIT_BEHAVIOR). But only Select statements and prepared Update or Delete . . . Where Current of Cursor statements are preserved when the transaction ends. All other prepared statements are closed and deleted.

Application Using Threads: A setting that ensures that the driver works with multi-threaded applications. You can clear this check box when using the driver with single-threaded applications. Turning off this setting avoids additional processing required for ODBC thread safety standards.

Translate Button

Displays the Select Translator dialog box, where you can translate your data from one character set to another. Choose the INTERSOLV OEM ANSI translator to translate your data from the IBM PC character set to the ANSI character set.

OK

Cancel

Apply


Logon to DB2 Dialog Box

Database Name: Enter the name of the remote database or select the name of the remote database from the Database Name box.

You must have cataloged any database you wish to access from the client.

Authorization ID: Enter your user name (authorization ID).

Password: Enter your password.

OK

Completes the logon and writes the values to the system information.


Catalog Remote Database Dialog Box
Catalog Db Tab, ODBC DB2 Driver Setup Dialog Box

Catalogs the remote database you wish to access.

Alias Name: Enter an alias name for the remote database.

Database Name: Enter the name of the remote database.

Node Name: Enter a name as an alias for the node. The node name could describe the remote node name and the communication protocol used to access it.

Comment: At your option, you can enter a comment.

Add

Catalogs the entry and adds the alias name to the Database Name drop-down list on the Logon dialog box.

Delete

Uncatalogs the entry selected in the Alias Name drop-down list.

OK

Cancel

Apply


Catalog Node Dialog box
Catalog Node Tab, ODBC DB2 Driver Setup Dialog Box

Provides information about the remote site for DB2 databases.

Node Name: Select a node name. Only names of previously cataloged nodes are available.

Server/Adapter: Enter the name of the server or the number of the adapter (for NetBIOS nodes).

Remote Node Name: Enter the name of the remote node (the node where the server is installed).

Node Type: Enter the type of communication protocol to use to access the node (IPX/SPX, NetBIOS, or TCP/IP).

Comment: At your option, you can enter a comment.

Add

Catalogs the entry.

Delete

Uncatalogs the entry selected in the Node Name drop-down list.

OK

Cancel

Apply


Apply Button

Writes the settings you have specified to the system information. These settings remain in effect until you change them in this dialog box. Clicking Cancel does not affect settings that have been applied.


OK Button

Writes the settings you have specified to the system information and closes the dialog box.


Cancel Button

Closes the dialog box without saving settings that have not been applied.