DB2导出导入数据库

DB2 导出 数据库 :db2move ncdb export -u db2admin -p 1

NCDB 为 数据库实例 DB2ADMIN为 用户名 1 为密码


DB2恢复: db2move 实例 import –u 用户名 –p 密码
DB2导出: db2move 实例 export -u 用户名 -p 密码





--DB2备份:
--连接数据库:开始---运行----db2cmd 然后输入以下命令
db2 connnect to ncdb user db2admin using 1
--解释: ncdb为实例名称 db2admin 用户名 1 为密码
--执行导出表结构命令
db2look -d ncdb -a -e -x -o ncdb.sql
--导出命令
db2move ncdb export
--导出完成




--DB2恢复:
--创建数据库:
CREATE DATABASE NCDB ON 'c:' USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\Catalogdata1' 128000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\Userdata1' 512000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\Tempspace1' 768000 )
--连接数据库
connect to ncdb user db2admin using 1
--执行创建表空间语句 DB2不会自动扩展 所以建表空间时候 建议直接建大点 此处的默认为 8G 可以自行修改
CREATE Bufferpool NCUSED4 SIZE 102400 PAGESIZE 4K

CREATE Bufferpool NCUSED16 SIZE 38400 PAGESIZE 16K

CREATE REGULAR TABLESPACE NNC_DATA01 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\nnc_data01' 8G) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

CREATE REGULAR TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\nnc_index01' 8G) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

CREATE REGULAR TABLESPACE NNC_DATA02 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\nnc_data02' 8G) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

CREATE REGULAR TABLESPACE NNC_INDEX02 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\nnc_index02' 8G) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

CREATE REGULAR TABLESPACE NNC_DATA03 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\nnc_data03' 8G) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

CREATE REGULAR TABLESPACE NNC_INDEX03 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'c:\DATA\NCDB\nnc_index03' 8G) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

CREATE  USER TEMPORARY  TABLESPACE USERTEMP PAGESIZE 16 K  MANAGED BY SYSTEM  USING ( 'c:\DATA\NCDB\Usertemp1' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16

CREATE  SYSTEM TEMPORARY  TABLESPACE TEMPSPACE2 PAGESIZE 16 K  MANAGED BY SYSTEM  USING (  'c:\DATA\NCDB\Tempspace2' ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16

--执行委派命令
db2 connect to ncdb

GRANT USE OF TABLESPACE NNC_DATA01 TO USER db2admin WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_DATA02 TO USER db2admin WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_DATA03 TO USER db2admin WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_INDEX01 TO USER db2admin WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_INDEX02 TO USER db2admin WITH GRANT OPTION
GRANT USE OF TABLESPACE NNC_INDEX03 TO USER db2admin WITH GRANT OPTION
GRANT USE OF TABLESPACE USERTEMP TO USER db2admin WITH GRANT OPTION
--重新连接
CONNECT RESET


---恢复数据库 首先进入 备份文件的目录
--创建表结构
db2 -tvf ncdb.sql >ncdb.log
--导入备份文件
db2move ncdb load >load.log


--连接数据库
db2 connnect to ncdb
--检查表是否挂起
db2 "select 'set INTEGRITY for DB2ADMIN.'||substr(tabname,1,30)||' IMMEDIATE CHECKED ;' from syscat.tables where status='C'"

--如果有挂起 执行 以下语句(可以多执行几次):

db2 "select 'set INTEGRITY for DB2ADMIN.'||substr(tabname,1,30)||' IMMEDIATE CHECKED ;' from syscat.tables where status='C'">set.sql


db2 -tvf set.sql


--常见错误解决 SQL0668N  不允许对表XXX 执行操作，原因码为 3 SQLSTATE=57016
--解决办法：（注意，有可能是表空间大小不够，切记要去查询各语句执行的结果——日志文件）
--查看表状态  DB2ADMIN.SM_BUSI_NAVIGATION 为表名
db2 load query table DB2ADMIN.SM_BUSI_NAVIGATION
--解决装入暂挂：
db2 LOAD FROM D:\x.txt OF DEL TERMINATE INTO DB2ADMIN.SM_BUSI_NAVIGATION
--解决设置完整性暂挂：
db2 SET INTEGRITY FOR DB2ADMIN.SM_BUSI_NAVIGATION IMMEDIATE CHECKED