With the move towards integrated software stacks, you may not always be aware of
all the technologies you have in products you own. IBM's data federation technology is no
exception. It's found in so many IBM products that many people aren't sure whether they have it or not. To cut down on confusion (I hope), I put together a quick reference that shows the products that allow federation, but are not data federation products themselves. How do they do this? They include a subset of IBM's InfoSphere Federation Server. In this post, I focus on those that allow federation though SQL. This includes distributed joins, which are queries that join tables from different databases.

These products fall in to three groups:

DB2 editions

InfoSphere Warehouse editions

DB2 Connect editions

Not all of them allow federation of all Federation Server data sources. Therefore, I have tables that show which data sources are allowed in the various editions. For those about to ask... yes, IBM has many more products that include data federation for specialized use instead of general SQL access. For example, IBM InfoSphere Data Replication.
However, those are not discussed in this post. Maybe a future one :)

InfoSphere Federation Server

Here a few quick points about Federation Server. First, this product contains all IBM data federation technology on UNIX and Windows.
It provides the data federation found in the other products discussed in this post. Next, for mainframes, DB2 z/OS access is provided in Federation Server at no additional cost. However, if you need to federate classic mainframe data sources - IMS, VSAM, ADABAS, or IDMS - through Federation Server, you will need to buy IBM's Classic Federation Server for z/OS.

DB2 9.7 for Linux, UNIX, and Windows

The following table shows which data sources you're allowed to federate in the various DB2 editions. Two things to note:

I limit this table to 9.7 since the list has changed from release to release.

DB2 editions can be extended to allow more data sources. Do this by installing InfoSphere Federation Server with the DB2 install.

DB2 9.7 editions and the data sources they can federate

Data Source Allowed

Express-C

Express

Workgroup

ESE

Advanced ESE

IBM DEDE

Comments

DB2 LUW

InfoSphere Warehouse

DB2 z/OS and i

Requires a licensed copy of DB2 Connect in addition to the DB2 edition.

I get a lot of questions about which tools work with IBM's data federation technology. Many people forget that IBM data federation is based on DB2 technology and any DB2 client app can be used with it. For example, if you need SOA for data virtualization, you can use IBM's InfoSphere Information Services Director with IBM's data federation to provide reusable services for SOA. The list goes on.

What about third-party tools (i.e., something not from IBM)? You can create reports in Microsoft Excel going through an ODBC driver. Better yet, one of my favorite is examples is accessing virtualized data from an iPad. For example, in the screen shot below (click to enlarge), I've used impathic's DB2 Mobile Database Client to access virtualized data and feed it into an Apple Numbers speadsheet. This took no special coding. All I had to do was (1) purchase and install the apps, (2) point impathic's client to my federation server, and (3) issue a query. From an iOS perspective, this means that IBM's data federation can help you get data for everything from DB2 and IMS on the mainframe to Oracle and Teradata appliances.

IBM's Federation Server has an interface to web services that lets you federate and query web services using SQL. IBM announcement letter 209-134 shows that this Federation Server capability was included in the following DB2 LUW 9.7 editions at no additional cost:

Enterprise Server Edition

Workgroup Server Edition

Express Edition

DB2 Advanced ESE (AESE) also has this capability builds on top of DB2 ESE. However, as per the announcement letter, web services federation is not included in Express-C or Personal Edition.

With any data replication technology, you may some day find that you need to replicate to a target database not currently supported by your replication technology. The good new is IBM's data federation technology can provide an easy way to extend your solution. For example, federation has long been used to extend IBM's SQL Replication so that it can replicate to PostgreSQL and other databases. Federation also can provide the same benefit for a number of other replication technologies, including InfoSphere Change Data Capture. I'm going to use this post to talk about considerations from a federation perspective. However, I'm not going to into details from a CDC perspective. I recommend you talk to a CDC expert for that. For example, if you have a specific question, the developerWorks CDC message board may be the right place.

My Tips

It's a short list, but, if I find more later, I'll add them.

You will most likely need to configure one of two federation interfaces - the ODBC wrapper or the JDBC wrapper.

Why? CDC already supports a wide range of databases, so you'll probably be targeting one for which IBM federation doesn't have a named wrapper. For example, it doesn't have a "PostgreSQL wrapper."

If the replication technology has its own tables that are written to in the same unit of work as target table nicknames, consider creating that table in the target database and then create a nickname for that table.

For example, I believe you'll need to do this for CDC's TS_BOOKMARK table.

If you do not do this, you will need to use 2-phase commit, which may not be available with a given target or may hurt performance.

If you create nicknames for multiple target databases, have the replication technology process different target databases in different units of work.

For example, create each CDC subscription so that it writes to nicknames for only for one target database within that subscription.

Otherwise, you hit the 2-phase commit issue mentioned above.

If you need to write to both local tables in the federated database and nicknames, put them in separate units of work.

For example, that would mean having either local tables or nicknames in a CDC subscription, but not both.

Otherwise, we're back to the 2-phase commit issue.

If you have questions, you can ask them in the comment section of this blog or use the message boards here on developerWorks.

The only DB2 edition missing this federation function today is DB2 Express-C (and I have no idea why it's been omitted). Of course, you may be wondering what 'homogeneous' federation has to do with Informix if we're talking about DB2 editions :) The answer can be found in DB2 9.1's announcement letter. In it, IBM defined homogeneous federation as being federation of it's two relational database families at that time:

"IBM Homogeneous Federation Feature allows you to manage and access DB2 and Informix data stored on remote data servers as local tables within your DB2 server."

In the 9.5 time frame, an IBM annoucement letter said that this function would be integrated into the DB2 editions available at that time. The relevant text from that announcement is:

To allow for easier data sharing between multiple IBM data servers the Homogeneous Federation feature is being included with all editions of DB2.

Notice it says "IBM data servers" and not "DB2 servers." The only catch is that it isn't installed by default. You must go through the DB2 install's custom install path to get it. For example, here's what I see in my 9.7's custom install path:

Great :) That only leaves one question...

What About Using This Federation with SQL Replication?

SQL Replication has been built into DB2 since Version 5 in 1997. Once data federation was added to DB2, the SQL Replication administration tools (currently the Replication Center and the asnclp script processor) were updated to allow replication between DB2 and Informix. Just in case you're wondering, the DB2 license information documents do not restrict this function. For example, see the DB2 ESE 9.7 license information document.

One note about this is that, if you want to capture changes from Informix databases, SQL Replication offers only trigger-based capture mechanisms. If you need to capture changes from Informix, a much better alternative is InfoSphere Change Data Capture's (CDC's) log-based capture for Informix.