A federated query of two tables between two databases in DB2

Hello, everyone, today I met a federated query of two tables between two databases in DB2

I know there are dblink in Oracle, but I don't know what to do with the two database federated queries of DB2.
I looked for something similar to
For example, two databases: DB1,DB2
User name
Password
SELECT * from DB1. User name. password, DB2. Username. password where db1. Nm=db2. NM
But it's not good to use.
Thank you for your help.

DB2 has a federal database, you can check it out.

1. To see the directory database, use:
DB2 List DB Directory
This information should be placed in the system table. (It is neither a registry.) It is not a document, please check it specifically. I don't know (analogy to Oracle's guess).


2.oracle has dblink. There is a transparent gateway.
DB2 has a federated database: (if different databases remember that drivers are DB2, it is not necessary.) ）

Using the DB2 federated database, you can use an SQL statement to query data from multiple data sources. Amazingly, these data sources can be diverse: they can be multiple relational database systems or non-relational database systems, such as Microsoft Excel
, XML, and so on. This article will first explore federated access between multiple relational databases.
Why don't we start by doing this? Configuring a simple federated database instance to nurture your sense of accomplishment gives you a bit of confidence in understanding the concepts that federated databases are designed and a wide range of parameter options. I've always liked this style of writing, and I'm sure a lot of people will like it.
To perform the following experiment, you must first install DB2 v8.2 ESE (Enterprise Server Edition) for Windows. The installation of DB2 is not covered by this article.
If your DB2 is already installed, let's start.
1) Users logged in to Windows with Windows Administrator privileges, the author uses administrator.
2) in order to complete the following demo, you also need another Windows user, the author uses the Db2admin user which automatically creates when installs the DB2. You can of course use other Windows users as well.
3) Make sure that you have created at least one available instance. The author uses the DB2 instance created by default. You can start this instance by running the Db2start command in the command window
4) Run the DB2 GET DBM cfg in the DB2 Command window to view the configuration parameters of the current instance and ensure that the federated parameter is set to Yes, you can set this parameter by running the DB2 UPDATE dbm CFG using federated Yes. After modifying this parameter, the instance must be restarted before it takes effect (Db2stop/db2start)
5) But you have created the DB2 sample database sample, if you have not yet created it, you can create it by running the Db2sampl command in the DB2 Command Line window. This sample database contains some sample tables and data, and using this database as a demonstration can save us a little time.
6) Create another database, the author uses the name TestDB:
DB2 CREATE DB TESTDB
7) Run the DB2CE command to open the DB2 command editor and complete the following steps here.
8) Connect to the sample database and create the Drda wrapper (WRAPPER).
CONNECT to SAMPLE;
CREATE WRAPPER DRDA;

9) Creating servers (server)
CREATE SERVER TESTDB TYPE db2/udb VERSION ' 8.2 ' WRAPPER "DRDA" AUTHID "Db2admin" PASSWORD "Db2admin" OPTIONS (ADD DBNAME ' TESTDB ', PASSWORD ' Y ');
10) Create User mappings
CREATE USER MAPPING for "ADMINISTRATOR" SERVER "TESTDB" OPTIONS (add Remote_authid ' db2admin ', add Remote_password ' db2a DMin ');
11) Connect to the TestDB database using the Db2admin user, create the table WorkItem, and insert the sample data.
CONNECT to TESTDB USER db2admin USING db2admin;
CREATE TABLE WorkItem (
ItemNo CHAR (4) Not NULL,
ItemName VARCHAR () not NULL,
EMPNO CHAR (6),
PRIMARY KEY (ItemNo)
);
INSERT into WorkItem (itemno,itemname,empno) VALUES (' 0001 ', ' Test program Module 1 ', ' 000010 ');
INSERT into WorkItem (itemno,itemname,empno) VALUES (' 0002 ', ' Completion of Module 2 ' development ', ' 000020 ');
INSERT into WorkItem (itemno,itemname,empno) VALUES (' 0003 ', ' contact customer a ', ' 000030 ');
12) Connect to the sample database and create a nickname for the WorkItem table in the TestDB database:
CONNECT to SAMPLE;
CREATE nickname ADMINISTRATOR. WorkItem for TESTDB. Db2admin. WorkItem;
OK, now we can use the WorkItem table in the TestDB database in the sample database, and can also query with the employee table of the sample database:
SELECT * from WorkItem;
Let's find out who is responsible for which work item:
SELECT A.itemno,a.itemname,a.empno,b.firstnme,b.lastname from WorkItem A to OUTER JOIN EMPLOYEE B on A.empno=b.empno;

So far, the sample database has become a federated database, and you can visit them by creating nicknames for the tables or views in the TestDB database in the sample database.

This article transferred from: http://www.cnblogs.com/zmc/p/4180636.html

A federated query of two tables between two databases in DB2