DB2 Remote nodes/databases
Data Server Driver
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>
Client connections
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>
DB2 Federation configuration
DB2 data source
Configure DB2 Federated tables
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";
Check DB2 Federation
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"
Delete DB2 Federation
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 ~]$
Oracle data source
Configure Oracle local net service in Client
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) ) )
Setup federation in DB2
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";
Federated objects
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"