So far, in this series about the IBM Data Studio integrated
development environment (IDE) that’s available with DB2 Version 9.5 (DB2 9.5), I’ve
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 source’s 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, it’s 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 that’s 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 don’t 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 that’s installed with the target
data source. Considering the fact that Microsoft is focusing on the .NET API
these days, vendors aren’t 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 haven’t read Parts 1 to 4 in this series, I assume, for this article, that you’ve at least created the SAMPLE database (using
the db2sampl –xml –sql command) and that the SAMPLE database appears in
the Database Explorer view. I’m 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 system’s command line processor (CLP):
regsvr32 <DB2_installation_path>\sqllib\function\db2olefn.dll
You will get a message confirming the registration of this
DDL file when it is complete:
Note: Even if you’ve 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. It’s important to come up with
some kind of analogy like this because you have to understand that objects
created in a database development project aren’t 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:
- Stored procedures
- User-defined functions
- SQL scripts
- XML artifacts
- Web services
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: I’m 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 that’s 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 don’t.
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 don’t 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 isn’t, you can select it from the Project drop-down list. You can
also use New to create a new data development project if one doesn’t
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, there’s 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 file’s extension
in Microsoft Access is .mdb;
don’t select the .ldb extension
since that’s the log file associated with a Microsoft Access database file.)
