DB2 9.5 and IBM Data Studio: Using OLE DB to Integrate Data - Page 2
January 8, 2008
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 isnt 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 dont 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 doesnt work. Heres 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 Ive 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 couldnt 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, its 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:
Thats 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 arent 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.