server eset 创建数据库 mps max star 关系 grep
- 目标机器:192.168.0.16
- 本地机器:192.168.0.18
- 1.登陆本地数据库
- db2 connect to dwmm user dainst using dainst
- ## 打开联邦数据库参数
- db2 update dbm cfg using FEDERATED YES
- db2 force application all
- db2stop
- db2start
- 2.编目数据库
- ##1.创建编目节点
- db2 connect to dwmm #连接数据库
- db2 list node directory #查看节点信息
- ##远程数据库全局设置
- db2set
- DB2COMM=TCPIP
- ##编目远程数据库节点[在本地数据库配置]
- ##node名称:NDBDPDB
- ##远程IP:192.168.0.16
- ##端口号:50000
- 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"
- 3.创建包容器
- db2 connect to dwmm
- db2 "CREATE WRAPPER DRDA"
- 4.创建连接对方数据的SERVER
- db2 "CREATE SERVER EDW TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHORIZATION \"dw\" PASSWORD \"password\" OPTIONS (ADD NODE 'NDBDPDB',DBNAME 'DBBDPDB')"
- 5.创建MAPPING
- db2 "CREATE USER MAPPING FOR dainst SERVER EDW OPTIONS (ADD REMOTE_AUTHID 'dw',REMOTE_PASSWORD 'password')"
- 6.创建联邦关系表
- 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 #删除包容器
- 1.检查并配置数据库参数
- 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 65536MAXLOCKS60LOGFILSIZ25600LOGPRIMARY15LOGSECOND30SHEAPTHRES_SHR250SORTHEAP8192LOGBUFSZ2048UTIL_HEAP_SZ65536;
- ## 打开联邦数据库参数
- db2 update dbm cfg using FEDERATED YES
- db2 force application all
- db2stop
- db2start
- 2.检查并建立 BUFFERPOOL
- db2 connect to dwmm user dainst using dainst;db2 set schema dainst;
- db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE 250;
- db2 CREATE BUFFERPOOL BP32K SIZE 16384PAGESIZE32768;
- 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"
- 3.检查并建立 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
- 4.创建数据库联邦
- ## 远程数据库全局设置
- 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
- 5.建ETL调度相关表
- db2 -svtf crt_dwmm_etl_table.ddl
db2 联邦数据库
来源: http://www.bubuko.com/infodetail-2010104.html