Accessing Oracle, Netezza, and Microsoft Excel from DB2 z/OS

I regularly see people ask how to access data on UNIX or Windows from apps on IBM's z/OS (mainframe) operating system.  However, I haven't been able to find anything that discusses the options.  Therefore, I'm using this post to provide the basics about one solution - using IBM's InfoSphere Federation Server as an interface.


This solution is for people running DB2 on z/OS.  It's probably best discussed with a simple example and a picture.  So, my example is - let's say you want to issue SQL to Oracle on UNIX from an app on z/OS.  Here's a high-level picture of how it works:



The first things you need to do are to set up Federation Server and have it point to the Oracle database.  More specifically:

  1. Create a federated database in the Federation Server.
  2. Tell this database about your Oracle database(s).
  3. Create nicknames for each Oracle table that your z/OS apps wants to access (nicknames are similar to Oracle dblinks).


If you've never worked with a federated database, you can try it out very quickly by getting a copy of DB2 Express-C (the free DB2) and following the Fast Start for data federation here on ChannelDB2.  DB2 Express-C does not provide access to Oracle tables.  However, once you've been through the Fast Start's configuration for DB2 tables, you don't need to know much more to work with Oracle tables.  When you're ready, the DB2 LUW 9.7 Information Center has plenty of information about federated access to Oracle under the folder called, surprisingly enough, 'Federation' :)


On the z/OS side, you'll need to add entries to the DB2 z/OS communication database.  These point to the federated database.  Your z/OS app will use this information (the 'location') to connect to federated database through DB2's DDF.  This use of DDF is possible with Federation Server because DB2 z/OS can be a client to Federation Server just as it can be a client to DB2 for Linux, UNIX, and Windows.


That's it.


A Few Variations


The example shows only one Oracle database being accessed.  However, many database can be accessed through a single Federation Server.  Many types of databases can be accessed as well - Teradata, Netezza, Microsoft SQL Server, and more.  You can even take advantage this data virtualization to join tables from different databases.


Federation Server does not have to run on the same system as your Oracle or other databases.  The following picture shows a variation of our example with Oracle running on a different system from the one where Federation Server runs:



The one exception to this is if you want to access Microsoft Excel.  You'll need a Federation Server running on the Windows where your Excel files are.


Using a DB2 LUW and InfoSphere Warehouse


All DB2 LUW and InfoSphere Warehouse editions come bundled with a subset of Federation Server function.  The subset depends on the edition.  For example, DB2 Advanced Enterprise Server Edition and InfoSphere Warehouse Enterprise Edition come bundled with Federation Server's Oracle, DB2, and Informix access at no additional cost.  More importantly, all other DB2 LUW and InfoSphere Warehouse editions can be extended using an install of Federation Server.


Feel free to ask questions.  I'll update this post if I think of any points I missed.




Views: 2504


You need to be a member of ChannelDB2 to add comments!

Join ChannelDB2

Featured Downloads

Try BLU Acceleration on Cloud

© 2020   Created by channeldb2.   Powered by

Badges  |  Report an Issue  |  Terms of Service