Copyright: , 1996 INTERSOLV


Contents, DB2/2 Driver Help
	DataDirect ODBC  DB2/2 Driver
About the DB2/2 Driver

System Requirements

Configuring Data Sources

DB2/2 and DDCS/2 Gateway

DB2/6000 and DDCS/6000 Gateway

Connecting to DB2/2 Using a Logon Dialog Box

Connecting to DB2/2 Using a Connection String

Data Types

Isolation and Lock Levels Supported

ODBC Conformance Level

Number of Connections and Statements Supported

Copyright


About the DB2/2 Driver

The DB2/2 driver supports the use of the Client Application Enabler (CAE) 2.1 interface. The DB2/2 driver supports the following database systems:

DB2 for OS/2 (formerly called Database Manager for OS/2) and DB2 for Windows NT

DB2/6000

SQL/400 (through the DDCS or DDCS/6000 gateway)

MVS DB2 (through the DDCS or DDCS/6000 gateway)

SQL/DS (through the DDCS or DDCS/6000 gateway)

The filename for the DB2/2 driver is IVDB2nn.DLL.


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


DB2/2 System Requirements
System Requirements

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

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

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

System requirements are as follows:

The DB2/2, DB/NT, or DB/6000 Server must be installed as the Server Version (not the Local Version).

To use the DDCS gateway, you must install the DDCS Multi-User Edition (not the Single-User Edition).

DB2/2 requires network protocols from products such as Communications Manager from Extended Services version 1.0, Communications Manager/2 version 1.0, Network Transport Services/2 version 1.0, LAN Services,  Novell NetWare, or numerous TCP/IP vendors.


Configuring DB2/2 Data Sources
Configuring Data Sources

To configure a DB2/2 data source, do the following:

1	Start the ODBC Administrator by double-clicking the ODBC icon in the the Windows Control Panel.  A list of data sources appears.

2	If you are configuring a new data source, click Add.  A list of installed drivers appears.  Select INTERSOLV DB2/2 and click OK.

If you are configuring an existing data source, select the data source name and click Setup.

The DB2/2 ODBC Setup dialog box appears.

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

4	To configure additional, optional settings for the data source, click Advanced. The Advanced dialog box appears.

5	You must catalog any database you want to access.  To do so, click the Catalog Db button.  The Catalog Db dialog box appears.

In this dialog box, type an alias name, the name of the database, and the name of the node (like an alias for the remote node where the server is installed and the protocol used to access it).  Click Add.  The alias name you added will be listed in the Database Name drop-down list in the logon dialog box when you exit this dialog box.

This dialog box also lets you uncatalog remote databases.  To do this, select the alias you want to delete from the Alias Name drop-down list and click Delete.

5	To provide information about the remote site for the DB2/2 databases, click Catalog Node. In the Catalog Node dialog box, 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.

This dialog box also lets you uncatalog remote databases.  To do this, select the alias you want to delete from the Alias Name drop-down list and click Delete.

This dialog box also lets you uncatalog nodes.  To do this, select the alias you want to delete from the Node Name drop-down list and click Delete.

6	Click Translate to perform a translation of your data from one character set to another. The Select Translator dialog box appears in which you can select a translator to peform the translation. INTERSOLV provides a translator named INTERSOLV OEM ANSI that translates your data from the IBM PC character set to the ANSI character set. Click OK to leave this dialog box and perform the translation.

The translators that are listed in this dialog box are determined by the values listed in the ODBC Translators section of your ODBCINST.INI file.

7	Click OK to write these values to ODBC.INI. These values are now the defaults when you connect to the data source.  You can change the defaults by configuring your data source again.  You can override the defaults by connecting to the data source using a connection string with alternate values.


DB2/2 and DDCS/2 Gateway

Access to DB2/2 databases, or other databases via the DDCS/2 gateway, requires that you bind to tables and grant privileges to all users.  These processes are described in the following topics.

Binding

Before you can access a table, you must bind to the database. You must bind to every database you intend to access.  o do this, enter the following commands on the server:

SQLBIND QECSV1.BND database_name

SQLBIND QERRV1.BND database_name

SQLBIND QEURV1.BND database_name

SQLBIND QECSWHV1.BND database_name

SQLBIND QERRWHV1.BND database_name

SQLBIND QEURWHV1.BND database_name

You may need to copy the bind files from the installation directory to the server.

database_name is the name of the database you want to access.

Note for SQL/DS Users:  For binding to SQL/DS databases, enter only the first three of these commands.  The files QECSWHV1.BND, QERRWHV1.BND, and QEURWHV1.BND will not work with SQL/DS.

For SQL/400 Users

If you receive the error

'QSYS.QE' is an undefined name

you must create a collection named QE.  To do this, enter the following command on the host AS/400:

CREATE COLLECTION QE

Then issue the SQLBIND commands again.

Granting Privileges

Users must be granted the necessary privileges before they can access a database or table through a gateway.

DB2/2 users are assigned an Authorization ID.  There are three categories of Authorization IDs:  administrator, local administrator, and user.  The third category, user, must be granted the privilege to access DB2/2 tables.

To grant the necessary privileges, an administrator must run Query Manager or the DBM Command Line Interface and execute the appropriate GRANT statement.

For DB2/2 databases and databases that reside on DDCS/2 connecting to DB2:

GRANT EXECUTE, BIND ON PROGRAM QE.QECSV1 TO authorization_list

GRANT EXECUTE, BIND ON PROGRAM QE.QERRV1 TO authorization_list

GRANT EXECUTE, BIND ON PROGRAM QE.QEURV1 TO authorization_list

GRANT EXECUTE, BIND ON PROGRAM QE.QECSWHV1 TO authorization_list

GRANT EXECUTE, BIND ON PROGRAM QE.QERRWHV1 TO authorization_list

GRANT EXECUTE, BIND ON PROGRAM QE.QEURWHV1 TO authorization_list

For databases that reside on the DDCS/2 gateway connecting to SQL/DS:

GRANT RUN ON QE.QECSV1 TO authorization_list

GRANT RUN ON QE.QERRV1 TO authorization_list

GRANT RUN ON QE.QEURV1 TO authorization_list

GRANT RUN ON QE.QECSWHV1 TO authorization_list

GRANT RUN ON QE.QERRWHV1 TO authorization_list

GRANT RUN ON QE.QEURWHV1 TO authorization_list

For databases that reside on the DDCS/2 gateway connecting to SQL/400:

GRANT RUN ON PROGRAM QE.QECSV1 TO authorization_list

GRANT RUN ON PROGRAM QE.QERRV1 TO authorization_list

GRANT RUN ON PROGRAM QE.QEURV1 TO authorization_list

GRANT RUN ON PROGRAM QE.QECSWHV1 TO authorization_list

GRANT RUN ON PROGRAM QE.QERRWHV1 TO authorization_list

GRANT RUN ON PROGRAM QE.QEURWHV1 TO authorization_list

authorization_list is a list of comma-separated Authorization IDs or Group IDs, or PUBLIC if you want to grant access to all users.


DB2/6000 and DDCS/6000 Gateway

Before you can access a table via DB2/6000 or the DDCS/6000 gateway, you must bind to the database and grant each user access to it.  To do this, enter the following commands on the client computer from the DBM Command Line Interface:

BIND QECSV1.BND BLOCKING ALL GRANT authorization_list

BIND QERRV1.BND BLOCKING ALL GRANT authorization_list

BIND QEURV1.BND BLOCKING ALL GRANT authorization_list

BIND QECSWHV1.BND BLOCKING ALL GRANT authorization_list

BIND QERRWHV1.BND BLOCKING ALL GRANT authorization_list

BIND QEURWHV1.BND BLOCKING ALL GRANT authorization_list

authorization_list is a list of comma-separated Authorization IDs or Group IDs, or PUBLIC if you want to grant access to all users.

Note for SQL/DS Users:  For binding to SQL/DS databases, enter only the first three of these commands.  The files QECSWHV1.BND, QERRWHV1.BND, and QEURWHV1.BND will not work with SQL/DS.

For SQL/400 Users

If you receive the error

'QSYS.QE' is an undefined name

you must create a collection named QE.  To do this, enter the following command on the host AS/400:

CREATE COLLECTION QE

Then issue the BIND commands again.


Connecting to DB2/2 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 ODBC.INI.


Connecting to DB2/2 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 ODBC.INI section 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 ODBC.INI.  These values are not written to ODBC.INI.

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/2 is

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

The following table gives the long and short names for each attribute, as well as a description.

The defaults listed in the table are initial defaults that apply when no value is specified in either the connection string or in the data source definition in ODBC.INI.  If you specified a value for the attribute when configuring the data source, that value is your default.

Attribute	Description
DataSourceName (DSN)	Identifies a DB2/2 data source configuration in ODBC.INI.  Examples include "Accounting" or "DB2/2-Serv1."
Database (DB)	Name of the database to which you want to connect.
Sysibm (SI)	On most DB2/2 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. If you are using DDCS/2 to connect to a SQL/400 database, specify a comma-separated list of the Collection IDs to which you want to have access in your ODBC application. If you are using DDCS/2 to connect to a SQL/DS database, specify SYSTEM as the owner.
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/2 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.
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.


DB2/2 Data Types
Data Types

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

DB2/2	ODBC
Char	SQL_CHAR
Char() for Bit Data	SQL_BINARY
Date	SQL_DATE
Decimal	SQL_DECIMAL
Float	SQL_DOUBLE
Integer	SQL_INTEGER
Long Varchar1	SQL_LONGVARCHAR
Long Varchar for Bit Data1	SQL_LONGVARBINARY
Smallint	SQL_SMALLINT
Time	SQL_TIME
Timestamp	SQL_TIMESTAMP
Varchar2	SQL_VARCHAR
Varchar3	SQL_LONGVARCHAR
Varchar() for Bit Data2	SQL_VARBINARY
Varchar() for Bit Data3	SQL_LONGVARBINARY
1	Not supported for SQL/400

2	For DB2/2, DB2/6000, and SQL/400; SQL/DS and MVS DB2 up to 254

3	For SQL/DS and MVS DB2 over 254

Note  The Graphic, Vargraphic, and Long Vargraphic data types are not supported.


Isolation and Lock Levels Supported by DB2/2
Isolation and Lock Levels Supported

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


DB2/2 ODBC Conformance Level
ODBC Conformance Level

The DB2/2 driver supports the Core, Level 1, and Level 2 API functions listed in Supported ODBC Functions.  The driver supports the minimum SQL grammar.


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

The DB2/2 family of database systems supports a single connection and multiple statements per connection.


DB2/2 ODBC Setup
DB2/2 ODBC Setup Dialog

Use the DB2/2 ODBC Setup dialog to create new DB2/2 data sources or configure existing data sources.

Data Source Name:  A string that identifies this DB2/2 data source configuration in ODBC.INI.  Examples include "Accounting" or "DB2/2-Serv1."

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

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

OK

Creates or modifies the current data source using the options you specify.

Cancel

Exits the ODBC Setup dialog without creating or modifying a data source.

Advanced

Displays the Advanced Dialog which offers additional configuration options for this data source.


Advanced Dialog

The Advanced data source settings are optional.

Owner of Catalog Tables:  On most DB2/2 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.

If you are using DDCS/2 to connect to a SQL/400 database, specify a comma-separated list of the Collection IDs to which you want to have access in your ODBC application.

If you are using DDCS/2 to connect to a SQL/DS database, specify SYSTEM as the owner.

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/2 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.

Catalog Db:  You must catalog any database you wish to access. To do so, click Catalog. The Catalog Remote Database dialog box appears.

Catalog Node: Displays the Catalog Node dialog box to let you provide informaion about the remote site for DB2/2 databases.

Translate

Displays the Select Translator dialog box to allow you to perform a translation of 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.

Close

Returns to the DB2/2 ODBC Setup dialog box where you can click the OK button to write these settings to the ODBC.INI file.


Logon to DB2/2 Dialog
Logon to DB2/2

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.

If you leave your user name and password blank, the ODBC application assumes you have previously logged on using SQLLOGN2 under DOS or using User Profile Management under OS/2. If you have not, the application returns an error. You must either enter your user name and password in the dialog box or log on using SQLLOGN2 and STARTDRQ under DOS or using User Profile Management under OS/2. See the IBM Extended Services for OS/2 Guide to DB2/2 Clients for information regarding these two statements.


Catalog Remote Database Dialog
Catalog Remote Database

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, like an alias for the node. The node name could describe the remote node name and the communication protocol used to access it.

Comment:  Optionally, enter a comment.

Add

Catalogs the entry.

Delete

Uncatalogs the entry.


Catalog Node Dialog
Catalog Node

Provides information about the remote site for DB2/2 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.

Comment:  Optionally, enter a comment.

Add

Catalogs the entry.

Delete

Uncatalogs the entry.