• Db2 Iseries Connection String

Info, examples and downloads for 'IBM iSeries Access ODBC driver'. Connection string options and links to more detailed resources.

. In Windows, on the taskbar, click Start, click Settings, and then click Control Panel. Double-click the ODBC icon to display the ODBC Data Source Administrator dialog box. If you are configuring an existing data source, select the data source name and click Configure to display the Microsoft ODBC Driver for DB2 Configuration dialog box.

If you are configuring a new data source click either the User DSN tab, the File DSN tab, or the System DSN tab. Click Add, select Microsoft ODBC Driver for DB2 Driver, and then click Finish to display the Microsoft ODBC Driver for DB2 Configuration dialog box. Type the appropriate values in the fields, and then click Apply. The Microsoft ODBC Driver for DB2 Configuration dialog box contains the following five tabs: The General tab contains the following fields. Parameter Comments Data Source Name A blank field for specifying the name of the data source.

Enter a string that identifies this ODBC data source. The data source is a required parameter that is used to define the data source. The ODBC driver manager uses this attribute value to load the correct ODBC data source configuration from the registry or from a file. For File data sources, this field is used to name the DSN file, which is stored in C: Program Files Common Files ODBC Data Sources. Description A blank field to provide a comment describing this ODBC data source.

The description is an optional parameter and may be left blank. The Connection tab allows the user to configure the basic attributes required to connect to a data source. For the Microsoft ODBC Driver for DB2, the Connection tab has the following fields. Parameter Comments APPC Connection and TCP/IP Connection An option button (radio button) is used to select the network transport. Valid options are APPC Connection (SNA LU 6.2) or TCP/IP Connection. For the default, APPC Connection, the values for APPC local LU alias, APPC remote LU alias, and APPC Mode Name are required.

For TCP/IP Connection, the values for IP address and Network port are required. APPC local LU alias When APPC Connection is selected, this field is the name of the local LU alias configured in Host Integration Server 2004. APPC remote LU alias When APPC Connection is selected, this field is the name of the remote LU alias configured in Host Integration Server 2004. APPC mode name When APPC Connection is selected, this field is the APPC mode and must be set to a value that matches the host configuration and SNA Server configuration. Legal values for the APPC mode include QPCSUPP (common system default often used by 5250), #INTER (interactive), #INTERSC (interactive with minimal routing security), #BATCH (batch), #BATCHSC (batch with minimal routing security), #IBMRDB (DB2 remote database access), and custom modes.

The following modes that support bi-directional LZ89 compression are also legal: #INTERC (interactive with compression), INTERCS (interactive with compression and minimal routing security), BATCHC (batch with compression), and BATCHCS (batch with compression and minimal routing security). The default is typically QPCSUPP. IP address When TCP/IP Connection is selected as the network transport, this field indicates the IP address of the host DB2 server. Network port When TCP/IP Connection is selected as the network port, this field indicates the TCP/IP port used for communication with the target DB2 DRDA service. The default is IP port 446. The Connection tab also includes a Test connection button that may be used to test the connection parameters.

A connection can only be tested after all of the required parameters for the Connection tab and other ODBC data source parameters are configured properly. When this button is clicked, a session is established with the remote DB2 system using ODBC Driver for DB2.

The Security tab allows the user to configure optional attributes used to restrict connections to a data source. For the Microsoft ODBC Driver for DB2, the Security tab has the following fields. Parameter Comments Authentication An option button (radio button) is used to select the type of authentication.

Valid options are Use this username or Use single sign-on. For the default Use this username option, the value for the username is required. Use this username When this option is selected, authentication is based on the username entered in the textbox. A valid user name is normally required to access data on DB2.

A user name can remain optionally in the DSN. The ODBC Driver for DB2 will prompt the user at run-time to enter a valid password. Additionally, the prompt dialog will enable the user to override the user name that is stored in the DSN. Use single sign-on An option button to select whether single sign on or a specific user name should be used. Single sign-on is an optional Host Security feature. Single sign-on enables the administrator to create data source definitions that isolate the logon process from the end user. The user context for single sign-on is the user context associated with the SNA DB2 service.

The AS/400 computer is case-sensitive with regard to user IDs and passwords. When connecting to DB2 for OS/400, user names and passwords must be in uppercase. The AS/400 only accepts a DB2 for OS/400 user ID and password in uppercase. If a DB2 for OS/400 connection fails due to incorrect authentication, the ODBC driver resends the authentication, forcing the user ID and password into uppercase. When connecting to DB2 on IBM mainframes, user names and passwords can be of mixed case; the mainframe is not case-sensitive.

The ODBC driver sends these values in uppercase. DB2 Universal Database (UDB) is case-sensitive.

The user ID is stored in uppercase. The password is stored in mixed case and users must enter the password in the correct case. The ODBC driver sends the password exactly in the case entered by the user. The user ID should contain only the user name, not a combination of the Windows domain name and username.

The Target Database tab allows the user to configure required, as well as optional, attributes used to define the target DB2 system. For the ODBC Driver for DB2, the Target Database tab has the following fields. Parameter Comments Initial catalog This parameter is used as the first part of a three-part fully qualified DB2 table name. It is referred to by different names depending on the DB2 platform. In DB2 for OS/390 and DB2 for MVS, this parameter is referred to as LOCATION. The SYSIBM.LOCATIONS table lists all the accessible locations.

To find the location of the DB2 that you need to connect to on these platforms, ask the administrator to look in the TSO Clist DSNTINST under the DDF definitions. These definitions are provided in the DSNTIPR panel in the DB2 installation manual. In DB2/400 on OS/400, this property is referred to as RDBNAM. The RDBNAM value can be determined by invoking the WRKRDBDIRE command from the console to the OS/400 system. If there is no RDBNAM value, then a value can be created using the Add option. In DB2 Universal Database, this property is referred to as DATABASE. Package collection The name of the DRDA target collection (AS/400 library) where the Microsoft ODBC Driver for DB2 should store and bind DB2 packages.

This can be the same as the default schema. The ODBC Driver for DB2, which is implemented as an IBM DRDA Application Requester, uses packages to issue dynamic and static SQL statements. The ODBC driver creates packages dynamically in the location that the user points to using the Package Collection parameter. By default, the ODBC Driver for DB2 automatically creates one package in the target collection, if one does not exist, at the time the user issues their first SQL statement. The package is created with GRANT EXECUTE authority to a single only, where AUTHID is based on the user ID value configured in the data source. The package is created for use by SQL statements issued under the same isolation level based on the Isolation Level value configured in the data source. Problems can arise in multi-user environments.

For example, if a user specifies a Package Collection value that represents a DB2 collection used by multiple users, but this user does not have authority to GRANT execute rights to the packages to other users (for example, PUBLIC), then the package is created only for use by this user. This means that other users may be unable to access the required package. The solution is for an administrative user, with package administrative rights (for example., PACKADM authority in DB2 for OS/390), to create a set of packages for use by all users. The ODBC Driver for DB2 ships with two utility programs for use by administrators to create packages. The crtpkg.exe tool is a command line utility for the administrator to create packages. The crtpkgw.exe tool is a Windows GUI utility used for the same purpose. Either of these utilities can be run using a privileged user ID to create packages in collections accessed by multiple users.

These utilities will create sets of packages and grant EXECUTE privilege to PUBLIC for all (see descriptions under the Default Isolation parameter). The packages created are as follows: AUTOCOMMIT package (MSNC001 is only applicable on DB2/400) READUNCOMMITTED package (MSUR001) REPEATABLEREAD package, (MSRS001) READCOMMITTED package, (MSCS001) SERIALIZABLE package (MSRR001). After being created, the packages are listed in the DB2 (mainframe) SYSIBM.SYSPACKAGE, the DB2 for OS/400 QSYS2.SYSPACKAGE, and the DB2 Universal Database (UDB) SYSIBM.SYSPACKAGE catalog tables.

Default schema The name of the Collection where the ODBC Driver for DB2 looks for catalog information. The Default schema is the SCHEMA name for the target collection of tables and views. The ODBC driver uses Default Schema to restrict results sets for popular operations, such as enumerating a list of tables in a target collection (for example, ODBC Catalog SQLTables).

For DB2, the Default Schema is the target AUTHENTICATION (User ID or owner). For DB2/400, the Default Schema is the target COLLECTION name. For DB2 Universal Database (UDB), the Default Schema is the SCHEMA name. If the user does not provide a value for Default Schema, the ODBC driver uses the USERID provided at logon. For DB2/400, the driver uses QSYS2 if no collection is found matching the USERID value. This default is inappropriate in many cases so it is essential that the Default Schema value in the data source be defined.

DBMS Platform The target DB2 platform property value is used to optimize performance of the ODBC driver when executing operations such as data conversion. The default value is DB2/MVS. Default Qualifier The name of the schema (collection/owner) with which to fully qualify unqualified object names.

This attribute allows the user to access database objects without fully-qualifying the objects using a collection (schema) qualifier. The ODBC driver sends this value to DB2 using a SET CURRENT SQLID statement, instructing the DBMS to use this value when locating unqualified objects (for example, tables and views) referenced in SQL statements. If you do not set a value for default qualifier, then no SET statement is issued by the ODBC driver.

This ODBC connection attribute is only valid when connecting to DB2 for MVS (OS/390, z/OS). Alternate TP Name The remote transaction program name is optional. For example, it is used when configuring an off-line DB2 demo link service connection. Distributed transactions When this option is checked, two-phase commit (distributed unit of work) is enabled.

Distributed transactions are handled using Microsoft Transaction Server, Microsoft Distributed Transaction Coordinator, and one of the HIS Resync services. Process binary as character When this option is checked, it indicates that binary data fields should be processed as characters. This option treats binary data type fields (with a CCSID of 65535) as character data type fields on a per-data source basis.

The Host CCSID and PC Code Page values are required input and output parameters. See the Locale tab. The Locale tab allows the user to configure the parameters used for character conversion between the client computer and the DB2 server. Two versions of the Locale tab are possible depending on which versions of SNA Server client software and ODBC for DB2 Driver client software are installed. For the ODBC Driver for DB2, the Locale tab has the following fields.

Parameter Comments Host CCSID The coded character set identifier (CCSID) matching the DB2 data as represented on the remote computer. This property is required when processing binary data as character data. Unless the Process Binary as Character value is set, character data is converted based on the DB2 column CCSID and configured ANSI code page.

This parameter defaults to U.S./Canada (37). PC code page This parameter indicates the personal computer code page to use. It is required when processing binary data as character data. Unless the Process Binary as Character value is set, character data is converted based on the default ANSI code page configured in Windows. The default value for this property is Latin 1 (1252). Click OK or Cancel when data entry is finished. If you click OK, the values specified become the defaults when an application connects to this data source.

Db2 Iseries Connection String

These default values can be changed at any time using this procedure to reconfigure the data source. An ODBC application can override these defaults by connecting to the data source using a connection string with alternate values. To download updated Host Integration Server 2004 Help from www.microsoft.com, go to. Copyright © 2004 Microsoft Corporation. All rights reserved.