0

I need a solution for below:

There are Two DB2 instances with two authorization ID on the same server and can be access using 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 one need 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 perform below join operational on the tables, in which one table belongs to INST1DB and 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 me how to connect the two instances and and perform join on tables.

Thanks

0

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.

| improve this answer | |
  • I can't use this feature as, DBA is not allowing this approach. does it have have impact on system performance? Also, is there any other way to do this? – Apeksha Sep 13 '19 at 3:27
  • Do you mean overall performance? I don't think so, but the only way to find out is to test it. Other ways to do it, well some kind of replication from db_b to db_a would do. – Lennart Sep 13 '19 at 5:46
  • Hi Lennart, Thanks for your response. Is this(Federation) way possible when we have 2 DB's in DB2 itself, on same server but with diffrerent authorisation ID. I have gone through Federation Documentation but couldn't find anything for DB2 to DB2 Server connectivity. Everything is related to other Databases like Informix Microsoft SQL Server Oracle Sybase Teradata. ibm.com/support/knowledgecenter/SSEPGG_10.5.0/… – Apeksha Sep 16 '19 at 2:10
  • Yes, federation is possible in your case. You use a user mapping – Lennart Sep 16 '19 at 2:52
  • See the link in my answer, it is a step by step instruction – Lennart Sep 16 '19 at 2:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.