Export (0) Print
Expand All

ADO Connection Object in the OLE DB Provider for DB2

The ADO Connection object represents an open connection to an OLE DB data source. The Provider property sets the OLE DB provider to use. The connection can be configured before opening the data source by setting the ConnectionString properties. The version of the ADO implementation in use can be determined from the Version property.

The physical connection to the data source is established using the Open method and terminated with the Close method. If errors occur, these can be examined with the Errors collection.

The following Connection object methods, properties, and collections are supported by the current version of the Microsoft® OLE DB Provider for DB2:

NameComment
Close methodCloses a connection to a data source.
Execute methodEvaluates command text.
Open methodOpens a connection to a data source and may optionally pass ConnectionString parameters with this method.
OpenSchema methodObtains database schema information from the OLE DB provider.
Attributes propertyOne or more characteristics supported for a given Connection object.
ConnectionString propertyContains the information used to establish a connection to a data source (see notes following).
CursorLocation propertySets or returns the location of the cursor (whether the cursor is on the client or the server side).
IsolationLevelSets or returns the level of isolation for a Connection object.
Note that versions of the OLE DB Provider for DB2 supplied with SNA Server 4.0 did not support this property.
Mode propertyIndicates the available permissions for modifying data in a connection.
Provider propertySets or returns the name of the provider for a connection.
State propertyDescribes the current state of an object.
Version propertyReturns the version number of the ADO implementation in use.
Errors collectionCollections of Error objects on the connection.
Properties collectionCollections of properties on the connection.

The information needed to establish a connection to a data source can be set in the ConnectionString property or passed as part of the Open method. In either case, this information must be in a specific format for use with the OLE DB Provider for DB2. This information can be a data source name (DSN) or a detailed connection string containing a series of argument=value statements separated by semicolons. ADO supports several standard ADO-defined arguments for the ConnectionString property as follows:

ArgumentDescription
Data SourceName of the data source for the connection. This argument is optional when using the OLE DB Provider for DB2.
File NameName of the provider-specific file containing preset connection information. This argument cannot be used if a Provider argument is passed.
LocationThe Remote Database Name used for connecting to OS/400 systems. This parameter is optional when connecting to mainframe systems.
PasswordValid mainframe or AS/400 password to use when opening the connection. This password is used to validate that the user can log on to the target DB2 host system and has appropriate access rights to the database.

This parameter is equivalent to the DBPROP_AUTH_PASSWORD OLE DB property ID.

ProviderName of the provider to use for the connection. To use the OLE DB Provider for DB2, the Provider string must be set to “DB2OLEDB.”
User IDValid mainframe or AS/400 user name to use when opening the connection. This user name validates that the user can log on to the target DB2 host system and has appropriate access rights to the database.

This parameter is equivalent to the DBPROP_AUTH_USERID OLE DB property ID.

The OLE DB Provider for DB2 also supports a number of provider-specific arguments, some of which have default values as specified in the tables below. The arguments supported by OLE DB Provider for DB2 supplied with Host Integration Server 2000 differ from the arguments supported by the earlier OLE DB Provider for DB2 included with SNA Server 4.0.

The arguments supported by the OLE DB Provider for DB2 supplied with Host Integration Server 2000 are as follows:

ArgumentDescription
BinAsCharWhen this parameter is set to true, the OLE DB Provider for DB2 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.

This parameter defaults to false.

CCSIDThe Code Character Set Identifier (CCSID) attribute indicates the character set used on the host.

If this argument is omitted, the default value is U.S./Canada (37).

DefSchThe name of the default schema (collection/owner) where the system catalogs resides. This parameter can be QSYS2;SYSIBM;SYSTEM; CURLIB; or USERID depending on platform.

This parameter does not have a default value.

InitCatThis parameter is used as the first part of a 3-part fully qualified table name. In DB2 (MVS, OS/390), this property is referred to as LOCATION. The SYSIBM.LOCATIONS table lists all the accessible locations. In DB2/400, this parameter 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 one can be created using the Add option. In DB2 Universal Database, this property is referred to as DATABASE.

This parameter has no default value.

LocalLUThe name of the local LU alias configured in Host Integration Server.
ModeNameThe APPC mode (must be set to a value that matches the host configuration and Host Integration Server configuration).

Legal values for the APPC mode include QPCSUPP (5250), #NTER (interactive), #NTERSC (interactive), #BATCH (batch), #BATCHSC (batch), and custom modes.

NetAddrWhen TCP/IP has been selected for the Network Transport Library, this parameter indicates the IP address of the host.
NetPortWhen TCP/IP has been selected for the Network Transport Library, this parameter is the TCP/IP port used for communication with the source.

The default value is TCP/IP port 446.

NetLibThis parameter determines whether TCP/IP or SNA APPC is used for network communication. The possible values for this parameter are TCPIP or SNA.

This value defaults to SNA.

PCCodePageThe character code page to use on the PC. If this argument is omitted, the default value is set to Latin 1 (1252).
PkgColThe name of the DRDA target collection (AS/400 library) where the Microsoft OLE DB Provider for DB2 should store and bind DB2 packages. This could be same as the Default Schema.

The Microsoft OLE DB Provider for DB2 uses packages to issue dynamic and static SQL statements. The OLE DB Provider will create packages dynamically in the location to which the user points using the Package Collection parameter.

RemoteLUThe name of the remote LU alias configured in Host Integration Server.
TPNameThe transaction program name when used with SQL/DS.
UOWThis parameter determines whether two-phase commit is enabled. The possible values for this parameter are DUW (distributed unit of work) or RUW (remote unit of work).

This value defaults to RUW.

When this parameter is set to RUW, two-phase commit is disabled.

When this parameter is set to DUW, two-phase commit is enabled in the OLE DB Provider for DB2. Distributed transactions are handled using Microsoft Transaction Server, Microsoft Distributed Transaction Coordinator, and the SNA LU 6.2 Resync Service. This option works only with DB2 for OS/390 v5R1 or later. This option also requires that SNA (LU 6.2) service is selected as the network transport and Microsoft Transaction Server (MTS) is installed.

Ee265664.note(en-US,BTS.10).gif Note   Not all of these parameters are required. The user can also be prompted for this information.

The arguments supported by the OLE DB Provider for DB2 supplied with SNA Server 4.0 are as follows:

ArgumentDescription
BinAsCharWhen this parameter is set to true, the OLE DB Provider for DB2 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.

This parameter defaults to false.

BindTypeThis parameter indicates the bind type to be used when creating packages. Legal values for the package binding type are as follows.

NORM—normal binding.

FAST—create all 64 package sections optimally in a single network flow.

NOSP—reserved for future use and currently not supported.

The default value for this parameter is NORM.

This parameter is not supported by the ODBC Driver for DB2 supplied with Host Integration Server 2000.

This parameter is supported by the OLE DB Provider for DB2 supplied with the Japanese version of the OLE DB Provider for DB2 client included with SNA Server 4.0 with Service Pack 2, and by the OLE DB Provider for DB2 client included with all versions of SNA Server 4.0 with Service Pack 3 or later.

CCSIDThe Code Character Set Identifier (CCSID) attribute indicates the character set used on the host.

If this argument is omitted, the default value is U.S./Canada (37).

CommitThis parameter indicates whether changes to data will be automatically committed or require a separate manual commit request.

This parameter defaults to true (auto commit).

DefSchThe name of the default schema (collection/owner) where the system catalogs resides. This parameter can be QSYS2;SYSIBM;SYSTEM; CURLIB; or USERID depending on platform.

This parameter does not have a default value.

GCCSIDThe graphics character code set identifier (GCCSID) matching the DB2 character data as represented on the remote host computer. This parameter is required when accessing DB2 databases configured to support mixed single-byte (SBCS) and double-byte (DBCS) data. This parameter only applies when accessing DB2 for OS/390 or DB2 for MVS.

The following values for GCCSID are supported by the OLE DB Provider for DB2: 300, 834, 835, 837, or 4396.

This parameter defaults to 0 indicating that mixed CCSID conversions are not supported.

This parameter is not supported by the ODBC Driver for DB2 supplied with Host Integration Server 2000.

This parameter is supported by the OLE DB Provider for DB2 supplied with the Japanese version of the OLE DB Provider for DB2 client included with SNA Server 4.0 with Service Pack 2, and by the OLE DB Provider for DB2 client included with all versions of SNA Server 4.0 with Service Pack 3 or later.

InitCatThis parameter is used as the first part of a 3-part fully qualified table name. In DB2 (MVS, OS/390), this property is referred to as LOCATION. The SYSIBM.LOCATIONS table lists all the accessible locations. In DB2/400, this parameter 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 one can be created using the Add option. In DB2 Universal Database, this property is referred to as DATABASE.

This parameter has no default value.

IsoLvlThis parameter determines the isolation level provided for this data source. Legal values for the default isolation level are the following:

CS—Cursor Stability. In DB2/400, this isolation level corresponds to COMMIT(*CS). In ANSI, this isolation level corresponds to Read Committed (RC).

NC—No Commit. In DB2/400, this isolation level corresponds to COMMIT(*NONE). In ANSI, this isolation level corresponds to No Commit (NC).

UR—Uncommited Read. In DB2/400, this isolation level corresponds to COMMIT(*CHG). In ANSI, this isolation level corresponds to Read Uncommited.

RS—Read Stability. In DB2/400, this isolation level corresponds to COMMIT(*ALL). In ANSI, isolation level this corresponds to Repeatable Read.

RR—Repeatable Read. In DB2/400, this isolation level corresponds to COMMIT(*RR). In ANSI, this isolation level corresponds to Serializable (Isolated).

This parameter defaults to NC.

This parameter is not supported by the ODBC Driver for DB2 supplied with Host Integration Server 2000.

LocalLUThe name of the local LU alias configured in Host Integration Server.
MCCSIDThe mixed character code set identifier (MCCSID) matching DB2 character data as represented on the remote host computer. This parameter is required when accessing DB2 databases configured to support mixed single-byte (SBCS) and double-byte (DBCS) data. This parameter only applies when accessing DB2 for OS/390 or DB2 for MVS.

The following values for MCCSID are supported by the OLE DB Provider for DB2: 930, 931, 933, 935, 937, 939, 5026, or 5035.

This parameter defaults to 0 indicating that mixed CCSID conversions are not supported.

This parameter is not supported by the ODBC Driver for DB2 supplied with Host Integration Server 2000.

This parameter is supported by the OLE DB Provider for DB2 supplied with the Japanese version of the OLE DB Provider for DB2 client included with SNA Server 4.0 with Service Pack 2, and by the OLE DB Provider for DB2 client included with all versions of SNA Server 4.0 with Service Pack 3 or later.

ModeNameThe APPC mode (must be set to a value that matches the host configuration and Host Integration Server configuration).

Legal values for the APPC mode include QPCSUPP (5250), #NTER (interactive), #NTERSC (interactive), #BATCH (batch), #BATCHSC (batch), and custom modes.

NetAddrWhen TCP/IP has been selected for the Network Transport Library, this parameter indicates the IP address of the host.
NetPortWhen TCP/IP has been selected for the Network Transport Library, this parameter is the TCP/IP port used for communication with the source.

The default value is TCP/IP port 446.

NetLibThis parameter determines whether TCP/IP or SNA APPC is used for network communication. The possible values for this parameter are TCPIP or SNA.

This value defaults to SNA.

PCCodePageThe character code page to use on the PC. If this argument is omitted, the default value is set to Latin 1 (1252).
PkgColThe name of the DRDA target collection (AS/400 library) where the Microsoft OLE DB Provider for DB2 should store and bind DB2 packages. This could be same as the Default Schema.

The Microsoft OLE DB Provider for DB2 uses packages to issue dynamic and static SQL statements. The OLE DB Provider will create packages dynamically in the location to which the user points using the Package Collection parameter.

ReadOnlyWhen the Read Only parameter is set to true (ReadOnly=1), the OLE DB Provider for DB2 creates a read-only data source. A user has read access to objects such as tables, and cannot do update operations (INSERT, UPDATE, or DELETE, for example).

This parameter is not supported by the ODBC Driver for DB2 supplied with Host Integration Server 2000.

RemoteLUThe name of the remote LU alias configured in Host Integration.
TPNameThe transaction program name when used with SQL/DS.
Ee265664.note(en-US,BTS.10).gif Note   Not all of these parameters are required. The user can also be prompted for this information.

A sample ConnectionString using the OLE DB Provider for DB2 follows:

Conn.Provider="DB2OLEDB"
Conn.ConnectionString = "User ID=USERNAME;Password=password",&_
  "LocalLU=LOCAL;RemoteLU=DATABASE",&_
  "ModeName=QPCSUPP;CCSID=37;PcCodePage=437"
Conn.Properties("PROMPT")=adPromptNever
Conn.Open
 
Ee265664.note(en-US,BTS.10).gif Note   The &_ character combination is used for continuing long lines in Visual Basic.

When opening a connection object in ADO 2.0, you must specify the Prompt connection property. For example, the following is valid with ADO 1.5 and ADO 2.0 and will prompt the user for ConnectionString properties.

Conn.ConnectionString = "Provider=DB2OLEDB
Conn.Properties("PROMPT")=adPromptAlways
Conn.Open

A sample Open method call with these parameters follows:

RS.Open "Accounting",Conn,0,1,1
 

The last three parameters to the Open method correspond with the CursorType (the adOpenForwardOnly enum is 0, for example), LockType (the adLockReadOnly enum is 1, for example), and Options (adCmdText is 1, which indicates that the Source name should be evaluated as SQL text). The Options parameter must be set to adCmdText (1) when used with a data source name with OLE DB Provider for DB2.

The allowable values for CCSID when using SNANLS (SNA National Language Support) for character code conversions (the default) are as follows:

EBCDIC character setCCSID value
Arabic20420
Binary (No Conversion)65535
Chinese (Simplified)935
Chinese (Traditional)937
Cyrillic (Russian)20880
Cyrillic (Serbian, Bulgarian)21025
Denmark/Norway (Euro)1142
Denmark/Norway20277
Finland/Sweden (Euro)1143
Finland/Sweden20278
France (Euro)1147
France20297
Germany (Euro)1141
Germany20273
Greek (Modern)875
Greek20423
Hebrew20424
Icelandic (Euro)1149
Icelandic20871
International (Euro)1148
International500
Italy (Euro)1144
Italy20280
Japanese (English-lower)931
Japanese (Extend English)939
Japanese (Extend Katakana)930
Japanese (Katakana)290
Japanese (Katakana-Kanji)5026
Japanese (Latin-Kanji)5035
Korean933
Latin America/Spain (Euro)1145
Latin America/Spain20284
Latin-1 Open System (Euro)20924
Latin-1 Open System1047
Multilingual/ROECE (Latin-2)870
Thai20838
Turkish (Latin-5)1026
Turkish20905
U.S./Canada (Euro)1140
U.S./Canada37
United Kingdom (Euro)1146
United Kingdom20285

Note that the SNANLS conversion uses the locale configured for the data sources using data links. For more information, see the SDK documentation on SNA National Language Support.

The allowable values for CCSID when using ANSI/OEM for character code conversions are:

ANSI/OEM character setCCSID value
ANSI - Arabic1256
ANSI - Baltic1257
ANSI - Cyrillic1251
ANSI - Eastern Europe1250
ANSI - Greek1253
ANSI - Hebrew1255
ANSI - Latin I1252
ANSI - Turkish1254
ANSI/OEM - Korean (Extended Wansung)949
ANSI/OEM - Japanese Shift-JIS932
ANSI/OEM - Simplified Chinese GBK936
ANSI/OEM - Traditional Chinese Big5950
ANSI/OEM - Thai874
ANSI/OEM - Vietnam1258
Show:
© 2015 Microsoft