1

I am looking for a solution to the problem described below.

There are two DB2 instances with two authorization IDs on the same server and can be accessed using the same user User1.

Example:

To access Inst1DB, I use the following commands:

[User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst1DB

To access Inst2DB, run the following commands:

[User1 ~]$ . /db2/auth2db2/home/auth2db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst2DB

Note that one needs to terminate the current DB2 session before connecting to another DB instance. To terminate DB session, run the following command:

[User1 ~]$ db2 terminate

Issue:

I want to join two tables, one of which belongs to INST1DB and the other to INST2DB, while connected to INST1DB.

[User1 ~]$ . /db2/authID1db2/home/authID1db2/sqllib/db2profile
[User1 ~]$ db2 connect to Inst1DB
[User1 ~]$ db2 "Select * from Inst1DB.schema1.table1 T1 , Inst2DB.schema2.table2 T2 where T1.column1 = T2.column1.

Curerntly I am getting below error:

SQL0204N "Inst2DB.schema2.table2" is an undefined name. SQLSTATE=42704.

Query:

Can anyone suggest to me how to connect the two instances and perform a join on these tables?

mustaccio
  • 28,207
  • 24
  • 60
  • 76
Apeksha
  • 11
  • 1
  • 5

1 Answers1

1

One way you can achieve this is via federation. In short words, if you want to access db_b from db_a, you catalog db_b in instance of db_a. You then create nicknames in db_a for the tables in db_b that you want to access. The nicknames can with some restrictions be treated as tables, so you can join a table in db_a with a table in db_b.

Lennart - Slava Ukraini
  • 23,842
  • 3
  • 34
  • 72