Use OData with IBM DB2 and Informix

A step-by-step guide

This article provides a brief introduction to OData, a Web API for data access. It then goes on to describe how OData can be used with IBM® DB2®, for Linux®, UNIX® and Windows®, for z/OS®, and for Informix®. Finally, it provides step-by-step instructions for exposing OData from DB2 in conjunction with Microsoft® Visual Studio.

Share:

John Gera (john.gera@us.ibm.com), Software Engineer, IBM

Gera author photoJohn Gera works as a Software Engineer with IBM DB2 Information Management Support team. He has a Masters Degree in Computer Science, and is an IBM Certified DB2 Database Administrator for DB2 V9.1 and DB2 V9.7. He specializes in DB2 High Availability (HA) features.



Brent Gross (gross@ca.ibm.com), Senior Technical Staff Member, IBM

Author Photo: BRent GrossBrent Gross is a Senior Technical Staff with IBM Information Management Development out of the Toronto Lab. He has over 20 years experience with IBM and has been developing for DB2 since 1995. His responsibilities have included architecture and development of stored procedure support and client interfaces. Brent has presented at many DB2 and user group conferences, His current role is the architect of the .NET and OLE DB APIs for DB2 and DB2 Connect.



Susan Malaika (malaika@us.ibm.com), Senior Technical Staff Member, IBM

Susan Malaika photoSusan Malaika is a Senior Technical Staff Member in IBM Software Group. Her specialties include the Web and databases. She is a member of the IBM Academy of Technology.



31 May 2012

Also available in Chinese Russian

Introduction

OData (see the Resources 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 Resources 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

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

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 Resources 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

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
  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
  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

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
  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
    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
    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
  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

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
    Figure 13. Global.asax (DefaultModel.RegisterContext method - 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

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
  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

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
    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

    (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

    (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
    Figure 21. Formatted 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 Resources 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.


Download

DescriptionNameSize
XML examplesUsingODataWithDB2-XML-Examples.zip2KB

Resources

Learn

Get products and technologies

  • Build your next development project with IBM trial software, available for download directly from developerWorks.
  • Evaluate IBM products in the way that suits you best: Download a product trial, try a product online, use a product in a cloud environment, or spend a few hours in the SOA Sandbox learning how to implement Service Oriented Architecture efficiently.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into Information management on developerWorks


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