Announcing Data Virtualization (Federation) for Db2 on Cloud

5 min read

Announcing Data Virtualization (Federation) for Db2 on Cloud

IBM is happy to announce Db2 Data Virtualization features for Db2 on Cloud. Sometimes called “federation,” this feature lets our customers access data from multiple different databases with a single query. With this latest addition, users will now be able to access data that is located on any of their Db2 or Informix data sources including both cloud and on premises systems. This functionality is supported on all versions of Db2 on Cloud, except for the free Lite plan. However, users can use the Lite plan as a target that you can pull data from.

Use Cases for Db2 on Cloud

Combine On Cloud and On Premise:

A company may have started their operations with an on-premises Db2 server. As cloud technology becomes more widespread and companies start to operate on cloud in a cost-effective fashion, there will be continued Cloud growth. However, the organization’s data on both sources remain as a critical component to their decision-making processes. By way of example, a client operating in retail industry needs to be able to access all data, say customer information, to run further analysis on their customers’ consumption behaviors. They need to be able to identify customers, match their records on cloud with already existing ones from an on-premises database and compose them as if the data is being retrieved from a single source. Federation capability here prevents the burdensome data migration process and allows the user to access the data without moving the data.


Attach Db2 Warehouse:

Db2 family users would now be able to federate data between Db2 on Cloud and Db2 Warehouse on Cloud. By being provided a common interface for accessing the data, a user can now easily add or query data from or to the Warehouse without complex ETL processes or any additional code.


Sharding Data Across Multiple Servers:

At times, users may choose to partition (shard). With federation capabilities, data can be queried with a unified interface and this lets the user better balance the workload, scale specific parts of an app or create microservices that work together.


Increase Capacity Beyond Fixed Limits:

By using federation, users can increase capacity of an on premises database by federating to or from the cloud. This is a great option if your on premises database is running out of storage. Increased capacity will also be useful for new development as our users no longer need to change a database in production. You can also use this feature to federate between two Db2 on Cloud databases to increase the capacity beyond the current limits of the Flex plan.

Ready to get started? It is simple

On the Target Machine


— Create a Table testdata in schema admin2 and load it with data through the console using user admin2 with password YYYY

From a Client Machine

-- catalog the target machines

db2 catalog tcpip node <node_name> remote <hostname> server 50000

e.g. db2 catalog tcpip node fedS remote server 50000


— catalog the database on fedS


db2 catalog db bludb as <db_name> at node <node_name>

e.g. db2 catalog db bludb as srcdb at node fedS


-- Connect to the database on fedS


db2 connect to <catalog_db_name> user <admin_user> using ‘<admin_password>’

e.g. db2 connect to srcdb user admin1 using ‘XXXX’



— create a wrapper on fedS


db2 "create wrapper drda"



— create a server to talk to the Target Machine


db2 "create server <server_name> type dashdb version 11 wrapper drda authorization \”<admin_user_on_target>\” password \”<admin_password_on_target>\” options (host ‘<target_hostname>’, port '50000', dbname 'bludb')"

e.g. db2 "create server db2server type dashdb version 11 wrapper drda authorization \”admin2\” password \”YYYY\” options (host ’’, port '50000', dbname 'bludb')"


— Create the User mapping for admin2


db2 "create user mapping for <admin_user> server db2server options (remote_authid ‘<admin_user_on_target>’, remote_password '<admin_password_on_target>')"

e.g. db2 "create user mapping for admin1 server db2server options (remote_authid 'admin2', remote_password 'YYYY')"


— create a nickname for the database


db2 -v "create nickname <nickname> for <server_name>.<schema_name>.<table_name>”

e.g. db2 -v "create nickname ntest1 for db2server.admin2.testdata"

On the Source Machine

— Test to make sure you can pull data from the Target Server


db2 “select * from <nickname>”

e.g. db2 “select * from ntest1”

Moving forward

With its easily scalable flex plan, free-forever Lite version, high availability and added capabilities of data virtualization, Db2 on Cloud will continue to increase its footprint in RDBMS landscape. We are working rigorously on improving every aspect of Db2 on Cloud and will be sharing other great new features for our customers very soon.


Be the first to hear about news, product updates, and innovation from IBM Cloud