From my experience working with DB2 shops on .NET application development projects, I have learned that most .NET developers are less familiar with the different flavors of DB2.  This is why they choose standards based connectivity from either IBM or Progress DataDirect.

The latest standard is OData, or “ODBC for the web”. DB2 is the core of many on-premise business systems, and this data has tremendous potential in the growing world of web and mobile.  Not surprisingly, Progress DataDirect (world’s leading contributor to data API standards) introduced instant and secure OData production from any DB2 system in a self service way for everyone, including those without specific DB2 knowledge.

A great example of OData’s power with DB2 is Salesforce.com that supports the consumption of OData sources for a new style of data federation using external objects (I will be speaking about this at Dreamforce ’14).   For example, OData provides instant access to DB2 for i MRP/ERP systems from the Salesforce CRM customer data in the cloud.  Here’s a list of OData consumers.

DataDirect Cloud OData production works with the following DB2 systems:

DB2 for LUW:

DB2 V10.1, V10.5 for Linux, UNIX, Windows
DB2 V9.1, V9.5, V9.7,
V9.8 for Linux, UNIX, Windows
DB2 V8.x for Linux, UNIX, Windows

DB2 for z/OS:

DB2 V11 for z/OS
DB2 V10 for z/OS
DB2 V9.1 for z/OS
DB2 UDB V8.1 for z/OS

DB2 for i:

DB2 i 7.1 (DB2 UDB V7R1 for I)
DB2 i 6.1 (DB2 UDB V6R1 for I)
DB2 for i 5/OS V5R4 (DB2 UDB V5R4 for I)

Getting started with self service DB2 OData production securely from behind a corporate firewall

  1. Register for trial of DataDirect Cloud connectivity service
  2. Click downloads to install the on-premise connector to generate a connector ID

  3. Create new IBM DB2 data source

     

    datadirectcloud

  4. Enter the DB2 connection information from perspective on on-premise connector including the connector ID from step #2. For example, I’m connecting to a DB2 for i system running in Progress Software’s corporate firewall.

     

    db2
    Click on Advanced Tab to configure the OData map which is a JSON string during the beta, but subject to change when GA (check latest documentation).  The following value exposes two entities with a foreign key relationship. {"schemaName":"TEST01","includedTables":["ACCOUNT","OPPORTUNITY"],"tableMapping":[{"name":"OPPORTUNITY","key":["SYS_NAME"]}]}

  5. For a demo, I selected PragmatiQa XOData which is one of several Odata consumers available from the list compiled by Odata.org (thanks to PragmatiQa for support during the trial)
  6. Launch the application from a chrome browser and select “Choose Access Option” > “OData Metadata URL” and enter https://service.datadirectcloud.com/api/odata/DB2/$metadata

  7. Click Connection tab and enter the DataDirect Cloud credentials for an OData 2.0 url.

     

    pragmatiQa1

  8. Click “Use this Setup” and then “Get Details”
  9. Click Diagram tab to see OData feed entity relationship diagram

     

    pragmatiQa2

  10. Click API Details for entity data types

     

    pragmatiQa3

  11. Click Query Builder and select “Account” and then click “Expand” and check “Opportunities” which has a foreign key relationship.  Then click “Get Data” from the following url:

https://service.datadirectcloud.com/api/OData/DB2/ACCOUNTS?$top=100&$expand=OPPORTUNITIES

pragmatiQa4

At this point, we've queried DB2 for i data using OData application.  This same feed can be consumed by a number of applications including Microsoft BI tools, Microsoft PowerPivot, CMS (Drupal or Joomla), Tableau, Telerik, and a host of development tools and libraries for web/mobile applications.
Summary

  • OData is a pretty cool data connectivity standard for the web
  • DataDirect Cloud produces OData feeds for on-premise data sources that reside behind a firewall
  • Producing this data is fully self service and secure without the requirement for data source specific knowledge, development resources nor network security expertise.

DB2 is one of many on-premise data sources available in DataDirect Cloud that will instantly produce OData, in addition: SQL Server, Oracle, Greenplum, MySQL, Sybase, Hadoop Ecosystem, Postgres, Progress OpenEdge, MongoDB, Cassandra.

These are in addition to the really exciting cloud data sources we’re enabling for OData such as Salesforce, Oracle Marketing Cloud (Eloqua), Marketo, Microsoft Dynamics, Amazon Redshift, Sugar CRM, and WorkDay.

Check out the most current list of OData data sources.

Let's talk

@SAsInSumit

www.linkedin.com/in/meetsumit