DB2_Tips_Tricks_04 遠端資料庫之間複製資料

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