DB2 9.5 and IBM Data Studio: Using OLE DB to Integrate Data
January 8, 2008
So far, in this series about the IBM Data Studio integrated development environment (IDE) thats available with DB2 Version 9.5 (DB2 9.5), Ive shown you how to set up database connection objects and the actions that you can perform on them. In addition, I introduced you (in Part 2) to some of the features available in the Database Explorer view, and Part 3 and Part 4 gave you a full tour of overview diagrams. In this article, I want to introduce you to the OLE DB function capability available in IBM Data Studio.
Object Linking and Embedding Database (OLE DB) Functions
Microsoft OLE DB is a set of application programming interfaces through which applications can uniformly access data that is stored in diverse information sources. OLE DB is conceptually divided into OLE DB providers and OLE DB consumers. An OLE DB provider is a software component that exposes OLE DB interfaces, and an OLE DB consumer is the application that accesses the OLE DB interfaces and consumes the data.
The IBM Data Studio gives you the ability, without writing a single line of code, to create OLE DB functions within a DB2 data server that talks to OLE DB providers. You can use this function to create a table and populate it with the target OLE DB data sources data and persist that data locally in a DB2 table. You can also create a view that calls the OLE DB function and provides access to the target data in the same manner as a regular DB2 view.
Today, most data sources have some sort of OLE DB provider with DB2 9.5, its automatically installed with an IBM Data Server client. For example, using IBM Data Studio, you can define an OLE DB table function to populate a local table automatically with data from a Microsoft Access table. (I will show you how to do that in this article.) In fact, lots of applications (not just data servers) have OLE DB providers today. For example, the Microsoft Exchange address book has a provider, which means that you can easily create a report in DB2 9.5 that seamlessly combines data from an OLE DB table function built over your Exchange address book and all the logic that lies within your DB2 database.
Using OLE DB table functions in DB2 9.5 reduces your application development effort by providing built-in access to most OLE DB providers. With OLE DB table functions, you use a generic OLE DB consumer interface thats built into DB2 9.5 to retrieve data from most OLE DB providers. You only need to register a table function as LANGUAGE OLEDB, and refer to the OLE DB provider and the relevant row set as a data source; of course, IBM Data Studio takes care of all of this for you. Quite simply, you dont have to do any function programming to take advantage of OLE DB table functions in DB2 9.5.
Once you get to know these functions in DB2 9.5, you may think they are some sort of panacea for integration. Well, they can be. However, you have to understand that the functionality provided by this method can only be as good as the OLE DB provider thats installed with the target data source. Considering the fact that Microsoft is focusing on the .NET API these days, vendors arent investing in OLE DB of late. Of course, if you want a scalable and rich integration platform, look at the capabilities of the IBM Information Server, which provides native access to target data sources with function compensation and optimizations to make it even easier than this method. With that said, if you want a quick-and-dirty way to grab that data from Microsoft Access (or from any other OLE DB data source, for that matter), this may just do the trick.
Things you have to do to follow the examples in this article...
In case you havent read Parts 1 to 4 in this series, I assume, for this article, that youve at least created the SAMPLE database (using the db2sampl xml sql command) and that the SAMPLE database appears in the Database Explorer view. Im also assuming that you have access to the NORTHWIND database that comes with Microsoft Access (which further assumes that you have Microsoft Access installed).
Finally, the db2olefn.dll file must be registered on your computer before you can create OLE DB functions using IBM Data Studio. To register this DLL file, enter the following command from your operating systems command line processor (CLP):
You will get a message confirming the registration of this DDL file when it is complete:
Note: Even if youve already registered this DLL, the message in the previous figure should still appear.
Building your first OLE DB Function
To create an OLE DB function that creates a table in your DB2 SAMPLE database and populates it with all the data from the CUSTOMERS table in your Microsoft Access NORTHWIND database, perform the following steps:
1. Select New>Project. The New Project window opens.
2. Select Data Development Project and click Next.
Before you create routines or other database development objects, you typically create a data development project to store your objects. I like to think of database development projects as a collection of thoughts that you can focus on. Its important to come up with some kind of analogy like this because you have to understand that objects created in a database development project arent deployed there. For example, if you create a stored procedure in a database development project, deploy it to your database, and subsequently erase that stored procedure from the project, it still exists in the database.
A data development project is linked to one database connection in the Database Explorer view that gives the project a target database to deploy objects to. (At deployment time, though, you can choose to deploy an object to a different database.)
Specifically, a data development project is used to store and organize data application development artifacts. You can store and develop the following types of objects in a database development project:
You can also test, debug, export, and deploy these objects from a data development project. The wizards that are available in a data development project use the connection information that is specified for the project to help you develop objects that are targeted for that specific database.
Using the New Data Development Project wizard (shown in the following steps), you specify basic information about the project including the name and current schema. You also specify which database connection to use for the project. You can use an existing connection or create a new connection. Once created, a data development project can be found in the Data Project Explorer view.
3. Enter DatabaseJournalProject in the Project Name field. The rest of the defaults are fine. Click Next.
Note: Im just going to show the basics of database development projects to facilitate the example in this article. In a future installment of this series, I will take you through the ins and outs of database development projects.
4. Select Use an existing connection, select a connection object thats bound to the SAMPLE database, and click Finish.
You can see in the previous figure that I have a number of different database connection objects that connect to the SAMPLE database: some have filters and some dont.
5. Expand your new project to see all of the objects that a data development project can contain, as shown below:
6. Click New>User-Defined Function, and then click Next.
If you dont see the User-Defined Function option from the File menu, you may need to select Other>Data from the Select a Wizard window. After you select it once, it should automatically be added to the File menu for subsequent usage.
The New User-Defined Function wizard opens. Ensure that the Project field is set to the DatabaseJournalProject. If it isnt, you can select it from the Project drop-down list. You can also use New to create a new data development project if one doesnt exist.
Note: You can also access the New User-Defined Function wizard directly from your data development project by right-clicking the User-Defined Functions folder.
7. In the Name field, enter OLEDB_2_MSACCESS__NORTHWIND_ CUSTOMERS.
8. Set the Language field to OLE DB, and click Next.
9. IBM Data Studio may try to connect to the target database where you want to create your OLE DB function. If the Database Authorization window appears, enter a set of credentials that has the authority to create functions on the SAMPLE database, and click OK. (If you are using the user account used to create the SAMPLE database, you should already have this authority.)
10. The OLE DB Provider window opens. You can use the Connection string field to manually type a connection string (the way most programmers do); however, theres a better way. Click Build String.
11. Select the appropriate provider from the Providers page for the target data source. Since you want to access a Microsoft Access database, select the Microsoft Jet 4.0 OLE DB Provider, and click Next.
You can see in the previous figure that IBM Data Studio automatically lists all of the OLE DB providers that are registered on your system. (Just below the provider you selected for this example is the IBM OLE DB Provider for DB2, which is the driver you would use if you wanted to access another DB2 database in this manner.)
12. The Connect window opens. Select the ellipsis ( ) in the Database file field and select the file that represents the Northwind database for your Microsoft Access installation. (A database files extension in Microsoft Access is .mdb; dont select the .ldb extension since thats the log file associated with a Microsoft Access database file.)