StarQuest Technical Documents

How to Join a DB2 Table and a Local Microsoft Access Table

Last Update: 26 June 2008
Product: StarSQL
Versions: 5.x
Article ID: SQV00SQ039

Abstract

This article describes how to join a DB2 table and a local Microsoft Access table for optimal performance.

Often with large amounts of data, performance is impaired (and sometimes the join is impossible) if Access attempts to resolve such a join locally. This is because the DB2 table has not been indexed in a way that is acceptable to Access.

Solution

There must be a unique index on the DB2 table on the field on which the join is performed. This index may include only the field referenced in the join or the field referenced in the join and other fields in the table (called a composite index). If the field referenced in the join contains data that is not unique for each record, you must create a composite index in order for the index to be unique. Note that in DB2, primary keys require unique indexes.

IMPORTANT: If multiple fields are included in the index, the field named in the join must be the first field named in the unique index.

Creating a unique index on a DB2 table:

To create a unique index on the CUSTOMER_NAME field of the DB2 table ELEANOR.CUSTOMERS, use the following SQL statement:

CREATE UNIQUE INDEX MYINDEX ON ELEANOR.CUSTOMERS (CUSTOMER_NAME)

If data in the CUSTOMER_NAME field is not unique for each record, you will not be able to create a unique index on that field alone. Instead, you will need to create a composite index. To create a unique index on the CUSTOMER_NAME field that includes the CUSTOMER_ID and ACCOUNT_NO fields as well, use the following SQL:

CREATE UNIQUE INDEX MYCOMPINDEX
ON ELEANOR.CUSTOMERS (CUSTOMER_NAME, CUSTOMER_ID, ACCOUNT_NO)

In your query, you must join the tables on the CUSTOMER_NAME field in order for the join to be resolved on the host. If you perform the join on CUSTOMER_ID or ACCOUNT_NO, Access will attempt to bring back the entire table and resolve the join locally.

Sample join statement:

When you define a query in the Access database, the join statement is generated automatically. Below is a sample SQL statement joining a local Access table (LocalTable) and a DB2 table (ELEANOR.CUSTOMERS).

SELECT ELEANOR_CUSTOMERS.CUSTOMER_NAME, ELEANOR_CUSTOMERS.BALANCE
FROM LocalTable INNER JOIN ELEANOR_CUSTOMERS
ON MyCustomer_Name = ELEANOR_CUSTOMERS.CUSTOMER_NAME

Because a unique index has been created as described above, the above join statement is resolved on the host and only those rows that satisfy the join are returned, resulting in optimal performance.