Use OData with IBM DB2 and Informix

A step-by-step guide

Comments

OData (see the Related topics section for more information) is a specification for a Web API for data access to enable resources such as tables in databases to be accessed from Web browsers and mobile devices. OData specifies create, delete, update, delete (CRUD), and query over HTTP on resources (data or applications). It also specifies the way the results are formatted in ATOM (XML) and JSON. OData is like a mini-ODBC or JDBC for the Web. More precisely, OData allows clients to construct URIs that name an entity set, filter among the entities it contains, and traverse relationships to related entities and collections of entities.

Figure 1 shows how DB2 or Informix can be exposed on the Web through their ADO.Net enablement. Microsoft Visual Studio provides the tooling to enable the database data to be exposed via HTTP on the Web. The database data can be created, updated, deleted, and queried via the OData syntax from Web Browsers and other OData consumers (see the Related topics section for more information).

Figure 1. OData overview
diagram shows web browser on left, connected via fixed query and CRUD services to OData services provider, which connects to the DB2 or                     Informix database
diagram shows web browser on left, connected via fixed query and CRUD services to OData services provider, which connects to the DB2 or Informix database

Figure 2 shows CSDL (Conceptual Schema Definition Language), which is an XML notation that describes underlying resources using an entity relationship model that can be accessed through OData.

Figure 2. CSDL (Conceptual Schema Definition Language)
shows web browser on left, connected through fixed query and CRUD                     services to OData CSDL, OData Service Provider, ADO.Net enablement solutions, and DB2 or Informix
shows web browser on left, connected through fixed query and CRUD services to OData CSDL, OData Service Provider, ADO.Net enablement solutions, and DB2 or Informix

CSDL is often used at development time, for example in tools or model mappers. CSDL is optional and is generated by Visual Studio to help consumer applications understand the structure of the data being exposed. CSDL is like metadata in JDBC and ODBC, helping client applications understand what they are accessing.

Exposing tables in the sample database on the web using OData

The next sections describe in detail how you can do the following.

  • Perform initial setup, such as defining the connection to the database.
  • Create an ADO.Net entity model.
  • Select the DB2 tables that will be exposed via OData.
  • Create an OData service (WCF service) for the selected tables.
  • Test the OData service.

The description uses the OData runtime that is incorporated into Microsoft Visual Studio. However, once the support has been tested in Visual Studio, other OData runtimes can be used. See the Related topics section for more information.

Creating a new web site

  1. Start Visual Studio. From the File menu, click New Web Site. The New Web Site dialog box is displayed.
  2. In the left pane under Installed Templates, select Visual C#.
  3. In the center pane, select ASP.NET Dynamic Entities Web Site.
  4. In the Web location box, select File System and then enter the name of the folder where you want to store the pages of the web site. For example, type the folder name C:\WebSites\DB2OData and click OK. Visual Studio creates the web site, as shown in Figure 3.
Figure 3. A new web site
shows ASP.NET Dynamic Entities Web Site selected
shows ASP.NET Dynamic Entities Web Site selected

Adding a data connection to Server Explorer

  1. From the Tools menu, select Connect to Database. The Add Connection dialog box is displayed.
  2. Click IBM DB2 and IDS Servers, as shown in Figure 4, and then click Continue.
    Figure 4. Adding data connection to Server Explorer
    Shows IBM DB2 and IDS Servers selected
    Shows IBM DB2 and IDS Servers selected
  3. As shown in Figure 5, in the Select or enter server name box, enter 127.0.0.1/localhost, or the hostname. In the User ID box, type db2admin. In the Password box, enter your password. Select the Save my password check box.
    Figure 5. Connection information
    shows connection information
    shows connection information
  4. In the Select or enter a Database Name box, type a name for the database, such as SAMPLE.
  5. Click Test Connection, and then click OK.
  6. As shown in Figure 6, in Server Explorer, you can optionally expand Data Connections and view the database tables.
    Figure 6. Database tables
    shows explorer view of the database tables
    shows explorer view of the database tables

Adding data to the web site and creating a new ADO.NET entity data model

An entity data model is required to expose DB2 data using WCF data services. Perform the following steps to add data to the site and create the ADO.NET entity data model.

  1. In Solution Explorer, right-click the project and then click New Item. The Add New Item dialog box is displayed.
  2. In the left pane under Installed Templates, select Visual C#. In the center pane, select ADO.NET Entity Data Model.
  3. In the Name box, type a name for the database model. For example, enter the name DB2.edmx, and then click Add, as shown in Figure 7.
    Figure 7. Create entity data model
    shows ADO.NET Entity Data Model selected
    shows ADO.NET Entity Data Model selected
  4. When prompted, click Yes.
  5. From the Entity Data Model Wizard, select Generate from database, and then click Next, as shown in Figure 8.
    Figure 8. Entity data model wizard
    shows Generate from database selected
    shows Generate from database selected

    The Choose Your Data Connection dialog box is displayed.
  6. In the drop-down list, select the connection that you configured previously. For example, db2admin@SAMPLE. Click Yes to include the sensitive data (user name and password) in the connection string, and then click Next, as shown in Figure 9.
    Figure 9. Choose your data connection
    shows data connection details
    shows data connection details

    The Choose Your Database Objects dialog box appears.
  7. Click the triangle to expand the Tables node. Select the check box for DEPARTMENT and EMPLOYEE tables, and then click Finish, as shown in Figure 10.
    Figure 10. Choose your data objects
    shows department and employee tables selected
    shows department and employee tables selected
  8. The DB2.edmx page will appear with the new Entity Data Model, as shown in Figure 11.
    Figure 11. DB2.edmx page
    shows entity data model with employee and department tables
    shows entity data model with employee and department tables

Registering the data content

  1. As shown in Figures 12 and 13, from the Solution Explorer, open the Global.asax file.
  2. Uncomment the line that contains the DefaultModel.RegisterContext method.
  3. Set the context type to SAMPLEModel.SAMPLEEntities, and set the variable ScaffoldAllTables to True.
    Figure 12. Global.asax (DefaultModel.RegisterContext method - commented)
    line that contains the DefaultModel.RegisterContext method is commented
    line that contains the DefaultModel.RegisterContext method is commented
    Figure 13. Global.asax (DefaultModel.RegisterContext method - uncommented)
    line that contains the DefaultModel.RegisterContext method is uncommented
    line that contains the DefaultModel.RegisterContext method is uncommented
  4. Save the Global.asax file.

Adding WCF data service

  1. From the Solution Explorer, right-click the project name DB2OData. The Add New Item dialog box is displayed.
  2. Under Installed Templates, in the left pane, select Visual C# and in the center pane, and then click WCF Data Service.
  3. In the Name box, enter a name for the data service, such as WcfDataService.svc, and then click Add, as shown in Figure 14.
    Figure 14. Adding WCF Data Service
    Shows WCF Data Service selected
    Shows WCF Data Service selected

Configure the WCF data service

  1. As shown in Figure 15, in the WcfDataService.cs file, replace the code comments /* TODO: put your data source class name here */ with SAMPLEModel.SAMPLEEntities.
    Figure 15. Data source class name and MyEntitySet
    MyEntitySet is highlighted
    MyEntitySet is highlighted
  2. Uncomment the code containing the config.SetEntitySetAccessRule. Replace MyEntitySet with an asterisk "*", as shown in Figure 16.
    Figure 16. Change code for WcfDataService.cs
    WcfDataService.cs - Change the code as per step 1 and 2 above
    WcfDataService.cs - Change the code as per step 1 and 2 above

Testing the WCF data service

  1. To run the application, from the Debug menu, click Start Debugging. If prompted to enable debugging, click OK.
  2. In the web browser, enter a URI to return all of the records from a DB2 table through the data service.
  3. Enter http://localhost:15452/DB2OData/WcfDataService.svc/ to view the Entities that have been included, as shown in Figure 17.
    Figure 17. WcfDataService.svc
    XML file includes DEPARTMENT and EMPLOYEE entities
    XML file includes DEPARTMENT and EMPLOYEE entities

    To see the text of this file, view the WcfDataService.svc.txt file in the download with this article.
  4. Enter http://localhost:15452/DB2OData/WcfDataService.svc/DEPARTMENTs to view the Department table, as shown in Figure 18.
    Figure 18. Department table
    Department Table in XML format
    Department Table in XML format

    (View a larger version of Figure 18.)

    See the download for the text of this figure.
  5. To search for Employee ID = 000010, paste http://localhost:15452/DB2OData/WcfDataService.svc/EMPLOYEEs('000010') in the URL, as shown in Figure 19.
    Figure 19. Employee table
    Employee Table in XML format
    Employee Table in XML format

    (View a larger version of Figure 19.)

    See the download for the text of this figure.
  6. You can use Add-Ons (like Bamboo for Firefox) to view the formatted XML data, as shown in Figures 20 and 21.
    Figure 20. Formatted employee table
    shows the data for each row in Employee Table
    shows the data for each row in Employee Table
    Figure 21. Formatted department Table
    shows data for each row in Department Table
    shows data for each row in Department Table

Conclusion

This article showed you how to expose DB2 data over HTTP via OData using Microsoft Visual Studio, making it possible to access DB2 from mobile devices and Web browsers. OData libraries exist for a number of mobile devices (see the Related topics section for more information). Similar OData support is available for Informix and DB2 on z/OS.

Currently, temporal and XML data are not supported through Visual Studio.


Downloadable resources


Related topics


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=818405
ArticleTitle=Use OData with IBM DB2 and Informix
publish-date=05312012