Data virtualization (federation)

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 Db2 on Cloud and Db2 Warehouse on Cloud 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 Db2 Warehouse on Cloud databases to increase the database capacity beyond the current limits of the Flex plan.

On the Db2 Warehouse on Cloud target machine

Host name: targetdotcom

1. Create a table testdata in schema admin2.

2. From the Db2 Warehouse on Cloud 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 Db2 Warehouse on Cloud 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