HOW TO SETUP FEDERATION BETWEEN TWO DB2 LUW DATABASES.

-

DB2 federation system allows you to communicate one or more local/remote databases (Any Database technology - Oracle.MySQL,MS SQL etc)with in single application connection or without disconnecting existing connection.

 The following steps to configure the federation system:

1.Cataloging a Node directory

2.Cataloging a Remote Database directory

3.Create the Wrapper

4.Create the Server for a remote database

5.Create the User mapping

6.Test the connection

7.Create the nicknames

 

Configuration Information:

The following information of local (Federate) database where we need remote database entry to be created and remote table to be present:

Hostname                     :                        localhost

DB2 Version                  :                         v9.7

Instance Name              :                         db2inst1

Service/Port Number     :                       50001

Password                      :                        db2inst1

Database Name            :                        LOCALDB

Schema Name              :                         DB2INST1

Table Name                  :                        Test (where, Test nickname to be created of Remote DB)

 

The following information of Remote database where physically table present:

Hostname                     :                       remotehost

DB2 Version                  :                         v9.7

Instance Name              :                       db2inst4

Service/Port Number     :                       60012

Password                      :                       db2inst4

Database Name            :                       REMOTEDB

Schema Name              :                       DB2INST4

Table Name                  :                       T1

Before you proceed with Federation, make sure you have already been set “FEDERATED Database manager (Instance) parameter to YES” on your local host as by default is not enabled.

db2 "UPDATE DBM CFG USING FEDERATED YES"

db2stop

db2start

 1. Cataloging a Node Directory: Specifies to connect to the DB2 Remote server & instance using given hostname & Port (TCP/IP Protocol) Number or can be services name

Syntax:

db2 "CATALOG TCPIP NODE node_name REMOTE hostname SERVER service_name"

Where, hostname you may give either hostname or IP Address of your remote host. Make sure local server can able to reach the remote server.

service_name you may give either service_name or Port number of your remote host. Make sure local server can able to telnet the remote server.

db2 catalog tcpip node localnode REMOTE remotehost server 60012

DB20000I  The CATALOG TCPIP NODE command completed successfully.

Execute LIST NODE DIRECTORY to view Node Directory entry:

$ db2 list node directory

 Node Directory

 Number of entries in the directory = 1

 Node 1 entry:

 Node name                      =        localnode

Comment                        =

Directory entry type        =         LOCAL

Protocol                         =          TCPIP

Hostname                      =         remotehost

Service name                =          60000

 

2. Cataloging a Remote Database directory: Specifies to connect to the DB2 Remote Database & Authentication mode

Syntax:

db2 “Catalog Database remote_db_name as Alias_name at Node node_name Authentication auth_mode"

If Remote database name has more than 8 characters then you should configure DCS (Database Connection Services) directory.

Syntax:

db2 "CATALOG DCS DATABASE remote_db_name AS alias_name"

(Execute LIST DCS DIRECTORY command to view DCS Directory entry)

 db2 CATALOG DB REMOTEDB AS RMTDB AT NODE localnode

DB20000I  The CATALOG DATABASE command completed successfully.

Execute LIST DB DIRECTORY to view Remote Database entry:

db2 list db directory

System Database Directory

Number of entries in the directory = 2

Database 1 entry:

Database alias                       =              localnode

Database name                       =            sample1

Node name                             =             localnode

Database release level            =            14.00

Comment                                =

Directory entry type                 =            Remote

Catalog database partition number    = -1

Alternate server hostname        =

Alternate server port number     =

Database 2 entry:

Database alias                       =              LOCALDB

Database name                      =             LOCALDB

Local database directory           =           /home/db2inst1

Database release level             =           14.00

Comment                                 =

Directory entry type                 =            Indirect

Catalog database partition number    = 0

Alternate server hostname       =

Alternate server port number    =

 

3. Create the Wrapper: Allows you to communicate & fetch data from Remote Database. Default WRAPPER name is “DRDA” (Distributed Relational Database Architecture)

Syntax:

db2 "CREATE WRAPPER DRDA"

If DRDA wrapper does exist in your environment then give different name of WRAPPER but you have to specify LIBRARY name by adding LIBRARY parameter at the end.

Syntax:

db2 "CREATE WRAPPER DRDA1 LIBRARY library_name"

To find LIBRARY name, Go to ~/sqllib/lib & search “libdb2drda.*“ file name.

If you’re working on AIX platform then LIBRARY Name would be libdb2drda.a & on Linux platform LIBRARY Name would be libdb2drda.so.

db2 CREATE WRAPPER DRDA

DB20000I  The SQL command completed successfully.

 

4. Create the Server for a remote database: Here, you have to specify authentication details to connect with Remote database and it will not store in the global catalog system

Syntax:
db2 “create server server_name Type DB2/UDB VERSION Db2_version_name WRAPPER wrapper_name AUTHORIZATION “user_id” password “password” OPTIONS(DBNAME ‘Cataloged_db_name’)

 

Where, DB2/UDB is for DB2 LUW Server & cataloged_db_name is alias name which you gave while cataloging database at RMTNODE node (db2 CATALOG DB REMOTEDB AS RMTDB AT NODE localnode)

First Method:

db2  CREATE SERVER fed_server TYPE DB2/UDB VERSION 9.7 WRAPPER DRDA authorization "db2inst1" PASSWORD "db2inst1" OPTIONS (DBNAME 'RMTDB')

DB20000I  The SQL command completed successfully.

5. Create the User mapping: To establish trusted relationship between local & remote Database system. User Mapping does not required if same credentials (User ID & Password) between local & remote database servers

ALTER USER MAPPING command can be used to change credentials in future if required.

db2 "ALTER USER MAPPING FOR <userid> SERVER server_name OPTIONS (SET REMOTE_AUTHID 'user_id')"

db2 "ALTER USER MAPPING FOR <userid> SERVER server_name OPTIONS (SET REMOTE_PASSWORD 'password')"

Syntax:

db2 "CREATE USER MAPPING FOR <user_id> SERVER <server_name> OPTIONS (REMOTE_AUTHID '<user_id>', REMOTE_PASSWORD '<password>')"

db2 "CREATE USER MAPPING FOR db2inst1 SERVER fed_server OPTIONS (REMOTE_AUTHID 'db2inst1', REMOTE_PASSWORD 'db2inst1')"

DB20000I  The SQL command completed successfully.

Note, you need note required user to be present on Remote database server for federation if you wish to use same local credentials on Remote database, but make sure you have CONNECT & SELECT TABLE Privilege on Remote database.

6. Test the connection: To test connection between Local to Remote Database

Syntax:

db2 "SET PASSTHRU <server_name>"

db2 "SET PASSTHRU RESET"

$ db2 "set passthru fed_server"

DB20000I  The SQL command completed successfully.

$ db2 "set passthru RESET"

DB20000I  The SQL command completed successfully.

 

7. Create the nicknames: A collection of remote database model as a table is called a Nickname. You may choose same Remote schema name & table name in Local database or you may change it as per your requirement

Syntax:

db2 "CREATE NICKNAME <schema_name>.<table_name> FOR <server_name>.<remote_schema>.<remote_table>"

db2 "CREATE NICKNAME DB2INST1.RT1 FOR fed_server.DB2INST4.Test"

DB20000I  The SQL command completed successfully.

Comments

Post a Comment

Popular posts from this blog

Db2 export command example using file format (del , ixf)

-
You want to transfer data from a db2 database table to another db2 database or within the same database, one of the way is to use db2 export / import command. Using db2 export / import you can move your data to excel also.   DB2 export command exports data selected by SQL statement from a table or view to an external files in the format of  del - delimited , wsf - worksheet format , ixf - Integration Exchange Format,asc.   The DEL format uses delimiter and column separator and decimal point. The default string delimiter is double quote " and column separator is comma(,) and Decimal Point is dot(.), you can also specify your own string delimiter & column separator, decimal point. Another format supported by DB2 is   IXF (Integration Exchange Format). It is a generic relational database exchange format which supports an end-of-record delimiter . IXF architecture supports to exchange of relational database structures and data. IXF is used for transferring data ...
Read more

How to fix DB2 Tablespace OFFLINE state issue?

-
Sometimes we face an error where one (or more) of our db2 tablespaces goes offline caused by loads, DB crashes, abnormal db2 services/or server shutdown, etc. So in this blogpost let us see how can we check which tablespace is in OFFLINE state and bring back tablespace’s state to ONLINE.   Finding TableSpace State To check which tablespace is OFFLINE, execute the below command: SYNTAX: db2 connect to <database_name> db2 list tablespaces |egrep -i "ID|Name|State" db2 list tablespaces show detail   If u see Tablespace state with 0x4000, then that tablespace is in Offline state There are 2 methods to resolve the tablespace from offline to online as below. Method1: We can execute below DB2 commands to take off tablespace's OFFLINE state.   db2 terminate db2 force application all db2 connect to <database_name>   Method 2:  Perform ALTER Tablespace command to bring the tablespace up while the rest of the database is still up...
Read more

How to determine fenced User-ID of a DB2 instance

-
When you create a DB2 instance in Unix, you have the option of specifying a fenced user-id different than instance owner. The purpose of fenced user id is to protect DB2 from untested or malicious code from the external stored procedures, user defined functions etc. When you try to run these external procedures, they run in a separate address space owned by the fenced user id.   You know about the fenced user id if you created a DB2 instance yourself on your Unix platform. But suppose, you are looking at a DB2 instance created by someone and you are wondering as which is the fenced user id, you can look at the user and group id of the file .fenced in ~/sqllib/ctrl directory and this will tell you as which is the fenced user id. For Eg:/home/db2inst1/sqllib/ctrl/.fencedID It will display the fenced user id for the instance db2inst1   Another method to find the fenced user id is   db2pd -fmp  
Read more