OLE DB Provider-Specific Property Support in the OLE DB Provider for DB2

The following table summarizes the provider-specific OLE DB version 2.0 properties in the DB2OLEDB_DBPROPSET_DBINIT property set that are supported by the version of the Microsoft® OLE DB Provider for DB2 included with Host Integration Server 2000:

OLE DB Property IDDescription
DBPROP_DB2OLEDB_APPCMODEWhen LU 6.2 (SNA) is selected for the Network Transport Library (DBPROP_DB2OLEDB_NETTYPE), this property 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 bidirectional 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).

This VT_BSTR type property normally defaults to QPCSUPP.

DBPROP_DB2OLEDB_BINASCHARThis property indicates whether to process binary fields (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 parameters when this parameter is true.

This VT_BOOL type property defaults to VARIANT_FALSE, don't process binary fields as character fields.

DBPROP_DB2OLEDB_CATALOGCOLThe name of the collection where the OLE DB Provider for DB2 looks for catalog information. This is the default schema, the “SCHEMA” name for the target collection of tables and views. This property is the Data Schema value when configuring data sources. The OLE DB Provider for DB2 uses this default schema to restrict results sets for popular operations, such as enumerating a list of tables in a target collection.

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 VT_BSTR value for DBPROP_DB2OLEDB_CATALOGCOL, then the OLE DB Provider uses the USER_ID provided at login. For DB2/400, the driver will use QSYS2 if there is no collection found matching the USER_ID value. Obviously, these values for the default schema are inappropriate in many cases, therefore it is essential that the Default Schema value in the data source be defined.

DBPROP_DB2OLEDB_HOSTCCSIDThe character code set identifier (CCSID) matching the data as represented on the host. This property is required when processing binary data as character data. Unless the DBPROP_DB2OLEDB_BINASCHAR property ID is set to true, character data is converted based on the host column CCSID and default ANSI code page.

This VT_I4 type property defaults to 37 (U.S./Canada).

DBPROP_DB2OLEDB_LOCALLUWhen LU 6.2 (SNA) is selected for the Network Transport Library, this property is the name of the local LU alias configured in the SNA server.

This VT_BSTR type property has no default value.

DBPROP_DB2OLEDB_NETADDRESSWhen TCP/IP has been selected for the Network Transport Library, this property is used to locate the target host computer. This parameter indicates the IP address or TCP/IP host name alias associated with the DDM server on the host. The network address is required when connecting via TCP/IP.

This VT_BSTR type property defaults to SNA.

DBPROP_DB2OLEDB_NETPORTWhen TCP/IP has been selected for the Network Transport Library, this property is used to locate the target DDM service access port when connecting via TCP/IP. This parameter represents the TCP/IP port used for communication with the DDM service on the host.

This VT_BSTR type property defaults to 446.

DBPROP_DB2OLEDB_NETTYPEThis property which represents the dynamic link library used for transport designates whether the provider connects via SNA LU 6.2 or TCP/IP for network communication. The possible values for this parameter are TCP/IP or SNA.

If TCPIP is selected, then values for Network Address (DBPROP_DB2OLEDB_NETADDRESS) and Network Port (DBPROP_DB2OLEDB_NETPORT) are required.

If SNA is selected, then values for APPC Local LU Alias (DBPROP_DB2OLEDB_LOCALLU, APPC Mode Name (DBPROP_DB2OLEDB_APPCMODE), and APPC Remote LU Alias (DBPROP_DB2OLEDB_REMOTELU) are required.

This VT_BSTR type property defaults to SNA.

DBPROP_DB2OLEDB_PACKAGECOLThe 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 the same as the Default Schema (DBPROP_DB2OLEDB_DEFAULTSCH).

The Microsoft OLE DB Provider for DB2, which is implemented as an IBM DRDA Application Requester, uses packages to issue dynamic and static SQL statements. The OLE DB Provider for DB2 will create packages dynamically in the location to which the user points using the this property ID.

This VT_BSTR type property has no default value.

DBPROP_DB2OLEDB_PCCODEPAGEThe PC Code Page property ID indicates the code page to be used on the PC for character code conversion. This property is required when processing binary data as character data. Unless DBPROP_DB2OLEDB_BINASCHAR is set to true, character data is converted based on the default ANSI code page configured in Windows.

If this parameter is set to Binary or 65535, then no character code conversions will take place.

This VT_I4 type property defaults to 1252 (Latin 1).

DBPROP_DB2OLEDB_REMOTELUWhen LU 6.2 (SNA) is selected for the Network Transport Library (DBPROP_DB2OLEDB_NETTYPE), this property is the name of the remote LU alias configured in the SNA server.

This VT_BSTR type property has no default value.

DBPROP_DB2OLEDB_TPNAMEThis property represents the default transaction program name for the DB2 DRDA application server (AS) which is 07F6DB (DB2DRDA). However, some DB2 installations may be configured to use an alternate TP name.

Host Integration Server 2000 uses the alternate TP name in the off-line demo configuration (DRDADEMO.UDL). In that case, this property is set to 0X07F9F9F9.

This VT_BSTR type property has no default value.

DBPROP_DB2OLEDB_UNITSOFWORKThis property indicates whether two-phase commit (distributed unit of work) used for transactions is supported for this data source. Distributed transactions are handled using Microsoft Transaction Server, Microsoft Distributed Transaction Coordinator, and the SNA LU 6.2 Resync Service.

The following values for this property are supported by the OLE DB Provider for DB2:

RUW (remote unit of work)

DUW (distributed unit of work)

This VT_BSTR type property has a default value of RUW.

Distributed unit of work (two-phase commit) works only with DB2 for OS/390 v5R1 or later. This option also requires that the SNA LU 6.2 service is selected as the network transport and Microsoft Transaction Server (MTS) is installed.

The following table summarizes the provider-specific OLE DB version 2.0 properties in the DB2OLEDB_DBPROPSET_DBINIT property set that are supported by the version of the Microsoft® OLE DB Provider for DB2 included with SNA Server 4.0:

OLE DB Property IDDescription
DBPROP_DB2OLEDB_APPCMODEWhen LU 6.2 (SNA) is selected for the Network Transport Library (DBPROP_DB2OLEDB_NETTYPE), this property 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 bidirectional 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).

This VT_BSTR type property normally defaults to QPCSUPP.

DBPROP_DB2OLEDB_BINASCHARThis property indicates whether to process binary fields (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 parameters when this parameter is true.

This VT_BOOL type property defaults to VARIANT_FALSE, don't process binary fields as character fields.

DBPROP_DB2OLEDB_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 NORM package binding option is designed to provide reasonable performance and maximum compatibility with different versions of DB2, DB2 for MVS, DB2 for OS/390, DB2 UDB, and DB2 for OS/400. Optionally, administrators can use the FAST method when running the Create Package utility and creating packages in many target collections. The FAST option should not be used with DB2 for MVS and DB2 UDB for NT due to known incompatibilities.

This VT_BSTR property defaults to NORM.

This VT_BOOL type property defaults to VARIANT_FALSE, don't process binary fields as character fields.

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

This parameter allows for implicit COMMIT on all SQL statements. In auto-commit mode, every database operation is a transaction that is committed when performed. This mode is suitable for common transactions that consist of a single SQL statement. It is unnecessary to delimit or specify completion of these transactions. No ROLLBACK is allowed when using Auto Commit mode.

This VT_BOOL type property defaults to VARIANT_TRUE (auto commit).

DBPROP_DB2OLEDB_DEFAULTSCHThe name of the Collection where the OLE DB Provider for DB2 looks for catalog information. The Default Schema is the “SCHEMA” name for the target collection of tables and views. The OLE DB Provider uses Default Schema to restrict results sets for popular operations, such as enumerating a list of tables in a target collection.

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 VT_BSTR value for Default Schema, then the OLE DB Provider uses the USER_ID provided at login. For DB2/400, the driver will use QSYS2 if there is no collection found matching the USER_ID value. Obviously, this default is inappropriate in many cases, therefore it is essential that the Default Schema value in the data source be defined.

DBPROP_DB2OLEDB_HOSTCCSIDThe character code set identifier (CCSID) matching the data as represented on the host. This property is required when processing binary data as character data. Unless the DBPROP_DB2OLEDB_BINASCHAR property ID is set to true, character data is converted based on the host column CCSID and default ANSI code page.

This VT_I4 type property defaults to 37 (U.S./Canada).

DBPROP_DB2OLEDB_HOSTGCCSIDThe graphics character code set identifier (GCCSID) matching the DB2 character data as represented on the remote host computer. This property is required when accessing DB2 databases configured to support mixed single-byte (SBCS) and double-byte (DBCS) data. When accessing DB2 for OS/390 or DB2 for MVS, a value must be specified for GCCSID if the "MIXED DATA" field (7) of the DB2 installation panel for Application Programming Defaults (DSNTIPF) is set to "YES".

This property is currently supported only by the Japanese version of the OLE DB Provider for DB2 client included with SNA Server 4.0 with Service Pack 2 or later and by the OLE DB Provider for DB2 client included with SNA Server 4.0 with Service Pack 3 or later. This property 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 VT_I4 type property defaults to indicating that mixed CCSID conversions are not supported.

DBPROP_DB2OLEDB_HOSTMCCSIDThe mixed character code set identifier (MCCSID) matching DB2 character data as represented on the remote host computer. This property is required when accessing DB2 databases configured to support mixed single-byte (SBCS) and double-byte (DBCS) data. When accessing DB2 for OS/390 or DB2 for MVS, a value must be specified for MCCSID if the "MIXED DATA" field (7) of the DB2 installation panel for Application Programming Defaults (DSNTIPF) is set to "YES".

This property is currently supported only by the Japanese version of the OLE DB Provider for DB2 client included with SNA Server 4.0 with Service Pack 2 or later and by the OLE DB Provider for DB2 client included with SNA Server 4.0 with Service Pack 3 or later. This property 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 VT_I4 type property defaults to 0 indicating that mixed CCSID conversions are not supported.

DBPROP_DB2OLEDB_ISOLATIONThis property 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—Uncommitted Read. In DB2/400, this isolation level corresponds to COMMIT(*CHG). In ANSI, this isolation level corresponds to Read Uncommitted.

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 VT_BSTR type property defaults to NC.

DBPROP_DB2OLEDB_LOCALLUWhen LU 6.2 (SNA) is selected for the Network Transport Library, this property is the name of the local LU alias configured in the SNA server.

This VT_BSTR type property has no default value.

DBPROP_DB2OLEDB_NETADDRESSWhen TCP/IP has been selected for the Network Transport Library, this property is used to locate the target host computer. This parameter indicates the IP address or TCP/IP host name alias associated with the DDM server on the host. The network address is required when connecting via TCP/IP.
DBPROP_DB2OLEDB_NETPORTWhen TCP/IP has been selected for the Network Transport Library, this property is used to locate the target DDM service access port when connecting via TCP/IP. This parameter represents the TCP/IP port used for communication with the DDM service on the host.

This VT_BSTR type property defaults to 446.

DBPROP_DB2OLEDB_NETTYPEThis property which represents the dynamic link library used for transport designates whether the provider connects via SNA LU 6.2 or TCP/IP for network communication. The possible values for this parameter are TCP/IP or SNA.

If TCPIP is selected, then values for Network Address (DBPROP_DB2OLEDB_NETADDRESS) and Network Port (DBPROP_DB2OLEDB_NETPORT) are required.

If SNA is selected, then values for APPC Local LU Alias (DBPROP_DB2OLEDB_LOCALLU, APPC Mode Name (DBPROP_DB2OLEDB_APPCMODE), and APPC Remote LU Alias (DBPROP_DB2OLEDB_REMOTELU) are required.

This VT_BSTR type property defaults to SNA.

DBPROP_DB2OLEDB_PACKAGECOLThe 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 the same as the Default Schema (DBPROP_DB2OLEDB_DEFAULTSCH).

The Microsoft OLE DB Provider for DB2, which is implemented as an IBM DRDA Application Requester, uses packages to issue dynamic and static SQL statements. The OLE DB Provider for DB2 will create packages dynamically in the location to which the user points using the this property ID.

This is a VT_BSTR type property.

DBPROP_DB2OLEDB_PCCODEPAGEThe PC Code Page property ID indicates the code page to be used on the PC for character code conversion. This property is required when processing binary data as character data. Unless DBPROP_DB2OLEDB_BINASCHAR is set to true, character data is converted based on the default ANSI code page configured in Windows.

If this parameter is set to Binary or 65535, then no character code conversions will take place.

This VT_I4 type property defaults to 1252 (Latin 1).

DBPROP_DB2OLEDB_READONLYA property indicates whether the OLE DB Provider creates a read-only data source. When this property is true, the user has only read access to objects such as tables and cannot perform certain operations (INSERT, UPDATE, and DELETE, for example).

This VT_BOOL type property defaults to VARIANT_TRUE.

DBPROP_DB2OLEDB_REMOTELUWhen LU 6.2 (SNA) is selected for the Network Transport Library (DBPROP_DB2OLEDB_NETTYPE), this property is the name of the remote LU alias configured in the SNA server.

This VT_BSTR type property has no default value.

DBPROP_DB2OLEDB_TPNAMEThis property represents the default transaction program name for the DB2 DRDA application server (AS) which is 07F6DB (DB2DRDA). However, some DB2 installations may be configured to use an alternate TP name.

This VT_BSTR type property has no default value.

Show: