db2 联邦 mysql_db2联邦数据库

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 LOCKLIST65536 MAXLOCKS 60 LOGFILSIZ 25600 LOGPRIMARY 15 LOGSECOND 30 SHEAPTHRES_SHR 250 SORTHEAP 8192 LOGBUFSZ 2048 UTIL_HEAP_SZ 65536;

## 打开联邦数据库参数

db2 update dbm cfg using FEDERATED YES

db2 force application all

db2stop

db2start2.检查并建立 BUFFERPOOL

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

db2 ALTER BUFFERPOOL IBMDEFAULTBP SIZE250;

db2 CREATE BUFFERPOOL BP32K SIZE16384 PAGESIZE 32768;

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|grepName4.创建数据库联邦

## 远程数据库全局设置

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

db2start5.建ETL调度相关表

db2-svtf crt_dwmm_etl_table.ddl