IBM Cloud Docs
Data virtualization (federation)

Data virtualization (federation)

Db2 data virtualization (also known as federation) is supported by IBM Db2 Warehouse SaaS. Data virtualization gives you single-query access to all of your data that is on multiple distributed databases anywhere in your organization. You can access data that is on any of your Db2 or Informix data sources, both in the cloud and on premises.

This function is supported on all versions of IBM Db2 Warehouse SaaS.

Use Cases

Consolidate data sources

By federating your data sources that are located both in the cloud and on premises anywhere in your organization, your virtualized data appears to be retrieved from a single source. Data virtualization eliminates the burdensome and costly data migration process, giving you the ability to analyze all of your data efficiently and cost effectively.

Attach to Db2 on Cloud

Users of products in the Db2 family can federate data from IBM Db2 SaaS and IBM Db2 Warehouse SaaS databases. From a common interface to access the data, you can easily add, query, and analyze your data without complex ETL processes and without any additional code.

Increase database capacity beyond fixed limits

Federation gives you the ability to increase the capacity of an on-premises database by federating with a database on the cloud. Data virtualization in this case is a great option if your on-premises database is running out of storage. Increasing the capacity of a database with federation is useful for new development because developers don't need to change a database already in production. You can also federate between two IBM Db2 Warehouse SaaS databases to increase the database capacity beyond the current limits of the Flex plan.

Getting started

The following steps are an example of how you go about federating your disparate data sources to appear as if the data is retrieved from a single source. The following example illustrates the federation of two IBM Db2 Warehouse SaaS databases:

On the Db2 Warehouse on Cloud target machine

Host name: targetdotcom

  1. Create a table testdata in schema admin2.

  2. From the IBM Db2 Warehouse SaaS console, load the testdata table with data as user admin2 with password YYYY.

On a Db2 Warehouse on Cloud machine being used as a federation source

From the IBM Db2 Warehouse SaaS console:

  1. Create a server to talk to the target machine:
    create server <server_name> type dashdb version 11 wrapper drda authorization "<admin_user_on_target>" password "<admin_password_on_target>" options (host '<target_host_name>', port '50000', dbname 'bludb')

    For example:
    create server db2server type dashdb version 11 wrapper drda authorization "admin2" password "YYYY" options (host 'targetdotcom', port '50000', dbname 'bludb')

  2. Create the user mapping for admin2:
    create user mapping for <admin_user> server db2server options (remote_authid '<admin_user_on_target>', remote_password '<admin_password_on_target>')

    For example:
    create user mapping for admin1 server db2server options (remote_authid 'admin2', remote_password 'YYYY')

  3. Create a nickname for the database:
    create nickname <nickname> for <server_name>.<schema_name>.<table_name>

    For example:
    create nickname ntest1 for db2server.admin2.testdata

  4. Test that you can pull data from the target server:
    select * from <nickname>

    For example:
    select * from ntest1

Additional information

For more information about data virtualization (federation), see: Federation.

For information about the data sources supported by federation, see: Federation Supported Data Sources.