Tuesday, October 13, 2009

Basic db2 federation setup

Db2 federation can be used to retrieve information from non-db2 sources like oracle,sql server.It also can be used with db2 datasources.Setting up federation can be confusing at times.Below is the basic procedure that can be used to setup federated system:

1)Enable federation: Federation can be enabled by running the following command

update dbm cfg using federated yes immediate;

2)Create wrapper:Below is the command you would issue for a db2 datasource.Wrappers differs for each RDBMS.Please refer to IBM documentation to get more information on wrappers

create wrapper DRDA;

3)Catalog the datasource information: Datasource should be cataloged properly and the federated server uses the access method depending on the datasource type.

4)Create a server definition.Refer to the below example for db2/udb datasource

db2 "create server SAMPLEHOST

type DB2/UDB

version 9.5 wrapper drda

authorization 'user1'

password '*****'

options(node 'SAMPLENODE', dbname 'SAMPLTST')"

5)Create user mapping:

db2 "CREATE USER MAPPING FOR federuser server SAMPLEHOST options(remote_authid 'user1',REMOTE_PASSWORD '*****')"

6)Create nickname:

CREATE NICKNAME EMP FOR SAMPLEHOST.SAMPLTST.EMP;

If you follow all the above steps, basic federation setup is done.Nicknames can be on tables or views that reside in the datasource.Once the federation is setup, nicknames can be used to refer to the actual datasource objects.If some part of the code can not be processed by the data source, it is not passed to the data source.The datasource in this case will be using alternate functionality that is close or the data set will be sent to the federated server for additional processing

No comments: