DB2 federated database and configuration method

If you need to use all of the different databases, including SELECT, INSERT, update, and delete, as if all the tables are in a single database, then you will get very high productivity. Database Federation is going to do this: make all tables look like they are in the same database

So, how does the database federation work?

The Federated (federator)system operates on a table in the remote system " being federated (federatee)" . The remote table appears as a virtual table in the "Federator" database. The client application can perform operations on the virtual tables in the "Federator" database, but the true persistence store resides in the remote database.

each " Federated" sees " Confederation " as another database client connection. "Federatee" is just a client request that handles database operations. the " Federated " needs to use the client software to access each remote database. Need to install IBM Informix to access each of the federated users ? , Client software for Sybase,Oracle, and more.

the application interface of the database federation is SQL. This greatly improves productivity compared to the need to learn a new interface. Access remote tables using the same syntax as selecting, inserting, updating, and deleting local tables. Of course, it is not possible to perform all table operations.

Let's take a look at how to configure the federated database by actually doing it:

Let's start with the pre-work, create a local database and a remote database to do the experiment--

Local Database :

Database: LOCALDB

IP Address:192.168.20.138

Port number:60000

User name:db2inst1

Password:db2inst1

Remote Database :

Database: Remotedb

IP Address:192.168.20.145

Port number:70000

User name:db2inst2

Password:db2inst2

Also make sure that the global settings for the remote database are as follows--

Now, let's start by adding a catalog database--

View Catalog Results--

Show Catalog succeeded

Next, take the steps to create the federated database, first create WRAPPER--

You can see the error from the above, that is, the instance of the database is not enabled for the specified operation

We view Federated(Federated) function is turned on, only need to turn on the local federal function, see the display is NO

we will Federated Open and restart the database for it to take effect--

Now you can see that the creation was successful--

Now start connecting to the remote database, remember to make sure the remote system has shut down the firewall before connecting--

Create a connection to the data SERVER--

$ DB2 "Create server REMOTEDB1SVR type DB2/UDB version 10.5 wrapper" DRDA "authorization \" db2inst2\ "Password \" db2inst2\ "Options (NODE ' NODE01 ', DBNAME ' remotedb ')"

Create MAPPING--

$ DB2 "Create user mapping for \" db2inst1\ "Server Remotedb1svr options (remote_authid ' Db2inst2 ', Remote_password ' Db2inst 2 ') "

Create a federated Relationship table (you first need to confirm that the remote library has a corresponding table already in the database)--

$ DB2 "Create nickname Db2inst1.testtable for Remotedb1svr.db2inst2.testtable"

By viewing the tables of the federated database locally

You can see the normal display

Let's test again if the insertion is feasible--

The execution succeeds and the data is inserted successfully.

Database federation can make it no longer necessary to build a data mart! There is also a premise, that is, if the amount of query is not very large, and if the summary table can usually meet the requirements of the query, then you do not need a data mart, do not need to create a new server and move a large number of data, etc., which can greatly improve productivity. Of course, a data mart or data Warehouse is the preferred solution for busy queries that require access to the lowest level of detail.

DB2 federated database and configuration method