DB2_Tips_Tricks_04 遠程數據庫之間復制數據
一、編目遠程數據庫
1、查看遠程名數據庫服務名
[db2inst4@db2-node01 ~]$ db2 get dbm cfg |grep SVCENAME
TCP/IP 服務名稱 (SVCENAME) = 60000
SSL 服務名稱 (SSL_SVCENAME) =
[db2inst4@db2-node01 ~]$
2、在本地實列編目數據庫
[db2inst1@db2 ~]$ db2 catalog tcpip node node_106 remote 108.88.3.106 server 60000
DB20000I CATALOG TCPIP NODE 命令成功完成。
DB21056W 直到刷新目錄高速緩存之後，目錄更改才生效。
[db2inst1@db2 ~]$ db2 catalog db test as test106 at node node_106
DB20000I CATALOG DATABASE 命令成功完成。
DB21056W 直到刷新目錄高速緩存之後，目錄更改才生效。
3、查看節點信息
[db2inst1@db2 ~]$ db2 LIST NODE DIRECTORY
節點目錄
目錄中的條目數 = 2
節點 1 條目：
節點名 = DB2
注釋 =
目錄條目類型 = LOCAL
協議 = TCPIP
主機名 = 127.0.0.1
服務名稱 = 50000
節點 2 條目：
節點名 = NODE_106
注釋 =
目錄條目類型 = LOCAL
協議 = TCPIP
主機名 = 108.88.3.106
服務名稱 = 60000
4、查看本地編目數據庫
[db2inst1@db2 ~]$ db2 list db directory
數據庫別名 = TANK
數據庫名稱 = TANK
本地數據庫目錄 = /home/db2inst1
數據庫發行版級別 = d.00
注釋 =
目錄條目類型 = 間接
目錄數據庫分區號 = 0
備用服務器主機名 =
備用服務器端口號 =
數據庫 6 條目：
數據庫別名 = TEST106
數據庫名稱 = TEST
節點名 = NODE_106
數據庫發行版級別 = d.00
注釋 =
目錄條目類型 = 遠程
目錄數據庫分區號 = -1
備用服務器主機名 =
備用服務器端口號 =
[db2inst1@db2 ~]$ db2 connect to test106 user db2inst4 USING db2inst4
數據庫連接信息
數據庫服務器 = DB2/LINUXX8664 9.7.5
SQL 授權標識 = DB2INST4
本地數據庫別名 = TEST106
[db2inst2@db2-node01 ~]$
TEST 源數據庫節點運行生成測試數據：
db2 "create table DB2INST2.TABLEA(id integer,name char(10),salary integer)"
db2 "insert into DB2INST2.TABLEA values(1001,'SAM',10000)"
db2 "insert into DB2INST2.TABLEA values(1002,'PAM',9500)"
db2 "insert into DB2INST2.TABLEA values(1003,'CAM',12500)"
db2 "insert into DB2INST2.TABLEA values(1004,'RAM',7500)"
db2 "insert into DB2INST2.TABLEA values(1005,'HAM',20000)"
db2 "create table DB2INST2.TABLEB(id integer,name char(10),salary integer)"
db2 "insert into DB2INST2.TABLEB values(2001,'SAM',10000)"
db2 "insert into DB2INST2.TABLEB values(2002,'PAM',9500)"
db2 "insert into DB2INST2.TABLEB values(2003,'CAM',12500)"
db2 "insert into DB2INST2.TABLEB values(2004,'RAM',7500)"
db2 "insert into DB2INST2.TABLEB values(2005,'HAM',20000)"
db2 "create table FOO.TABLEC(id integer not null primary key,name char(10),salary integer)"
db2 "insert into FOO.TABLEC values(3001,'SAM',10000)"
db2 "insert into FOO.TABLEC values(3002,'PAM',9500)"
db2 "insert into FOO.TABLEC values(3003,'CAM',12500)"
db2 "insert into FOO.TABLEC values(3004,'RAM',7500)"
db2 "insert into FOO.TABLEC values(3005,'HAM',20000)"
db2 "create table FOO.TABLED(id integer,lastname char(10))"
db2 "ALTER TABLE FOO.TABLED ADD CONSTRAINT EMP_LNAME FOREIGN KEY (ID) REFERENCES FOO.TABLEC"
db2 "insert into FOO.TABLED values(3001,'MAS')"
db2 "insert into FOO.TABLED values(3002,'MAP')"
db2 "insert into FOO.TABLED values(3003,'MAC')"
db2 "insert into FOO.TABLED values(3003,'MAC')"
db2 "insert into FOO.TABLED values(3003,'MAC')"
二、導入方法一
1、采用load with cursor 方式
DB2 LOAD WITH CURSOR:
[db2inst1@db2 ~]$ db2 connect to tank
數據庫連接信息
數據庫服務器 = DB2/LINUXX8664 9.7.5
SQL 授權標識 = DB2INST1
本地數據庫別名 = TANK
[db2inst1@db2 ~]$ db2 "create table DB2INST2.TABLEA(id integer,name char(10),salary integer)"
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 "drop table DB2INST2.TABLEA"
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 connect to tank
數據庫連接信息
數據庫服務器 = DB2/LINUXX8664 9.7.5
SQL 授權標識 = DB2INST1
本地數據庫別名 = TANK
[db2inst1@db2 ~]$ db2 "create table DB2INST1.MYTABLE(id integer,name char(10),salary integer)"
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 declare mycur cursor database TEST106 user db2inst4 using db2inst4 for select id,name,salary from db2inst2.tableA
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 load from mycur of cursor insert into db2inst1.mytable
SQL3501W 由於禁用數據庫正向恢復，因此表所駐留的表空間將
不被置於備份暫掛狀態。
SQL3039W 可供 DATA BUFFER 的 LOAD 使用的內存禁止完全 LOAD
並行性。將使用裝入並行性 "4"。
SQL3253N 實用程序正在開始通過 SQL 語句 " select id,name,salary
from db2inst2.tableA" 來從數據庫 "TEST106" 裝入數據。
SQL3500W 在時間 "2017-01-11 21:44:51.822168"，實用程序在開始
"LOAD"。
SQL3519W 開始裝入一致點。輸入記錄數 = "0"。
SQL3520W “裝入一致點”成功。
SQL3110N 實用程序已完成處理。從輸入文件讀了 "5" 行。
SQL3519W 開始裝入一致點。輸入記錄數 = "5"。
SQL3520W “裝入一致點”成功。
SQL3515W 在時間 "2017-01-11 21:44:52.121959"，實用程序已經完成了
"LOAD"。
讀取行數 = 5
跳過行數 = 0
裝入行數 = 5
拒絕行數 = 0
刪除行數 = 0
落實行數 = 5
[db2inst1@db2 ~]$ db2 "select * from db2inst1.mytable"
ID NAME SALARY
----------- ---------- -----------
1001 SAM 10000
1002 PAM 9500
1003 CAM 12500
1004 RAM 7500
1005 HAM 20000
5 條記錄已選擇。
[db2inst1@db2 ~]$
三、導入方法之-二
采用 DB2MOVE WITH COPY ACTION:
db2move
COPY -sn -tf -co TARGET_DB user using MODE [DDL_AND_LOAD,DDL_ONLY,LOAD_ONLY] -u -p
1、采用db2move copy 僅導入數據
[db2inst1@db2 ~]$ db2 connect to tank
數據庫連接信息
數據庫服務器 = DB2/LINUXX8664 9.7.5
SQL 授權標識 = DB2INST1
本地數據庫別名 = TANK
[db2inst1@db2 ~]$ db2 "create table DB2INST2.TABLEA(id integer,name char(10),salary integer)"
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 "create table FOO.TABLEC(id integer not null primary key,name char(10),salary integer)"
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2move TEST106 COPY -tn \"DB2INST2\".\"TABLEA\",\"FOO\".\"TABLEC\" -co TARGET_DB TANK user db2inst1 using db2inst1 MODE "LOAD_ONLY" -u db2inst4 -p db2inst4
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: COPY
Start time: Wed Jan 11 21:49:59 2017
All table names matching: "DB2INST2"."TABLEA"; "FOO"."TABLEC";
Connecting to database TEST106 ... successful! Server : DB2 Common Server V9.7.5
Start Load Phase :
db2move finished successfully
Files generated:
-----------------
COPYSCHEMA.20170111214959.msg
LOADTABLE.20170111214959.MSG
Please delete these files when they are no longer needed.
[db2inst2@db2-node01 ~]$ db2 "SELECT * FROM FOO.TABLEC"
ID NAME SALARY
----------- ---------- -----------
3001 SAM 10000
3002 PAM 9500
3003 CAM 12500
3004 RAM 7500
3005 HAM 20000
5 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM FOO.TABLEC"
ID NAME SALARY
----------- ---------- -----------
3001 SAM 10000
3002 PAM 9500
3003 CAM 12500
3004 RAM 7500
3005 HAM 20000
5 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM DB2INST2.TABLEA"
ID NAME SALARY
----------- ---------- -----------
1001 SAM 10000
1002 PAM 9500
1003 CAM 12500
1004 RAM 7500
1005 HAM 20000
5 條記錄已選擇。
2、采用db2move copy 使有指定導入表且僅導入數據
[db2inst1@db2 ~]$ more tbname.txt
"DB2INST2"."TABLEA"
"FOO"."TABLEC"
[db2inst1@db2 ~]$
[db2inst1@db2 ~]$ db2move TEST106 COPY -tf tbname.txt -co TARGET_DB TANK user db2inst1 using db2inst1 MODE "LOAD_ONLY" -u db2inst4 -p db2inst4
[db2inst1@db2 ~]$ more LOADTABLE.20170111220439.MSG
SQL3501W 由於禁用數據庫正向恢復，因此表所駐留的表空間將
不被置於備份暫掛狀態。
SQL3039W 可供 DATA BUFFER 的 LOAD 使用的內存禁止完全 LOAD
並行性。將使用裝入並行性 "4"。
SQL3254N 實用程序正在開始從數據庫 "TEST106" 中的表
""DB2INST2"".""TABLEA"" 裝入數據。
SQL3500W 在時間 "2017-01-11 22:04:42.391733"，實用程序在開始
"LOAD"。
SQL3519W 開始裝入一致點。輸入記錄數 = "0"。
SQL3520W “裝入一致點”成功。
SQL3110N 實用程序已完成處理。從輸入文件讀了 "5" 行。
SQL3519W 開始裝入一致點。輸入記錄數 = "5"。
SQL3520W “裝入一致點”成功。
SQL3515W 在時間 "2017-01-11 22:04:42.935579"，實用程序已經完成了
"LOAD"。
SQL3501W 由於禁用數據庫正向恢復，因此表所駐留的表空間將
不被置於備份暫掛狀態。
SQL3039W 可供 DATA BUFFER 的 LOAD 使用的內存禁止完全 LOAD
並行性。將使用裝入並行性 "4"。
SQL3254N 實用程序正在開始從數據庫 "TEST106" 中的表 ""FOO
"".""TABLEC"" 裝入數據。
SQL3500W 在時間 "2017-01-11 22:04:43.419405"，實用程序在開始
"LOAD"。
SQL3519W 開始裝入一致點。輸入記錄數 = "0"。
SQL3520W “裝入一致點”成功。
SQL3110N 實用程序已完成處理。從輸入文件讀了 "5" 行。
SQL3519W 開始裝入一致點。輸入記錄數 = "5"。
SQL3520W “裝入一致點”成功。
SQL3515W 在時間 "2017-01-11 22:04:43.638720"，實用程序已經完成了
"LOAD"。
SQL3500W 在時間 "2017-01-11 22:04:43.639014"，實用程序在開始
"BUILD"。
SQL3213I 建立索引方式為 "REBUILD"。
SQL3515W 在時間 "2017-01-11 22:04:43.829744"，實用程序已經完成了
"BUILD"。
3、采用db2move copy schema 復制方式
[db2inst1@db2 ~]$ db2move TEST106 COPY -sn DB2INST2,FOO -co TARGET_DB TANK user db2inst1 using db2inst1 MODE "DDL_AND_LOAD" -u db2inst4 -p db2inst4
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: COPY
Start time: Wed Jan 11 22:08:36 2017
All schema names matching: DB2INST2; FOO;
Connecting to database TEST106 ... successful! Server : DB2 Common Server V9.7.5
**ERROR** SYSTOOLSPACE doesn't exist on the source database.
Rolled back all changes from the create phase (debuginfo:60).
db2move failed with -1 (debuginfo:220).
Files generated:
-----------------
COPYSCHEMA.20170111220836.msg
Please delete these files when they are no longer needed.
**Error occured -1
End time: Wed Jan 11 22:08:37 2017
在源數據庫檢查是否存在：SYSTOOLSPACE
[db2inst4@db2-node01 ~]$ db2 "create tablespace SYSTOOLSPACE"
DB21034E 該命令被當作 SQL
[db2inst4@db2-node01 ~]$ db2 list tablespaces
當前數據庫的表空間
表空間標識 = 0
名稱 = SYSCATSPACE
類型 = 數據庫管理空間
內容 = 所有持久數據。常規表空間。
狀態 = 0x0000
詳細解釋：
正常
表空間標識 = 1
名稱 = TEMPSPACE1
類型 = 系統管理空間
內容 = 系統臨時數據
狀態 = 0x0000
詳細解釋：
正常
表空間標識 = 2
名稱 = USERSPACE1
類型 = 數據庫管理空間
內容 = 所有持久數據。大型表空間。
狀態 = 0x0000
詳細解釋：
正常
表空間標識 = 3
名稱 = SYSTOOLSPACE
類型 = 數據庫管理空間
內容 = 所有持久數據。大型表空間。
狀態 = 0x0000
詳細解釋：
正常
再次執行
[db2inst1@db2 ~]$ db2move TEST106 COPY -sn DB2INST2,FOO -co TARGET_DB TANK user db2inst1 using db2inst1 MODE "DDL_AND_LOAD" -u db2inst4 -p db2inst4
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: COPY
Start time: Wed Jan 11 22:16:19 2017
All schema names matching: DB2INST2; FOO;
Connecting to database TEST106 ... successful! Server : DB2 Common Server V9.7.5
Copy schema DB2INST2 to DB2INST2 on the target database TANK
Copy schema FOO to FOO on the target database TANK
Create DMT : "SYSTOOLS"."DMT_58763e33a1c87"
Start Load Phase :
db2move finished successfully
Files generated:
-----------------
COPYSCHEMA.20170111221619.msg
LOADTABLE.20170111221619.MSG
Please delete these files when they are no longer needed.
End time: Wed Jan 11 22:16:30 2017
[db2inst1@db2 ~]$
[db2inst1@db2 ~]$ db2 "SELECT * FROM DB2INST2.TABLEA"
ID NAME SALARY
----------- ---------- -----------
1001 SAM 10000
1002 PAM 9500
1003 CAM 12500
1004 RAM 7500
1005 HAM 20000
5 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM DB2INST2.TABLEB"
ID NAME SALARY
----------- ---------- -----------
2001 SAM 10000
2002 PAM 9500
2003 CAM 12500
2004 RAM 7500
2005 HAM 20000
5 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM FOO.TABLEC"
ID NAME SALARY
----------- ---------- -----------
3001 SAM 10000
3002 PAM 9500
3003 CAM 12500
3004 RAM 7500
3005 HAM 20000
5 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM FOO.TABLED"
ID LASTNAME
----------- ----------
3001 MAS
3002 MAP
3003 MAC
3003 MAC
3003 MAC
5 條記錄已選擇。
[db2inst1@db2 ~]$
4、采用db2move copy 先導DDL和後導入數據庫方式
[db2inst1@db2 ~]$ db2 DROP TABLE DB2INST2.TABLEA
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 DROP TABLE DB2INST2.TABLEB
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 DROP TABLE FOO.TABLEC
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 DROP TABLE FOO.TABLED
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 DROP SCHEMA DB2INST2 RESTRICT
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 DROP SCHEMA FOO RESTRICT
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2move TEST106 COPY -sn DB2INST2,FOO -co TARGET_DB TANK user db2inst1 using db2inst1 MODE "DDL_ONLY" -u db2inst4 -p db2inst4
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: COPY
Start time: Wed Jan 11 22:41:02 2017
All schema names matching: DB2INST2; FOO;
Connecting to database TEST106 ... successful! Server : DB2 Common Server V9.7.5
Copy schema DB2INST2 to DB2INST2 on the target database TANK
Copy schema FOO to FOO on the target database TANK
Create DMT : "SYSTOOLS"."DMT_587643ff130e2"
db2move finished successfully
Files generated:
-----------------
COPYSCHEMA.20170111224102.msg
Please delete these files when they are no longer needed.
End time: Wed Jan 11 22:41:04 2017
[db2inst1@db2 ~]$ db2 "SELECT * FROM DB2INST2.TABLEA"
ID NAME SALARY
----------- ---------- -----------
0 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM DB2INST2.TABLEB"
ID NAME SALARY
----------- ---------- -----------
0 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM FOO.TABLEC"
ID NAME SALARY
----------- ---------- -----------
0 條記錄已選擇。
[db2inst1@db2 ~]$ db2 "SELECT * FROM FOO.TABLED"
ID LASTNAME
----------- ----------
0 條記錄已選擇。
[db2inst1@db2 ~]$ more tbname.txt
"DB2INST2"."TABLEA"
"DB2INST2"."TABLEB"
"FOO"."TABLEC"
"FOO"."TABLED"
[db2inst1@db2 ~]$ db2move TEST106 COPY -tf tbname.txt -co TARGET_DB TANK user db2inst1 using db2inst1 MODE "LOAD_ONLY" -u db2inst4 -p db2inst4
Application code page not determined, using ANSI codepage 1208
***** DB2MOVE *****
Action: COPY
Start time: Wed Jan 11 22:45:30 2017
All table names matching:
Connecting to database TEST106 ... successful! Server : DB2 Common Server V9.7.5
Start Load Phase :
db2move finished successfully
Files generated:
-----------------
COPYSCHEMA.20170111224530.msg
LOADTABLE.20170111224530.MSG
Please delete these files when they are no longer needed.
End time: Wed Jan 11 22:45:34 2017
[db2inst1@db2 ~]$ db2 "SELECT * FROM FOO.TABLED"
ID LASTNAME
----------- ----------
SQL0668N 不允許對表 "FOO.TABLED" 執行操作，原因碼為 "1"。
SQLSTATE=57016
[db2inst1@db2 ~]$ db2 -tsvf 123.sql
EXPORT TO 345.sql OF DEL MODIFIED BY nochardel with gen(tabname, seq) as( select rtrim(tabschema) || '.' || rtrim(tabname) as tabname, row_number() over (partition by status) as seq from syscat.tables WHERE status='C' ),r(a, seq1) as (select CAST(tabname as VARCHAR(3900)), seq from gen where seq=1 union all select r.a || ','|| rtrim(gen.tabname), gen.seq from gen , r where (r.seq1+1)=gen.seq ), r1 as (select a, seq1 from r) select 'SET INTEGRITY FOR ' || a || ' IMMEDIATE CHECKED;' from r1 where seq1=(select max(seq1) from r1)
SQL0347W 遞歸公共表表達式 "DB2INST1.R" 可能包含無限循環。
SQLSTATE=01605
SQL3104N EXPORT 實用程序 正在開始將數據導出至文件 "345.sql"。
SQL3105N Export 實用程序已經完成導出 "1" 行。
導出的行數：1
[db2inst1@db2 ~]$ more 345.sql
SET INTEGRITY FOR FOO.TABLEC,FOO.TABLED IMMEDIATE CHECKED;
[db2inst1@db2 ~]$ db2 -tsvf 345.sql
SET INTEGRITY FOR FOO.TABLEC,FOO.TABLED IMMEDIATE CHECKED
DB20000I SQL 命令成功完成。
[db2inst1@db2 ~]$ db2 "SELECT * FROM FOO.TABLED"
ID LASTNAME
----------- ----------
3001 MAS
3002 MAP
3003 MAC
3003 MAC
3003 MAC
