Shulou(Shulou.com)06/01 Report--

If you need to use all different databases, including select, insert, update, and delete, as if all tables are in a single database, you will be very productive. Database federation is to do this: make all tables look as if they are in the same database

So, how does database federation work?

The federator system operates on tables in the remote system federatee. Remote tables appear as virtual tables in the "Federator" database. Client applications can perform operations on virtual tables in the "Federator" database, but the real persistent storage is in the remote database.

Each federate treats the federate as another database client connection. "Federatee" is just a client request for database operations. "federator" needs client software to access each remote database. To access each federated, you need to install client software for IBM Informix ®, Sybase, Oracle, and so on.

The application program interface of the database federation is SQL. This greatly improves work efficiency compared to having to learn new interfaces. Access the remote table using the same syntax as selecting, inserting, updating, and deleting the local table. Of course, not all table operations can be performed.

Let's take a look at how to configure the federated database:

Let's prepare the preliminary work and 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, the first thing we need to do is add a catalog node--

And then cataloging the database--

Db2 catalog database REMOTEDB as REMOTEDB at node NODE1

View catalog nodes and catalog database results--

Db2 list node directory

Db2 list db directory

Show that the catalog has been successful

Next, perform the steps of creating a database federation, first creating a WRAPPER--

Note: if the name of wrapper is customized, then library should be added. Different systems have different suffixes of lib.

AIX is .a

Linux is .so

Note: db2 "create wrapper db2_wrapper library 'libdb2drda.a'" double quotes!

You can use uname-a to see what the current operating system is.

(note that you must connect to the local database before creating a wrapper:

Db2 connect to dbname)

From the above, you can see that an error has been reported, which means that the instance of the database is not enabled for the specified operation

Let's check whether the function of Federated (federation) is enabled. We only need to turn on the local federation function, and we can see that it is NO.

We open the Federated and restart the database to make it work--

Now you can see that the creation is successful--

Now start connecting to the remote database, remember to make sure that the remote system has turned off the firewall before connecting

Create a SERVER-- that connects to each other's data

$db2 "create server remotedb1svr type DB2/UDB version 10.5 wrapper" DRDA "authorization\" db2inst2\ "password\" db2inst2\ "options (NODE 'NODE01', DBNAME' REMOTEDB')"

If you report an error SQL1101N Remote database "dbname" on node "" could not be accessed

With the specified authorization id and password. SQLSTATE=08004

Attempt to modify db2 get dbm cfg | grep AUTHENTICATION

Database manager authentication (AUTHENTICATION) = SERVER

Restart the database takes effect

Create MAPPING--

$db2 "create user mapping for\" db2inst1\ "server remotedb1svr options (remote_authid 'db2inst2',remote_password' db2inst2')"

Create a federated relational table (first you need to make sure that the corresponding table in the remote library already exists in the database)--

$db2 "create nickname db2inst1.testtable for remotedb1svr.db2inst2.testtable"

By viewing the tables of the federated database locally

You can see that it can be displayed normally.

Let's test again whether the insertion operation is feasible--

The execution was successful and the data was inserted successfully.

Database federation eliminates the need to build a data Mart! There is also a premise that if the amount of query to be queried is not very large, and if the summary table can usually meet the requirements of the query, then there is no need for a data Mart, no need to create a new server, move a large amount of data, and so on. this can greatly improve work efficiency. Of course, data Marts or data warehouses are the preferred solution for busy queries that need to access the lowest level of detail.

Note: if the database instance user password (the user and password defined in the federation, or not the instance user, as long as it is a remote user) needs to be modified, the password in the federation needs to be modified as well, otherwise the federation will be invalidated. In particular, it is important to note that after the instance user password is changed, it is best to perform a restart, otherwise you will find that it does not affect if you do not change the federated user password, but once the database is restarted, the password will use the latest user password.

The ways to modify a user or password in the federation are as follows:

(modify table federated user):

Db2 "ALTER USER MAPPING FOR\" db2inst1\ "SERVER remotedb1svr OPTIONS (SET remote_authid 'db2inst2')"

(modify table federated password):

Db2 "ALTER USER MAPPING FOR\" db2inst1\ "SERVER remotedb1svr OPTIONS (SET remote_password 'db2inst2')"

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.