一次db2备份和导入

最新推荐文章于 2023-12-25 16:52:19 发布

原创最新推荐文章于 2023-12-25 16:52:19 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

db2 专栏收录该内容

4 篇文章

订阅专栏

1.使用dbvis

2.用sql建立数据库

CREATE DATABASE NCXH ON 'D:\DB2\NODE0000\NCXH\' USING CODESET GBK TERRITORY CN COLLATE USING SYSTEM CATALOG TABLESPACE  MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\Catalogdata1' 128000 )  USER TABLESPACE  MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\Userdata1' 512000 )  TEMPORARY TABLESPACE  MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\Tempspace1' 768000 )

connect to NCXH user db2inst1 using db2inst1 

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 'D:\DB2\NODE0000\NCXH\nnc_data01' 204800) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

CREATE REGULAR TABLESPACE NNC_INDEX01 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\nnc_index01' 204800) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

CREATE REGULAR TABLESPACE NNC_DATA02 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\nnc_data02' 20480) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

CREATE REGULAR TABLESPACE NNC_INDEX02 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\nnc_index02' 40960) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

CREATE REGULAR TABLESPACE NNC_DATA03 PAGESIZE 16K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\nnc_data03' 204800) EXTENTSIZE 8 OVERHEAD 24.1 PREFETCHSIZE 8 TRANSFERRATE 0.9 BUFFERPOOL NCUSED16

CREATE REGULAR TABLESPACE NNC_INDEX03 PAGESIZE 4K MANAGED BY DATABASE USING ( FILE 'D:\DB2\NODE0000\NCXH\nnc_index03' 204800) EXTENTSIZE 32 OVERHEAD 24.1 PREFETCHSIZE 32 TRANSFERRATE 0.9 BUFFERPOOL NCUSED4

CREATE  USER TEMPORARY  TABLESPACE USERTEMP PAGESIZE 4 K  MANAGED BY DATABASE  USING ( FILE 'D:\DB2\NODE0000\NCXH\Usertemp1' 2560000 ) EXTENTSIZE 64 OVERHEAD 10.67 PREFETCHSIZE 64 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED4 

CREATE  SYSTEM TEMPORARY  TABLESPACE TEMPSPACE2 PAGESIZE 16 K  MANAGED BY DATABASE  USING ( FILE 'D:\DB2\NODE0000\NCXH\Tempspace2' 192000 ) EXTENTSIZE 32 OVERHEAD 10.67 PREFETCHSIZE 32 TRANSFERRATE 0.04 BUFFERPOOL  NCUSED16

GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2admin;

GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER db2admin;

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;

3.上面sql用db2inst1 用户之前，而要创建用户

CONNECT TO NCXH;
GRANT  DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE  TO USER DB2INST1;
CONNECT RESET;

4.接着就是将导出的文件执行