DB2 Federal Database

Target machine:192.168. 0.16 Local Machine: 192.168. 0.18 1 . Log in to the local database DB2 connect to DWMM user Dainst using Dainst

# # Open Federated database parameter  DB2 update dbm CFG using federated YES  DB2 Force application all  db2stop  Db2start

2. Cataloging Database # #1. Create catalog node DB2 connect to DWMM #连接数据库 DB2 list node Directory #查看节点信息 # #远程数据库全局设置 Db2set db2comm
    =TCPIP # #编目远程数据库节点 [in local database configuration] # #node名称: ndbdpdb # #远程IP:192.168.0.16# #端口号:50000DB2"catalog TCPIP Node ndbdpdb remote 192.168.0.16 server 50000"DB2"Terminate"# # Cataloging remote database DB2"Catalog db bdpdb as dbbdpdb at node ndbdpdb authentication SERVER"DB2"Terminate"# # Test If the connection is available DB2"List db directory"DB2"Connect to dbbdpdb user DW using password"3. Create the containment DB2 connect to DWMM DB2"CREATE WRAPPER DRDA"    4. Create a server DB2 that connects the data to each other"CREATE SERVER EDW TYPE db2/udb VERSION ' 9.7 ' WRAPPER DRDA AUTHORIZATION \ "dw\" PASSWORD \ "password\" OPTIONS (ADD NODE ' ndbdpdb ', DBNAME ' dbbdpdb ')"5. Create mapping DB2"CREATE USER MAPPING for Dainst SERVER EDW OPTIONS (ADD remote_authid ' DW ', Remote_password ' PASSWORD ')"6. Create a federated Relationship table 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

# #删除节点, cataloging database    DB2 uncatalog node nodename[nodes name]    DB2 terminate    DB2 Uncatalog DB dbname[alias]    DB2 terminate# Delete table mappings, user mappings, package containers, service names    drop nickname nickname     #删除表映射 drop    SERVER server_name    #删除用户映射    drop WRAPPER Wrapper_name  #删除包容器

1. Check and configure database parameters 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"# # Database parameters, page size 4k, database log DB2 UPDATE for DWMM USING locklist65536MAXLOCKS -LOGFILSIZ25600LOGPRIMARY theLOGSECOND -Sheapthres_shr -SORTHEAP8192LOGBUFSZ2048Util_heap_sz65536; # # Open Federated database parameter DB2 update dbm CFG using federated YES DB2 Force application all Db2stop Db2start2. Check and establish Bufferpool DB2 connect to DWMM user Dainst using DAINST;DB2 set schema Dainst; DB2 ALTER Bufferpool IBMDEFAULTBP SIZE -; DB2 CREATE Bufferpool bp32k SIZE16384PAGESIZE32768;  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. Check and establish tablespace DB2-SVTF CRT_DWMM_SPACES.DDL # # # data table space for TS_DWMM ts_sys_tmp # # # User table Space Ts_user_tmp # # temp table Space TEMPSPACE1 # # # system Tablespace SYSCAT SPACE systoolspace systoolstmpspace DB2 list tablespaces Show Detail|grepName4. Create a database Commonwealth # # remote Database Global settings Db2set Db2comm=TCPIP # # DB2 get dbm CFG|grep-I. TCP--get Svcename # #grepAbove the svcename/etc/Services # # Local Database Setup # # Cataloging remote database nodes DB2"catalog TCPIP Node ndbdpdb remote 155.101.252.172 server 50000"DB2"Terminate"# # Cataloging remote database DB2"Catalog db bdpdb as dbbdpdb at node ndbdpdb authentication SERVER"DB2"Terminate"# # Test If the connection is available DB2"List db directory"DB2"Connect to dbbdpdb user DW using password"# # CREATE DATABASE Federated DB2 connect to DWMM user dainst using Dainst; # # Create wrapper DB2"CREATE WRAPPER DRDA"# # Create a server DB2 that connects data to the other"CREATE SERVER EDW TYPE db2/udb VERSION ' 9.7 ' WRAPPER DRDA AUTHORIZATION \ "dw\" PASSWORD \ "password\" OPTIONS (ADD NOD E ' ndbdpdb ', DBNAME ' dbbdpdb ')"# # Create mapping DB2"CREATE USER MAPPING for Dainst SERVER EDW OPTIONS (ADD remote_authid ' DW ', Remote_password ' PASSWORD ')"# # Create a Federated Relationship table 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"# # See if the federated database is available DB2"get dbm CFG"|grep-I federated DB2"Force Applications All"db2stop Db2start5. Building ETL Scheduling related table DB2-SVTF CRT_DWMM_ETL_TABLE.DDL

DB2 Federal Database