I'm having a problem setting up the connection from a federated system to another federated system. What makes this particularly frustrating is that I have gotten it to work in the past...

I have 3 machines I'm working with:

Logger: XPPro, db2express C, running the 'log' database
Test: Server2003 sp1, db2express C, running as a server
Prod: Server2003 sp2, db2express C, running as a server

The idea is that there are certain tables in the Test (or Prod) database that we want to audit and write the audit trail to the Logger.

The Test server has an instance that is federated, and works. By that, I mean in the federated objects, there is a DRDA wrapper, and a Server and 3 nicknamed tables that are mapped to tables that reside on the Logger. I've been trying to set the exact same thing up using the Prod server (have it map tables residing on the Logger.) However, it isn't working and other than the service pack difference in the OS, the environments are the same. Since one machine is able to map tables from the Logger, I'm assuming the problem is not with the Logger.

Here are the exact commands I've been trying:

>db2 catalog tcpip node BBSYS remote bacatx-320 server 50015
DB20000I The CATALOG TCPIP NODE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.

>> Rebooted the machine

>db2 attach to bbsys user rvsdban - attach to new node

>db2 catalog database RVSDB as FEDDB at node BBSYS
DB20000I The CATALOG DATABASE command completed successfully.
DB21056W Directory changes may not be effective until the directory cache is refreshed.

At this point, in Control Center, I can see the tables on the Logging (remote) server. Now I attach/connect to the local instance where I want the mapping:

>db2 attach to u29prvs1 user rvsdban

>db2 connect to rvsdb1 user rvsdban


>db2 create wrapper drda
DB20000I The SQL command completed successfully.

Now, here's where things fall apart:

>db2 create server FEDLGSRV type DB2/UDB version 9.1 wrapper DRDA authorization rvsdban password "***" options (dbname 'FEDDB')
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL30061N The database alias or database name "FEDDB" was not found at the remote node. SQLSTATE=08004

It doesn't matter what password I put in, so it is not authenticating. Any help at all would be greatly appreciated.

Just curious, check the DBM parameter FEDERATED on the server where you are having problems.

Andy

This is for the instance that resides on the machine I'm running the commands on.


C:\Program Files\IBM\SQLLIB\BIN>db2 get database manager configuration

Database Manager Configuration
Node type = Database Server with local and remote clients

Database manager configuration release level = 0x0b00
Maximum total of files open (MAXTOTFILOP) = 16000
CPU speed (millisec/instruction) (CPUSPEED) = 3.070238e-007
Max number of concurrently active databases (NUMDB) = 8
Federated Database System Support (FEDERATED) = YES

At this instance, do you see the "FEDDB" when you do "db2 list db directory"?

No, it didn't, so what I've done is ran all 4 commands after I attached to the local instance. Now when I'm attached to the local instance, it is listing both db's.

C:\Program Files\IBM\SQLLIB\BIN>db2 attach to u29prvs1 user rvs_dba
Enter current password for rvs_dba:

Instance Attachment Information

Instance server = DB2/NT 9.1.0
Authorization ID = RVS_DBA
Local instance alias = U29PRVS1


C:\Program Files\IBM\SQLLIB\BIN>db2 list db directory

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias = FEDDB
Database name = RVSDB
Node name = BBSYS
Database release level = b.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 2 entry:

Database alias = RVSDB1
Database name = RVSDB1
Node name = U29PRVS1
Database release level = b.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

But if I then connect to the local database and run the same create server command, I still get that error.

I really appreciate your help on this, by the way.

I think you still have a directory problem. The "list DB directory" does not show the "local" DB, so I highly suspect you have something wrong. Try this:

1) Goto the instance of the server you want to federate from
2) type "db2 list node directory"
3) if the remote node is not there, enter it
4) type "db2 list db directory"
5) If the remote DB is not there, enter it by just catalogging it, do not do the attach to node first.
6) try creating the server.

Andy

This morning, on the server that is running the federated instance where I want the nicknamed table to appear on, I dropped all but the actual locally running instance and database (server: dbtest01, instance: u29prvs1, database: rvsdb1). Then I rebooted just to be safe. I ran the 2 'list' commands and verified that it was only seeing the db/instance that it is hosting. I then, without attaching to any instance, did the catalog tcpip node and the catalog database and here's the result of the lists:

>db2 list node directory

Node Directory
Number of entries in the directory = 2

Node 1 entry:

Node name = BBSYS
Comment =
Directory entry type = LOCAL
Protocol = TCPIP
Hostname = BACATX-320
Service name = 50015

Node 2 entry:

Node name = U29PRVS1
Comment =
Directory entry type = LOCAL
Protocol = LOCAL
Instance name = U29PRVS1

>db2 list db directory

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias = RVSDB
Database name = RVSDB
Node name = BBSYS
Database release level = b.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

Database 2 entry:

Database alias = RVSDB1
Database name = RVSDB1
Node name = U29PRVS1
Database release level = b.00
Comment =
Directory entry type = Remote
Catalog database partition number = -1
Alternate server hostname =
Alternate server port number =

And when I try to create do the create server, I'm getting the same error.

OK, I have two more questions.

1) without doing anything else than above, can you connect to the remote DB from this instance (db2 connect to RVSDB user xxxx using yyyy)?
2) what is the exact sql you are using to create the server?

Andy

1) Yes, I can connect to it and issue queries against the tables on RVSDB.
2) Here is a direct cut and past from the server:

C:\Program Files\IBM\SQLLIB\BIN>db2 create server FEDLGSRV type DB2/UDB version 9.1 wrapper DRDA authorization rvsdban password "***" options (dbname 'RVSDB')
DB21034E The command was processed as an SQL statement because it was not avalid Command Line Processor command. During SQL processing it returnedQL30061N The database alias or database name "RVSDB" was not found at theremote node. SQLSTATE=08004

Try this command:

db2 create server FEDLGSRV type DB2/UDB version '9.1' wrapper DRDA authorization "rvsdban" password "***" options (ADD dbname 'RVSDB', PASSWORD 'Y')

Andy

Same exact error. What's killing me is that the original test machine is sitting right here and it still works :/

Can you try to use the "Control Center" gui to create the federation?

Andy

I have tried it, when I get to the step to create a server (either via the wizard or just doing the stepts) there is an option to 'Discover'. On the machine that the federation works on, if I click the discover button it lists the remote database. On the machine I'm trying to get federation working on, it doesn't find the remote database. If I specify the parameters to make the connection, it fails with the same error that I'm getting via the command line.

I think it has something to do with the way the remote instance / database is cataloged. The reason I say that is that on the server that works, when I use the Configuration Assistant, and look at the properties for the local system/instance, it actually displays the local db and an alias to the remote db. I can not get the new server to display that.

Then, on the server that works, when I got to use the GUI to create a federated server, the alias that shows up in the Config Assist shows up when I do 'Discover'. I think it's that 'mapping' or catalog that I'm not able to replicate on the new server. But for the life of me, I can't see what I'm doing different / wrong.

What is the DB2 version on each server (version and FP level)?
What is the tcpip address and port number of each instance?

Andy