Migrating a Microsoft Access 2000 Database to IBM DB2 Universal Database 7.2

Abdul Al-Azzawe, Senior Software Engineer, IBM Silicon Valley Lab

Abdul H. Al-Azzawe is a Senior Software Engineer at the IBM Silicon Valley Laboratory in San Jose and an IBM veteran since 1990. He is the lead architect for the next generation of application development tools for DB2. Prior to his current assignment, Abdul was a member of the core DB2 engine development team at the IBM Toronto Laboratory.

Summary: This article describes and evaluates two of the preferred methods for migrating an Microsoft Access database to DB2 and uses a customized case study to trace the steps involved in the two methodologies: Migration using the MS Access Export to ODBC Tool, and Migration using the IBM OLE DB Table UDF Wizard.

Tag this!

Date: 03 Dec 2001
Level: Introductory
Activity: 1190 views
Comments:

Overview

Microsoft Access provides developers with a set of tools to quickly prototype applications having either an Access or Visual Basic graphical user interface. When the design and implementation of the database is completed, developers are often required to migrate the prototype database to a production relational database server capable of supporting numerous concurrent users -- namely IBM DB2® Universal Database™ V7.2.

Because an Access database can be accessed as an OLE DB data source, there are a number of ways to migrate an OLE DB database to DB2. In this article, we will cover the two most prominent ways, namely:

Migration using the Microsoft Access Export to ODBC Tool
Migration using the IBM OLE DB Table UDF Wizard

Case study

To evaluate the various tools that may be used to migrate a Microsoft Access database to an IBM DB2 database, we must first create a fictitious database table that contains all of the supported Microsoft Access database types.

The Access 2000 dialogue, shown below, describes this fictitious table, which we call TestTable.

Figure 1. Microsoft Accest view of TestTable

Migration process

Although there are multiple ways to migrate an Access database to DB2, we will focus our attention on two such methods, namely:

Migration using the Microsoft Access Export to ODBC Tool
Migration using the IBM OLE DB Table UDF Wizard

Migration using the Microsoft Access Export to ODBC Tool

Microsoft Access provides users with the ability to export any database table to a number of targets. The only target type that supports DB2 is an ODBC data source.

The steps required to export our TestTable are as follows:

1. Register the DB2 database as an ODBC data source
For DB2 databases to be accessible using an ODBC driver manager, they must first be registered as an ODBC data source. The DB2 Client Configuration Assistant provides a quick graphical interface for registering DB2 databases as ODBC data sources.

The steps required to perform this registration are as follows:

Launch DB2 Client Configuration Assistant from your Windows task bar:
Start->Programs->IBM DB2->Client Configuration Assistant
Select the target DB2 database.
Click on Properties.
In the properties dialog, check the Register this DB for ODBC check-box.

Figure 2. Properties dialog box in Client Configuration Assistant

2. Export table using Access
Using Microsoft Access you can export any table to any number of target formats. The only relevant format that is applicable to DB2 is an export to an ODBC data source.

The steps required to export from Access are as follows:

Open the Access database and select the TestTable table.
Select the menu File>Export

Figure 3. Exporting tables using Microsoft Access
Properties dialog box in Client Configuration Assistant

Select ODBC from the Save as type combo-box.

Figure 4. Dialog box for saving as an ODBC database

Specify TestTable in the name field of the Export dialogue.

Figure 5. Choosing a name in the Export dialog box

Go to the Machine Data Source tab of the Select Data Source dialogue and select the DB2 database name you registered using DB2 CCA.

Figure 6. Selecting Machine Data Source in the Select Data Source dialog box

Specify optional User ID and Password in the Connect to DB2 Database dialogue.

Figure 7. Entering user ID and password in the Connect to DB2 Database dialog box

The export process is now complete!

3. View the exported table using DB2 Control Center
The DB2 Control Center allows you to view and alter the properties of database tables. It is generally a good idea to validate the exported table to ensure that all table properties and data have been exported properly.

The steps required to view the table using the DB2 Control Center are as follows:

Launch DB2 Control Center from your Windows task bar:
Start>Programs>IBM DB2>Control Center
Select the DB2 system, instance, and database where the TestTable was exported.
Select the TestTable entry and select the Alter pop-up menu item.
Use the Alter Table dialog to view and alter the table columns and properties.

Figure 8. Alter Table dialog box

Migration using IBM OLE DB table UDF Wizard

IBM DB2 Universal Database V7.2 features a new OLE DB UDF Assist Wizard that can be launched from a number of locations including the DB2 Stored Procedure Builder and the Data Warehouse Center.

The OLE DB Assist Wizard helps you create a table function that reads data from another database provider that supports the Microsoft OLE DB specification. You can optionally create a table view for the OLE DB table function, allowing for the data to continue to reside in the OLE DB provider but be accessible as a table within DB2.

Another feature of the OLE DB Assist Wizard is the ability to create an actual DB2 table that matches the UDF table definition and directly import all of the UDF Table data into the table itself, thus making the data locally available to DB2. Using an actual DB2 table makes it possible to create any indexes that are required to optimize access the table data.

The steps required to export our TestTable table are as follows:

1. Launch the OLE DB UDF Assist Wizard
The steps required to launch the wizard from the DB2 Stored Procedure Builder are as follows:

Launch the DB2 Stored Procedure Builder from your Windows task bar:
Start>Programs>IBM DB2>Stored Procedure Builder
Select the target DB2 database
Select to create a new (or open existing) project
Select the database from the Project Explorer
Click the OLE UDF Wizard toolbar button to launch the wizard

2. Export the Table using the OLE DB UDF Assist Wizard
The steps required to export our TestTable using the wizard are as follows:

Specify the UDF Name that will access the OLE DB datasource.

Figure 9. Choosing the UDF Name in the dialog box

Specify the OLE DB provider information, namely the connection string, which can be built graphically using the Build String button that launches the Connection String wizard (not shown here).

Figure 10. Specifying the OLE DB provider connection string

Select the Access table to export, namely TestTable.

Figure 11. Selecting the access table for export

Select the columns to export. For our case, we want all the columns.

Figure 12. Selecting the source data columns

View, modify, and reorder the column mapping from the OLE DB data types to the SQL data types prior to accessing the data.

Figure 13. Specifying the OLE DB-to-DB2 data-type mappings

Since some of the default mapping may not be optimal, such as using a DATE in place of a TIME or TIMESTAMP data type, click the Change button to modify the column mapping using the Change Column Definition dialog. (For this experiment, we use the defaults; however, it is generally a good idea to select the most optimal DB2 type using this wizard step.)

Figure 14. Change Column Definition dialog box

Specify to import the data into a new database table, and, optionally, whether to create a table view for the UDF. (For this experiment, we do not require the table view.)

Figure 15. Importing the UDF data into a new table

Validate your selections and options using the summary page.

Figure 16. Create OLE DB Table Function wizard

View the generated SQL for the selections and options you specified.

Figure 17. Viewing the generated SQL statements

Dismiss the SQL dialog and click the wizard's Finish button.
The export process is now complete!

The steps required to view the table using the DB2 Control Center are as follows:

Launch DB2 Control Center from your Windows task bar:
Start>Programs>IBM DB2>Control Center
Select the DB2 system, instance, and database where the TestTable was exported.
Select the TestTable entry and select the Alter pop-up menu item.
Use the Alter Table dialog to view and alter the table columns and properties.

Figure 18. Viewing/altering the table columns and properties

Evaluating the migration alternatives

To evaluate the two table export procedures, we first inspect the default column mappings and then list the advantages and disadvantages of both procedures.

Default column mapping

One key difference between an export using the Access export tool and the OLE DB UDF Assist Wizard is that using the Access export tool, the column mappings cannot be altered using the tool itself and prior to exporting the data. Using the UDF Assist Wizard, the column mappings can be modified, giving the user the option to select the most appropriate DB2 data type.

The following table lists the default column mapping using either tool.

Normal text denotes elements that are mapped correctly.
Bold text denotes elements that are mapped incorrectly by default (that is, they should be remapped).
Italics denotes elements that are mapped correctly, but not to the optimal DB2 data type.

Table 1. Default column mapping using either tool

Access type	Sample data	SQL type Access tool	SQL type UDF Wizard
Number -- Long Integer		INTEGER	INTEGER
Number (integer)		SMALLINT	SMALLINT
Number (byte)		SMALLINT	SMALLINT
Number (single)		REAL	REAL
Number (double)		DOUBLE	DOUBLE
Number (decimal(10,2))		DECIMAL(10,2)	DECIMAL(10,2)
Text (with default value)		VARCHAR	VARCHAR
Text (allow zero length)		VARCHAR	VARCHAR
Memo		LONG VARCHAR	CLOB
Date/Time (general date)	6/19/2001 5:31:22 PM	TIMESTAMP	DATE *
Date/Time (long date)	Sunday, June 19, 1999	TIMESTAMP	DATE
Date/Time (medium Date)	19-Jun-1994	TIMESTAMP	DATE
Date/Time (short date)	6/19/2001	TIMESTAMP	DATE
Date/Time (long time)	5:31:22 PM	TIMESTAMP	DATE *
Date/Time (medium time)	5:31 PM	TIMESTAMP	DATE *
Date/Time (short time)	17:31	TIMESTAMP	DATE *
Currency (currency)	$555.77	DOUBLE	DECIMAL(19,4)
Currency (fixed)	2222.77	DOUBLE	DECIMAL(19,4)
Currency (standard)	2,222,222.99	DOUBLE	DECIMAL(19,4)
Currency (percent)	98.20%	DOUBLE	DECIMAL(19,4)
Currency (scientific)	6.7722E+02	DOUBLE	DECIMAL(19,4)
AutoNumber (long integer)		INTEGER	INTEGER
Yes/No Flag		SMALLINT	SMALLINT
OLE Object		LONG VARCHAR	BLOB

Note: DATE can be remapped using the wizard to the proper SQL type, namely TIME. The reason behind choosing a DATE as the default in the UDF wizard is that the OLE DB provider for Jet 4.0 returns the type for this column as being a DBTYPE_DATE.

Evaluating Microsoft Access Export to ODBC tool

To properly evaluate the procedure required to export an Access table to DB2 using the Access export to ODBC tool, we look at the advantages and disadvantages of using this method.

Advantages

The advantages of exporting an Access table using the export tool are:

Intuitive export procedure.
Table name is automatically delimited to preserve letter casing and spaces.
Column names are automatically delimited to preserve letter casing and spaces.

Disadvantages

The disadvantages of exporting an Access table using the export tool are:

No control over target table schema.
No control over target column types.
Text fields always converted to VARCHAR.
Indexed property not preserved/used.
Primary key property not preserved.
Default value not preserved.
Required property is not preserved or used; it always uses nullable.
Auto Number is not translated to a DB2 Identity column.
All Access Date/Time types converted to DB2 TIMESTAMP.

Comments

Some of the neutral features of exporting an Access table using the export tool are:

Access Memo type converted to DB2 Long Varchar type.
Access OLE type converted to DB2 Long Varchar type.

Evaluating the IBM OLE DB Table UDF Wizard tool

To properly evaluate the procedure required to export an Access table to DB2 using the OLE DB UDF Assist Wizard, we will look at the advantages and disadvantages of using this method.

Advantages

The advantages of exporting an Access table using the UDF Wizard are:

Intuitive easy-to-follow export procedure.
Schema name may be specified for the table.
Have the option of either accessing the data locally using the generated table, or remotely from the OLE DB source, using a UDF or a table view for the OLE DB data source.
Column names are automatically delimited to preserve letter casing and spaces.
Can choose a subset of the columns.
Can choose to export the results of an SQL Query covering a susbset of the table rows or rows resulting from joins across multiple tables.
Can reorder columns in final table and UDF.
Can remap column types to more optimal DB2 SQL types before importing the data.

Disadvantages

The disadvantages of exporting an Access table using the UDF Wizard are:

No control over target column names.
Text fields always converted to VARCHAR.
Indexed property not preserved or used.
Primary key property not preserved.
Default value not preserved.
Required property is not preserved or used; it always uses Nullable.
Auto Number is not translated to a DB2 Identity column.
All Access Date/Time types converted to DB2 DATE.

Comments

Some of the neutral features of exporting an Access table using the UDF Wizard are:

Access Memo type converted to DB2 CLOB type.
Access OLE type converted to DB2 BLOB type.

Conclusion

On the Microsoft Windows platforms it is common to prototype Microsoft Visual Basic applications using Microsoft Access databases. When the prototype phase is completed, applications are then migrated to a relational database server, specifically IBM DB2 Universal Database V7.2.

There are a number of ways to export Access database tables to DB2. This article described two such methods: the Access Export Tool and the DB2 OLE DB Table UDF Assist Wizard. The UDF wizard has many advantages over the alternative. Some of these include the flexibility it offers in terms of remapping of column types, reordering of columns, selecting a subset of columns, providing more accurate default data types, and the ability to export tables, accommodate results of a query from possibly multiple-joined tables, as well as the ability to create a view to access the data directly from the OLE DB data source.

About the author

Comments

Trademarks | My developerWorks terms and conditions

Close [x]

Help: Update or add to My dW interests

What's this?

This little timesaver lets you update your My developerWorks profile with just one click! The general subject of this content (AIX and UNIX, Information Management, Lotus, Rational, Tivoli, WebSphere, Java, Linux, Open source, SOA and Web services, Web development, or XML) will be added to the interests section of your profile, if it's not there already. You only need to be logged in to My developerWorks.

And what's the point of adding your interests to your profile? That's how you find other users with the same interests as yours, and see what they're reading and contributing to the community. Your interests also help us recommend relevant developerWorks content to you.

View your My developerWorks profile

Return from help

Close [x]

Help: Remove from My dW interests

What's this?

Removing this interest does not alter your profile, but rather removes this piece of content from a list of all content for which you've indicated interest. In a future enhancement to My developerWorks, you'll be able to see a record of that content.

View your My developerWorks profile

Return from help

static.content.url=http://www.ibm.com/developerworks/js/artrating/

SITE_ID=1

Zone=Information Management

ArticleID=13251

ArticleTitle=Migrating a Microsoft Access 2000 Database to IBM DB2 Universal Database 7.2

publish-date=12032001

author1-email=dmdd@us.ibm.com

author1-email-cc=