DB2 9.5 and IBM Data Studio: Using OLE DB to Integrate Data - Page 3
January 8, 2008
Seeing it all in action...
You can see the OLE_DB_2_MSACCESS_NORTHWIND_CUSTOMERS function within the User-Defined Functions folder. This function was used to populate the MSACCESSCUSTOMERS table and is called each time an SQL statement is run against the VIEW_OVER_MSACCESS_CUSTOMERS view.
For example, you can use IBM Data Studio to look at the data from the VIEW_OVER_MSACCESS_CUSTOMERS table, as shown below:
Naturally, if you compared this result set to the same query run natively in Microsoft Access, you would end up with the same results:
Of course, any method you use to access the table or view that you created will return data to your application. For example, in the following figure you can see I called the same view using the DB2 command line processor (CLP):
Remember that if you deleted the NORTHWIND database, or changed its location, the table would continue to operate just fine (since the data was imported into it by the function you created), but the view would fail:
Notice that the columns are still available? This is because there is a meta-data layer for this function within DB2 9.5. So here is another nuance you should be aware of when using these kinds of functions: if someone moves the data, your objects can break. With IBM Information Server, there are health indicators that are surfaced when a nickname over a remote data server become invalid.
Wrapping it up...
In this article, I took you through the steps involved in creating an OLE DB function to integrate data from a broad spectrum of data sources, in this case, specifically Microsoft Access. You saw that this feature can be handy and that with IBM Data Studio you didnt have to write a single line of code to make this all happen. In my next article, Im going to show you how you can use IBM Data Studio to create an SQL statement.
IBM, DB2, and WebSphere are registered trademarks of International Business Machines Corporation in the United States, other countries, or both.
Microsoft and Windows are trademarks of Microsoft Corporation in the United States, other countries, or both.
Other company, product, or service names may be trademarks or service marks of others.
Copyright International Business Machines Corporation, 2007. All rights reserved.
The opinions, solutions, and advice in this article are from the authors experiences and are not intended to represent official communication from IBM or an endorsement of any products listed within. Neither the author nor IBM is liable for any of the contents in this article. The accuracy of the information in this article is based on the authors knowledge at the time of writing.