目标机器：192.168.0.16

本地机器：192.168.0.18

.登陆本地数据库

db2 connect to dwmm user dainst using dainst

## 打开联邦数据库参数

  db2 update dbm cfg using FEDERATED YES

  db2 force application all

  db2stop

  db2start

.编目数据库

    ##.创建编目节点

    db2 connect to dwmm       #连接数据库

    db2 list node directory   #查看节点信息

    ##远程数据库全局设置

    db2set

    DB2COMM=TCPIP

    ##编目远程数据库节点[在本地数据库配置]

    ##node名称：NDBDPDB

    ##远程IP:192.168.0.16

    ##端口号：

    db2 "catalog tcpip node NDBDPDB remote 192.168.0.16 server 50000"

    db2 "terminate"

    ## 编目远程数据库

    db2 "catalog db bdpdb as DBBDPDB at node NDBDPDB authentication SERVER"

    db2 "terminate"

    ## 测试连接是否可用

    db2 "list db directory"

    db2 "connect to DBBDPDB user dw using password"

.创建包容器

    db2 connect to dwmm

    db2 "CREATE WRAPPER DRDA"

.创建连接对方数据的SERVER

  db2 "CREATE SERVER EDW TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHORIZATION \"dw\" PASSWORD \"password\" OPTIONS (ADD NODE 'NDBDPDB',DBNAME 'DBBDPDB')"

.创建MAPPING

  db2 "CREATE USER MAPPING FOR dainst SERVER EDW OPTIONS (ADD REMOTE_AUTHID 'dw',REMOTE_PASSWORD 'password')"

.创建联邦关系表

  db2 "CREATE NICKNAME CORE.BGFMCINF FOR EDW.DW.F_CORE_BGFMCINF"

  db2 "ALTER NICKNAME CORE.BGFMCINF ALTER COLUMN ETL_FLAG LOCAL TYPE CHARACTER (1)"

  db2 "ALTER NICKNAME CORE.BGFMCINF ALLOW CACHING"

  ##查看联邦数据库是否可用

  db2 "get dbm cfg" | grep -i federated

  db2 "force applications all"

  db2stop

  db2start

##删除节点、编目数据库

    db2 uncatalog node nodename[节点名]

    db2 terminate

    db2 uncatalog db dbname[别名]

    db2 terminate

#删除表映射、用户映射、包容器、服务名

    DROP NICKNAME nickname     #删除表映射

    DROP SERVER server_name    #删除用户映射

    DROP WRAPPER wrapper_name  #删除包容器

.检查并配置数据库参数

  db2 connect to dwmm user dainst using dainst;db2 set schema dainst;

  db2 RESET DATABASE CONFIGURATION FOR DWMM;

  db2 get db cfg|grep -wE "LOCKLIST|MAXLOCKS|LOGFILSIZ|LOGPRIMARY|LOGSECOND|SHEAPTHRES_SHR|SORTHEAP|UTIL_HEAP_SZ"

  ## 数据库参数,页大小4k,数据库日志

  db2 UPDATE DATABASE CONFIGURATION FOR DWMM USING LOCKLIST  MAXLOCKS  LOGFILSIZ  LOGPRIMARY  LOGSECOND  SHEAPTHRES_SHR  SORTHEAP  LOGBUFSZ  UTIL_HEAP_SZ ;

  ## 打开联邦数据库参数

  db2 update dbm cfg using FEDERATED YES

  db2 force application all

  db2stop

  db2start

.检查并建立 BUFFERPOOL

  db2 connect to dwmm user dainst using dainst;db2 set schema dainst;

  db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE ;

  db2 CREATE BUFFERPOOL BP32K SIZE  PAGESIZE ;

  db2 CONNECT RESET;

  db2 connect to dwmm user dainst using dainst;db2 set schema dainst;

  db2 -x "select BPNAME,NPAGES,PAGESIZE from syscat.bufferpools with ur"

.检查并建立 TABLESPACE

  db2 -svtf crt_dwmm_spaces.ddl

  ### 数据表空间为 TS_DWMM TS_SYS_TMP

  ### 用户表空间 TS_USER_TMP

  ### 临时表空间 TEMPSPACE1

  ### 系统表空间 SYSCATSPACE SYSTOOLSPACE SYSTOOLSTMPSPACE

  db2 list tablespaces show detail|grep Name

.创建数据库联邦

  ## 远程数据库全局设置

  db2set

  DB2COMM=TCPIP

  ## db2 get dbm cfg|grep -i TCP  --得到SVCENAME

  ## grep 上面的SVCENAME /etc/services

  ## 本地数据库设置

  ## 编目远程数据库节点

  db2 "catalog tcpip node NDBDPDB remote 155.101.252.172 server 50000"

  db2 "terminate"

  ## 编目远程数据库

  db2 "catalog db bdpdb as DBBDPDB at node NDBDPDB authentication SERVER"

  db2 "terminate"

  ## 测试连接是否可用

  db2 "list db directory"

  db2 "connect to DBBDPDB user dw using password"

  ## 创建数据库联邦

  db2 connect to dwmm user dainst using dainst;

  ## 创建WRAPPER

  db2 "CREATE WRAPPER DRDA"

  ## 创建连接对方数据的SERVER

  db2 "CREATE SERVER EDW TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHORIZATION \"dw\" PASSWORD \"password\" OPTIONS (ADD NODE 'NDBDPDB',DBNAME 'DBBDPDB')"

  ## 创建MAPPING

  db2 "CREATE USER MAPPING FOR dainst SERVER EDW OPTIONS (ADD REMOTE_AUTHID 'dw',REMOTE_PASSWORD 'password')"

  ## 创建联邦关系表

  db2 "CREATE NICKNAME CORE.BGFMCINF FOR EDW.DW.F_CORE_BGFMCINF"

  db2 "ALTER NICKNAME CORE.BGFMCINF ALTER COLUMN ETL_FLAG LOCAL TYPE CHARACTER (1)"

  db2 "ALTER NICKNAME CORE.BGFMCINF ALLOW CACHING"

  ## 查看联邦数据库是否可用

  db2 "get dbm cfg" | grep -i federated

  db2 "force applications all"

  db2stop

  db2start

.建ETL调度相关表

  db2 -svtf crt_dwmm_etl_table.ddl