13. Ensure that you can
connect to the target data source using the selected OLE DB provider by
clicking Test Connection. If the test is successful, you will see the
following message:
If the connection isn’t successful,
ensure that you selected the correct OLE DB provider for the target data source
and that the data source is available; for example, if you are connecting to a DB2
or an Oracle database, you have to ensure that the database instance is
started.
14. The Options page opens.
You can use this page to specify some additional settings for the selected
provider. The settings available will depend on the provider you selected. For
our example, just click Finish.
15. The connection string
for the target data source is automatically added to the Connection string field,
avoiding the pitfalls associated with manual entry of these strings within your
application or function definition.
Of course, you don’t find out
about manual entry errors during the design phase or the build phase. You
figure it out when you go to invoke the function or test your application and
the database connection doesn’t work. Here’s another example of how IBM Data
Studio will save you a lot of time.
16. Click Test Connection,
and click Next if the connection is successful.
If the connection is successful,
you will see the same message shown in Step 13. (I always test connections as I
work through this wizard in case I’ve introduced an error, or an option I
selected makes the connection invalid.)
17. The Source Data page opens.
Select Access source data using a table and select Customers in the
Name drop-down list, as shown below, and click Next:
The tables in the Name drop-down
list are from the target Microsoft Access database. You can now test the
connection such that the data in the target table is returned to IBM Data
Studio when you click Show Sample Content:
This is a great way to ensure that your
function will retrieve the data you actually want.
Also, note the Access source
data using an SQL query radio button. You select this option if you want to
manually write an SQL query to run against the target table. For example,
suppose you were only interested in customers that reside in Mexico - you
couldn’t apply such a restriction to the Customers data using the Access
source data using a table option; however, you can with this option, as
shown below:
If you write your own SQL, it’s
important that you follow the semantics of the SQL used at the target data
server. For example, if you were to restrict a result set using double quotation
marks (“”s), as shown in the previous figure, you would see the following error
message:
That’s because in DB2 9.5 you
define strings using single quotation marks (‘’s). By contrast, if you used single
quotation marks to restrict a result set in Microsoft Access, you would see an
error message similar to this:
This example presents one of the
drawbacks of using OLE DB functions to integrate data and should illustrate why
I called it a ‘quick-and-dirty method’. Quite simply, you have to know the
nuances of each target data server. Now imagine trying to integrate data from
Oracle, SQL Server, Teradata, Excel, Sybase, and more! OLE DB functions just
aren’t suitable for large integration projects. A product such as IBM
Information Server (or a subset of it such as IBM WebSphere Federation Server)
on the other hand, would automatically shield you from such nuances, which is
why I recommended its use earlier in this article for large-scale integration
projects.
18. The Data Columns page opens.
This page allows you to apply a projection to your result set by adding or
removing the columns in the result set. For this example, leave the defaults as
is, and click Next.
Note in the previous figure that
you can click Show Sample Content to give you once again a sample of the
data that will be returned by your function.
19. The Column Mapping page
allows you to specify the mapping of the OLE DB data types to DB2 data types.
For our example, simply click Next.
This feature is very useful because
it allows you to leverage logic in your DB2 data server for this integration
effort. For example, you may have created a user-defined type (UDT) to handle
phone numbers.
Note that you can also use the Move
Up and Move Down buttons to perform another type of projection on
the result set by reordering the columns that appear in it.
If you make any changes at this
stage of the OLE DB definition process, you will have the opportunity to view
the sample output of the function via the Show Sample Contents button
that appears when a change is made.
20. Click Next to
move to the Options page. Here you can specify whether to create a view that
uses the OLE DB function to return results to your application, have IBM Data
Studio automatically create a DB2 table and use your function to import the
data from the target table in a DB2 table, or both.
For our example, select both the Create
a corresponding table view and Create a new table and import the UDF
data into it check boxes, and use the View name and Comments fields
to define and describe these objects as shown below.
You can optionally select Replace
Existing if you want IBM Data Studio to automatically drop any
corresponding objects that exist with the names specified on this page before
creating the new objects. Click Next.
21. The Summary page opens.
This page summarizes all the options that you selected using the wizard. You
can also see what IBM Data Studio will create as a result of the options you
selected. If you are satisfied with the information here, click Finish and
IBM Data Studio will create the object (or objects, as is the case in this example)
for you.
You can click Show SQL to
display the data definition language (DDL) that IBM Data Studio will use to
create the objects defined in this wizard:
22. In the Data Output window,
you can see that IBM Data Studio successfully deployed the function you created
along with its associated objects (the defined table and view).
23. Refresh your database
connection object in the Database Explorer view by right-clicking it and
selecting Refresh. (If you recall from Part 1, this is a best practice
to follow – although not always needed – because it assures that the object
cache is up-to-date.)
24. Expand the database
connection object and locate the Tables, User-Defined Functions,
and Views folders. You should be able to see all the new objects that
were created by IBM Data Studio.
» 
Comment and Contribute
