DB2 LUW Federation between 2 databases within same instance

The objective of this post is to establish communication between two databases within SAME INSTANCE. For example: I have two databases – FEDDB1, FEDDB2 – within db2inst1. The below setup talks about accessing T1 table in FEDDB1 from FEDDB2 database.

Step1: Enable Data Federation at instance level

$ db2 update dbm cfg using FEDERATED YES
DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

$ db2stop
07/29/2016 05:33:07 0 0 SQL1025N The database manager was not stopped because databases are still active.
SQL1025N The database manager was not stopped because databases are still active.

$ db2stop force
07/29/2016 05:34:06 0 0 SQL1064N DB2STOP processing was successful.
SQL1064N DB2STOP processing was successful.

$ db2start
07/29/2016 05:34:39 0 0 SQL1063N DB2START processing was successful.
SQL1063N DB2START processing was successful.

Step2:  Create two databases (FEDDB1, FEDDB2)

$ db2 “create database FEDDB1”

$ db2 “create database FEDDB2”

$ db2 =>

db2 => connect to feddb1

db2 => create table t1(c1 char(30))

db2 => insert into t1 values (‘From FEDDB1’)

db2 => connect to FEDDB2

db2 => create wrapper drda

db2 => create server SERVER1 type db2/udb version 11.1 wrapper drda authorization “db2inst1” password “password” options (DBNAME ‘FEDDB1’)

db2 => create user mapping for db2inst1 server SERVER1 options (remote_authid ‘db2inst1’, remote_password ‘password’)

db2 => connect

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB2

db2 => select * from server1.db2inst1.t1

C1
——————————
From FEDDB1

1 record(s) selected.

db2 => create nickname DB2INST1.T1_NICK FOR SERVER1.DB2INST1.T1
DB20000I The SQL command completed successfully.

db2 => select * from t1_nick

C1
——————————
From FEDDB1

1 record(s) selected.

db2 => connect to feddb1

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB1

db2 => insert into t1 values (‘From FEDDB1: Testing again’)
DB20000I The SQL command completed successfully.
db2 => connect to feddb2

Database Connection Information

Database server = DB2/LINUXX8664 11.1.0
SQL authorization ID = DB2INST1
Local database alias = FEDDB2

db2 => select * from t1_nick

C1
——————————
From FEDDB1
From FEDDB1: Testing again

2 record(s) selected.

 

 

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: