ODBC Driver for DB2 may cause "Index Already Exists" error

When you use the Microsoft ODBC Driver for DB2 to link a table from Microsoft Access, you may receive an "Index Already Exists" error.

Microsoft Access calls the SQLStatistics ODBC API function to retrieve all the indexes that exist in a table. When the SQLStatistics API is called, the ODBC Driver issues an SQL statement to the backend DB2 database that queries the SYSINDEX table in SYSIBM. This query must return all the information from the indexes according to some specific order, such as NON_UNIQUE, TYPE, INDEX_QUALIFIER, and INDEX_NAME. The ODBC Driver for DB2 does not sort the returned result set. This results in the "Index Already Exists" error.

Service pack information

To resolve this problem, obtain the latest service pack for Microsoft Host Integration Server 2000. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

Hotfix information

The English version of this hotfix has the file attributes (or later file attributes) that are listed in the following table. The dates and times for these files are listed in Coordinated Universal Time (UTC). When you view the file information, it is converted to local time. To find the difference between UTC and local time, use the Time Zone tab in the Date and Time tool in Control Panel.
Note Because of file dependencies, the most recent fix that contains the preceding files may also contain additional files.

The problem can be avoided by using an SQL pass-through query instead of linking the tables in Microsoft Access.

For more information about how to create an SQL pass-through query, click the following article number to view the article in the Microsoft Knowledge Base:

Microsoft has confirmed that this is a problem in Microsoft Host Integration Server 2000 and Host Integration Server 2000 SP1.

This problem was corrected in Microsoft Host Integration Server 2000 Service Pack 2.

The updated ODBC Driver for DB2 uses an "ORDER BY" clause to sort the indexes that are returned by the SYSINDEX table query.