Set path to data server drive cfg file (db2dsdriver.cfg) :

db2set DB2DSDRIVER_CFG_PATH=<PATH>

Export current DS catalog into cfg :

db2dsdcfgfill -i <INSTANCE> -o </OUTPUT>

Export/import client-to-server configuration :

db2cfexp
db2cfimp

Check cataloged nodes :

db2 list node directory

Catalog remote node :

db2 catalog tcpip node <NODENAME> remote <HOSTNAME> server <PORT/SERVICE>

Uncatalog remote node :

db2 uncatalog node <NODENAME>

Catalog remote database :

db2 catalog db <REMOTEDB> [as <ALIAS>] at node <NODENAME>

Uncatalog remote database :

db2 uncatalog db <REMOTEDB|ALIAS>

Windows ODBC :

db2cli writecfg add -dsn <DSNNAME> -database <DBNAME> -host <HOSTNAME/IP> -port <PORT/SERVICE>
db2cli registerdsn -add -dsn <DSNNAME> -system
db2cli validate -dsn <DSNNAME> -connect -user <USER> -passwd <PASSWORD>

Create a wrapper

db2 "CREATE WRAPPER "DRDA" LIBRARY 'libdb2drda.so' OPTIONS (DB2_FENCED 'N')";
<sxh>

**Create a server definition**

<sxh bash>
db2 "CREATE SERVER APAAIRDB TYPE DB2/UDB VERSION '10.5' WRAPPER DRDA AUTHORIZATION \"apainst1\" PASSWORD \"passw0rd\" OPTIONS (DBNAME 'APAAIR')";

Create a user mapping

db2 "CREATE USER MAPPING FOR apainst1 SERVER "APAAIRDB" OPTIONS(REMOTE_AUTHID 'apainst1' ,REMOTE_PASSWORD 'passw0rd')"

Create a table mapping

db2 "create nickname apainst1.scr for APAAIRDB.AIR.scr";

List User mapping & options :

db2 "select varchar(authid,20) USER, varchar(servername,30) server, varchar(option,30) option, varchar(setting,50) setting from syscat.useroptions with ur"

List Wrappers :

db2 "select varchar(WRAPNAME,20) as WRAPNAME, WRAPTYPE, WRAPVERSION, varchar(LIBRARY,20) as LIBRARY from syscat.wrappers with ur"

List Servers :

db2 "select varchar(WRAPNAME,20) WRAPNAME, varchar(SERVERNAME,20) as SERVERNAME, substr(SERVERTYPE,1,10) as SERVERTYPE, substr(SERVERVERSION,1,5) as SERVERVERSION from syscat.servers with ur"

List Nicknames :

db2 "select varchar(servername,20) SERVER, varchar(tabschema,20) schema,varchar(tabname,40) nickname,status,varchar(remote_schema,30) R_SCHEMA,varchar(remote_table,40) R_TABLE,remote_type R_TYPE from syscat.nicknames order by 1 with ur"

Drop user mapping

[apainst1@appid01-tj ~]$ db2 "drop user mapping for apainst1 server "APAAIRDB""
DB20000I  The SQL command completed successfully.

Drop a server definition

apainst1@appid01-tj ~]$ db2 "drop server APAAIRDB"
DB20000I  The SQL command completed successfully.
[apainst1@appid01-tj ~]$ 

Setup client connection : /path/to/network/admin/tnsnames.ora :

GENCSCT_new =
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.172.225)
      (PORT=1523)
    )
    (CONNECT_DATA=
      (SID=GENCSCT)
    )
  )

1. Enable instance for federation :

db2 update dbm cfg using federated yes

2. Create or edit ~/sqllib/cfg/db2dj.ini to point to ORACLE_HOME of Oracle client :

ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1

3. Edit DB2 user's profile to export ORACLE_HOME and DB2LIBPATH :

export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1
export DB2LIBPATH=$ORACLE_HOME/lib:$DB2LIBPATH:$DB2LIB

4. Reload the profile and bounce the instance :

. ~/.bashrc or ~/.profile

db2stop
db2start

5. Create the Oracle predefined NET8 wrapper :

db2 connect to <DB>
db2 "create wrapper NET8 library 'libdb2net8.so'"

6. Create the server federation to data source :

db2 "CREATE SERVER <SERVER> TYPE oracle VERSION '11g' WRAPPER NET8 OPTIONS (NODE '<NODE FROM TNSNAMES.ORA>')"

7. Define user mapping :

db2 "CREATE USER MAPPING FOR <USER or PUBLIC> SERVER <SERVER> OPTIONS (REMOTE_AUTHID '<remote_username>', REMOTE_PASSWORD '<remote_password>')"

8. Create nicknames to access remote tables :

db2 "CREATE NICKNAME <SCHEMA>.<NICKNAME> FOR <SERVER>.<REMOTE_SCHEMA>.<REMOTE_TABLE";

Create index specification on nickname :

db2 "CREATE [UNIQUE] INDEX <INDEX NAME> ON <NICKNAME> (<COLUMNS>) SPECIFICATION ONLY"

Collecting statistics on federated objects :

db2 "CALL SYSPROC.NNSTAT(
                    SERVER           VARCHAR(128)
                    SCHEMA           VARCHAR(128)
                    NICKNAME         VARCHAR(128)
                    COLNAMES         CLOB(2M)
                    INDEXNAMES       CLOB(2M)
                    METHOD           SMALLINT
                    LOG_FILE_PATH    VARCHAR(1000)
                    OUT_TRACE        VARCHAR(2000)
                    )"

where METHOD :

• 0 → DEFAULT, goes to 1 (Catalog-based statistics collection).
• 1 → Catalog-based statistics collection.
• 2 → Data-based statistics collection

Collect statistics for all nicknames of defined server :

db2 "CALL SYSPROC.NNSTAT('<SERVER>',NULL,NULL,NULL,NULL,0,NULL,?)"

Check statistics collection of federated nicknames :

db2 "select * from sysproc.fed_stats"