db2_connect [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
====== DB2 Remote nodes/databases ======
===== Data Server Driver =====
**Set path to data server drive cfg file (db2dsdriver.cfg) :**
<sxh bash>
db2set DB2DSDRIVER_CFG_PATH=<PATH>
</sxh>

**Export current DS catalog into cfg :**
<sxh bash>
db2dsdcfgfill -i <INSTANCE> -o </OUTPUT>
</sxh>

===== Client connections =====

**Export/import client-to-server configuration : **
<sxh bash>
db2cfexp
db2cfimp
</sxh>

**Check cataloged nodes :**
<sxh bash>
db2 list node directory
</sxh>

**Catalog remote node :**
<sxh bash>
db2 catalog tcpip node <NODENAME> remote <HOSTNAME> server <PORT/SERVICE>
</sxh>

**Uncatalog remote node :**
<sxh bash>
db2 uncatalog node <NODENAME>
</sxh>

**Catalog remote database :**
<sxh bash>
db2 catalog db <REMOTEDB> [as <ALIAS>] at node <NODENAME>
</sxh>

**Uncatalog remote database :**
<sxh bash>
db2 uncatalog db <REMOTEDB|ALIAS>
</sxh>

**Windows ODBC :**
<sxh bash>
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>
</sxh>

----

====== DB2 Federation configuration ======

===== DB2 data source =====

==== Configure DB2 Federated tables ====


**Create a wrapper**
<sxh bash>
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')";
</sxh>

**Create a user mapping**
<sxh bash>
db2 "CREATE USER MAPPING FOR apainst1 SERVER "APAAIRDB" OPTIONS(REMOTE_AUTHID 'apainst1' ,REMOTE_PASSWORD 'passw0rd')"
</sxh>

**Create a table mapping**
<sxh bash>
db2 "create nickname apainst1.scr for APAAIRDB.AIR.scr";
</sxh>


==== Check DB2 Federation ====
**List User mapping & options :**
<sxh bash>
db2 "select varchar(authid,20) USER, varchar(servername,30) server, varchar(option,30) option, varchar(setting,50) setting from syscat.useroptions with ur"
</sxh>

**List Wrappers :**
<sxh bash>
db2 "select varchar(WRAPNAME,20) as WRAPNAME, WRAPTYPE, WRAPVERSION, varchar(LIBRARY,20) as LIBRARY from syscat.wrappers with ur"
</sxh>

**List Servers :**
<sxh bash>
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"
</sxh>

**List Nicknames :**
<sxh bash>
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"
</sxh>

==== Delete DB2 Federation ====

**Drop user mapping**
<sxh bash>
[apainst1@appid01-tj ~]$ db2 "drop user mapping for apainst1 server "APAAIRDB""
DB20000I  The SQL command completed successfully.
</sxh>


**Drop a server definition**
<sxh bash>
apainst1@appid01-tj ~]$ db2 "drop server APAAIRDB"
DB20000I  The SQL command completed successfully.
[apainst1@appid01-tj ~]$ 
</sxh>

===== Oracle data source =====

==== Configure Oracle local net service in Client ====

**Setup client connection : /path/to/network/admin/tnsnames.ora :**
<sxh bash>
GENCSCT_new =
  (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=192.168.172.225)
      (PORT=1523)
    )
    (CONNECT_DATA=
      (SID=GENCSCT)
    )
  )
</sxh>

==== Setup federation in DB2 ====

**1. Enable instance for federation :**
<sxh bash>
db2 update dbm cfg using federated yes
</sxh>

**2. Create or edit ~/sqllib/cfg/db2dj.ini to point to ORACLE_HOME of Oracle client :**
<sxh bash>
ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1
</sxh>

**3. Edit DB2 user's profile to export ORACLE_HOME and DB2LIBPATH :**
<sxh bash>
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/client_1
export DB2LIBPATH=$ORACLE_HOME/lib:$DB2LIBPATH:$DB2LIB
</sxh>

**4. Reload the profile and bounce the instance :**
<sxh bash>
. ~/.bashrc or ~/.profile

db2stop
db2start
</sxh>

**5. Create the Oracle predefined NET8 wrapper :**
<sxh bash>
db2 connect to <DB>
db2 "create wrapper NET8 library 'libdb2net8.so'"
</sxh>

**6. Create the server federation to data source :**
<sxh bash>
db2 "CREATE SERVER <SERVER> TYPE oracle VERSION '11g' WRAPPER NET8 OPTIONS (NODE '<NODE FROM TNSNAMES.ORA>')"
</sxh>

**7. Define user mapping :**
<sxh bash>
db2 "CREATE USER MAPPING FOR <USER or PUBLIC> SERVER <SERVER> OPTIONS (REMOTE_AUTHID '<remote_username>', REMOTE_PASSWORD '<remote_password>')"
</sxh>

**8. Create nicknames to access remote tables :**
<sxh bash>
db2 "CREATE NICKNAME <SCHEMA>.<NICKNAME> FOR <SERVER>.<REMOTE_SCHEMA>.<REMOTE_TABLE";
</sxh>


==== Federated objects ====
**Create index specification on nickname :**
<sxh bash>
db2 "CREATE [UNIQUE] INDEX <INDEX NAME> ON <NICKNAME> (<COLUMNS>) SPECIFICATION ONLY"
</sxh>

**Collecting statistics on federated objects :**
<sxh bash>
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)
                    )"
</sxh>

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 :**
<sxh bash>
db2 "CALL SYSPROC.NNSTAT('<SERVER>',NULL,NULL,NULL,NULL,0,NULL,?)"
</sxh>

**Check statistics collection of federated nicknames :**
<sxh bash>
db2 "select * from sysproc.fed_stats"
</sxh>