Creating Packages for Use with the OLE DB Provider for DB2

The Microsoft OLE DB Provider for DB2, which is implemented as an IBM Distributed Relational Database Architecture (DRDA) application requester, uses packages to issue SQL statements and call DB2 stored procedures. There is a provider-specific property that the OLE DB Provider for DB2 uses to identify a location in which to create and store DB2 packages. The OLE DB Provider for DB2 creates packages dynamically in the location to which the user points using the Package Collection property corresponding to the DBPROP_DB2OLEDB_PACKAGECOL property ID of OLE DB. This location may be configured using the Connection and Advanced tabs using Microsoft Data Links or can be passed as part of the connection string as an attribute keyword and argument. This attribute keyword can be either pkgcol or the long form of this attribute, Package Collection.

There are two package creation options:

1. The OLE DB Provider for DB2 autocreates one package for the currently used isolation level at run time if no package already exists. This autocreate process may fail if the user account does not have authority to create packages.
   
2. An administrator or user can manually creates all four packages (five packages on DB2/400) for use with all isolation levels and for use by all users (the PUBLIC group on DB2 representing all users) or a specific set of users. The OLE DB Provider for DB2 includes a program for use by users with appropriate administrative privilege that will create these packages and grant access to the PUBLIC group for this purpose.
   

However, some users may not have the security level when manually creating packages to GRANT authority to the packages to other users (grant authority to the DB2 PUBLIC group representing all users, for example). This can be a problem if two or more users with different user IDs try to access a single collection of packages. The first user that created the packages will have access to the packages, but the second user likely will not. The Host Integration Server 2006 CD includes a program for use by an administrator to create packages. This tool can be run using a privileged User ID to create packages in collections accessed by multiple users. You use the Data Access Tool to create packages for use with DB2.

A shortcut for this tool is added to the Programs menu off the Start button on the Windows taskbar under the Host Integration Server\Data Integration folder with a name of Data Access Tool. This shortcut is created when the Microsoft Host Integration Server or the Host Integration Client is first installed and support for Data Access is checked.

This tool creates a set of packages and grants EXECUTE privileges on these packages to the PUBLIC group. The PUBLIC group on DB2 systems is a default group that represents all DB2 users. The following packages are created:

• AUTOCOMMITTED package (MSNC001) is only applicable on DB2/400)
  
• READ UNCOMMITTED package (MSUR001)
  
• READ COMMITTED package (MSCS001)
  
• REPEATABLE READ package (MSRS001)
  
• SERIALIZABLE package (MSRR001)
  

Note that the AUTOCOMMITTED package (MSNNC001) is only created on DB2 for OS/400.

The descriptive process name used by the Data Access Tool corresponds with the isolation levels defined in the ANSI SQL standard. The following table indicates how these packages correspond with the terms used by IBM for isolation levels in DB2 documentation.

Note that when upgrading from SNA Server 4.0, any existing SNA 4.0 packages must be re-created using the Host Integration Server Data Access Tool to make them compatible with Host Integration Server 2006. The package names used by the OLE DB Provider for DB2 on SNA Server 4.0 are not compatible with the OLE DB Provider for DB2 included with Host Integration Server. On SNA Server 4.0, these packages used different names as follows:

AUTOCOMMITTED package (SNANC001) only applicable on DB2/400
READ UNCOMMITTED package (SNACH001)
READ COMMITTED package, (SNACS001)
REPEATABLE READ package, (SNARR001)
SERIALIZABLE package (SNAAL001)

These isolation levels are described in detail under Support for Isolation Levels Using the OLE DB Provider for DB2. These isolation levels are also described under the OLE DB isoLevel parameter and ADO IsolationLevel property. Note that the AUTOCOMMITTED package (MSNC001) is only created on DB2 for OS/400.

Note that the Data Access Tool creates this set of packages and grants EXECUTE privileges to the PUBLIC group. There may be cases for security reasons where EXECUTE privileges to this set of packages on the DB2 system should be restricted to a different group of users or specific users. In these cases, execution privileges on these created packages need to be modified on the host system.

The Data Access Tool creates all of these packages inside the Collection that is specified in the Package Collection property in the data link file, or in the connection string. If the user does not have the appropriate authority to create packages in the specified Collection, or if the specified Collection does not exist, the OLE DB Provider for DB2 will return an error.

In the case of DB2 on MVS or OS/390, the normal error text returned if the user does not have the appropriate authority would be as follows:

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 51002, SQLCODE: -567. 

In the case of DB2/400, the normal error text returned if the user does not have the appropriate authority would be as follows:

A SQL error has occurred. Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 51002, SQLCODE: -805. 

In the case of DB2/400, the normal error returned if the collection does not exist would be as follows:

Failed to create AUTOCOMMITTED (NC) package.  RETCODE=-99.
SQL Error: Code=-204, State=42704, Error Text= A SQL error has occurred.  Please consult the documentation for your specific DB2 version for a description of the associated Native Error and SQL State. SQLSTATE: 42704, SQLCODE: -204

There are two authorities required to execute the create package process on MVS using the Data Access Tool:

GRANT BINDADD TO <authorization ID>
GRANT CREATE IN COLLECTION <collection ID> TO <authorization ID>

The "authorization ID" is the user who needs the permission to create the packages. The "collection ID" is the name of the collection, which the user specifies in the data link file for the Package Collection property. This collection should be a valid collection within the DB2. If an administrator executes the preceding statements on behalf of a nonprivileged user, this nonprivileged user can then run the CrtPkg tool. Once run, the CrtPkg process creates four sets of packages (one for each of the four isolation levels supported on DB2 for MVS or OS/390) for use by all (PUBLIC) users of the Microsoft data access features.

The following example illustrates this process on DB2 for MVS or DB2 for OS/390.

Grant rights to run the CrtPkg tool to authorization ID WNW999

GRANT BINDADD TO WNW999
GRANT CREATE IN COLLECTION MSPKG TO WNW999

Run the Data Access Tool using authorization ID WNW999.

To execute the Data Access Tool on DB2/400, a user ID must have one of the following authorities:

• *CHANGE authority on the DB2 collection
  
• *ALL authority on the DB2 collection
  

If the user has *USE authority or if the user has *EXCLUDE authority, the Create Package process will fail.

There are several steps required to change user authority on a DB2/400 collection (AS/400 library): From interactive SQL (STRSQL command) while logged on as user with administrative privileges, create a new collection. This command can also be issued using ADO, OLE DB, and ODBC. However, most administrators typically create collections from the AS/400 console because the administrator must be logged on at the console to issue the Command Language (CL) command with which to change the user authority on the collection.

CREATE COLLECTION <collection ID>

From the AS/400 command console, type the CL WRKOBJ command with the <collection ID> as a parameter.

WRKOBJ <collection ID>

The "collection ID" is the name of the collection, which the user specifies in the data link file for the Package Collection property. This collection should be a valid collection within DB2. The Work with objects dialog box appears. Place the cursor on the *PUBLIC Object Authority line and change the authority from *USE to *ALL.

If an administrator executes the preceding statements on behalf of a nonprivileged user, this nonprivileged user can then run the CrtPkg tool. Once run, the CrtPkg process creates five sets of packages (one for each of the five isolation levels supported on DB2/400) for use by all (PUBLIC) users of the Microsoft data access features. On DB2/400, five packages are created including the AUTOCOMMITTED packages.

The following example illustrates this process on DB2/400.

Grant rights to run the Data Access Tool to authorization ID WNW999

CREATE COLLECTION MSPKG
WRKOBJ MSPKG

Run the Data Access Tool.

When using the Data Access Tool, if the package collection specified does not exist, DB2 returns SQLCODE -805.

When using auto-create packages, if a package collection is not specified or the package collection does not exist, during the auto-create package process, the consumer application receives SQLSTATE HY000 and SQLCODE -385. The SQLSTATE HY000 is defined as a provider-specific error. The -385 Error Return Code is not a SQLCODE but rather a DDM DRDA AR (DB2 client) return code. This error code is defined as DDM_VALNSPRM with the following associated text string:

"The parameter value is not supported by the target system." 

The OLE DB Provider for DB2 client error codes are defined in the Db2oledb.h file located on the Host Integration Server CD.

Note that when upgrading from SNA Server 4.0, any existing SNA 4.0 packages must be re-created using the Host Integration Server Data Access Tool to make them compatible with Host Integration Server.