remote access on the same instance

[Terry]

Id like to connect to a table in a different database in the same instance.

Create database db1
Create database db2

Connect to db1....

Create table test1 (
Name varchar(10),
age INT)

connect to db2...

Create table test2 (
Name varchar(10),
age INT)
While connected to db2 how do I uses table test1?

Thanks

[Knut Stolze]

Terry wrote:

Id like to connect to a table in a different database in the same
instance.

Create database db1
Create database db2

Connect to db1....

Create table test1 (
Name varchar(10),
age INT)

connect to db2...

Create table test2 (
Name varchar(10),
age INT)
While connected to db2 how do I uses table test1?

Use the federated facilities and create a nickname in db2 that refers to
table test1.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Terry]

Im not having a great deal of luck with that. I have...

Inst1.db1.test1

Inst1.db2

Inst2.db2

If Im connected to Inst2.db2 and try a federated connection to
Inst1.db1

catalog tcpip node <node> remote <server> server <service>
catalog database db1 as db1 at node <node>
create wrapper DRDA
create server <server> type DB2/EEE version 7.1 wrapper DRDA
authorization <inst1 user> password <inst1 pwd> options(add node
<node> , add dbname 'db1')
create user mapping for <inst2 user> server <server> options
(remote_authid <inst1 user>, remote_password <inst1 pwd>)
create nickname db1.test1 for <server>.db1.test1

This works fine.

If I try the same thing from inst1.db2 it fails.

catalog tcpip node <node> remote <server> server <service>
catalog database db1 as db1 at node <node>
create wrapper DRDA
create server <server> type DB2/EEE version 7.1 wrapper DRDA
authorization <inst1 user> password <inst1 pwd> options(add node
<node> , add dbname 'db1')
create user mapping for <inst1 user> server <server> options
(remote_authid <inst1 user>, remote_password <inst1 pwd>)
create nickname db1.test for <server>.db1.test1

SQL1101N Remote database <db1> on node <node> could not be accessed
with the specified authorization id and password.

Im confused as the usr/pwd was correct when connecting from a
different instance. Any thoughts

Thanks

Terry
Knut Stolze <st****@de.ibm.com> wrote in message news:<ch**********@fsuj29.rz.uni-jena.de>...

Terry wrote:

Id like to connect to a table in a different database in the same
instance.

Create database db1
Create database db2

Connect to db1....

Create table test1 (
Name varchar(10),
age INT)

connect to db2...

Create table test2 (
Name varchar(10),
age INT)
While connected to db2 how do I uses table test1?

Use the federated facilities and create a nickname in db2 that refers to
table test1.

[Knut Stolze]

Terry wrote:

Im not having a great deal of luck with that. I have...

Inst1.db1.test1

Inst1.db2

Inst2.db2

If Im connected to Inst2.db2 and try a federated connection to
Inst1.db1

catalog tcpip node <node> remote <server> server <service>
catalog database db1 as db1 at node <node>
You don't need these two steps. You already specify the necessary
information in the CREATE SERVER statement below. Just use the name of the
instance as "node" for the server options.
create wrapper DRDA
create server <server> type DB2/EEE version 7.1 wrapper DRDA
authorization <inst1 user> password <inst1 pwd> options(add node
<node> , add dbname 'db1')
create user mapping for <inst2 user> server <server> options
(remote_authid <inst1 user>, remote_password <inst1 pwd>)
create nickname db1.test1 for <server>.db1.test1

This works fine.

If I try the same thing from inst1.db2 it fails.

catalog tcpip node <node> remote <server> server <service>
catalog database db1 as db1 at node <node>
Again, you don't need the above two lines.
create wrapper DRDA
create server <server> type DB2/EEE version 7.1 wrapper DRDA
authorization <inst1 user> password <inst1 pwd> options(add node
<node> , add dbname 'db1')
create user mapping for <inst1 user> server <server> options
(remote_authid <inst1 user>, remote_password <inst1 pwd>)
create nickname db1.test for <server>.db1.test1

SQL1101N Remote database <db1> on node <node> could not be accessed
with the specified authorization id and password.

Im confused as the usr/pwd was correct when connecting from a
different instance. Any thoughts

I'd guess that you have a problem with the node/db/instance entries that you
used. Without knowing the exact values and user names, I have no idea why
it failed, though.

Here is what works quite well for me all the time:

--------------------------------------------------------------------
CREATE WRAPPER drda OPTIONS(db2_fenced 'Y');
CREATE SERVER db2 TYPE db2/aix VERSION 8 WRAPPER drda
AUTHORIZATION "<user>" PASSWORD "<password>"
OPTIONS (node 'db2inst1', dbname 'remote');
CREATE USER MAPPING FOR user SERVER db2
OPTIONS (remote_authid '<user>', remote_password '<password>');
--------------------------------------------------------------------

--
Knut Stolze
Information Integration
IBM Germany / University of Jena