DB2_Tips_Tricks_04 copy data between remote databases

DB2_Tips_Tricks_04 copy data between remote databases

I. cataloguing remote databases

1. view the remote name database service name
[Db2inst4 @ db2-node01 ~] $ Db2 get dbm cfg | grep SVCENAME
TCP/IP service name (SVCENAME) = 60000
SSL Service name (SSL_SVCENAME) =
[Db2inst4 @ db2-node01 ~] $

2. Local real-column cataloguing Database
[Db2inst1 @ db2 ~] $ Db2 catalog tcpip node node_106 remote 108.88.3.106 server 60000
The DB20000I catalog tcpip node command is successfully completed.
DB21056W does not take effect until the directory cache is refreshed.


[Db2inst1 @ db2 ~] $ Db2 catalog db test as test106 at node node_106
The DB20000I catalog database Command is successfully completed.
DB21056W does not take effect until the directory cache is refreshed.

3. View node Information

[Db2inst1 @ db2 ~] $ Db2 LIST NODE DIRECTORY

Node directory


Number of entries in the directory = 2


Node 1 entry:


Node name = DB2
Note =
Directory Entry type = LOCAL
Protocol = TCPIP
Host Name = 127.0.0.1
Service name = 50000


Node 2 entries:


Node name = NODE_106
Note =
Directory Entry type = LOCAL
Protocol = TCPIP
Host Name = 108.88.3.106
Service name = 60000

4. view the local cataloguing Database
[Db2inst1 @ db2 ~] $ Db2 list db directory

Database alias = TANK
Database Name = TANK
Local Database directory =/home/db2inst1
Database Release level = d.00
Note =
Directory Entry type = indirect
Directory database partition number = 0
Backup Server Host Name =
Backup server port number =


Database 6 entries:


Database alias = TEST106
Database Name = TEST
Node name = NODE_106
Database Release level = d.00
Note =
Directory Entry type = remote
Directory database partition number =-1
Backup Server Host Name =
Backup server port number =


[Db2inst1 @ db2 ~] $ Db2 connect to test106 user db2inst4 USING db2inst4


Database connection information


Database Server = DB2/LINUXX8664 9.7.5
SQL authorization id = DB2INST4
Local Database alias = TEST106

[Db2inst2 @ db2-node01 ~] $

TEST source database node run to generate TEST data:

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 ')"


Ii. Import method 1


1. load with cursor

DB2 load with cursor:

[Db2inst1 @ db2 ~] $ Db2 connect to tank


Database connection information


Database Server = DB2/LINUXX8664 9.7.5
SQL authorization id = DB2INST1
Local Database alias = TANK


[Db2inst1 @ db2 ~] $ Db2 "create table DB2INST2. TABLEA (id integer, name char (10), salary integer )"
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 "drop table DB2INST2. TABLEA"
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 connect to tank


Database connection information


Database Server = DB2/LINUXX8664 9.7.5
SQL authorization id = DB2INST1
Local Database alias = TANK

[Db2inst1 @ db2 ~] $ Db2 "create table DB2INST1. MYTABLE (id integer, name char (10), salary integer )"
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 declare mycur cursor database TEST106 user db2inst4 using db2inst4 for select id, name, salary from db2inst2. tableA
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 load from mycur of cursor insert into db2inst1. mytable
SQL3501W because the Forward Recovery of the database is disabled, the tablespace where the table resides will
It is not put in the backup hold state.


SQL3039W memory available for data buffer load prohibit full LOAD
Parallelism. The load concurrency will be used "4 ".


SQL3253N utility is starting to use SQL statement "select id, name, salary
From db2inst2. tableA "to load data from the database" TEST106.


SQL3500W at the time "21:44:51. 822168", the utility starts
"LOAD ".


SQL3519W starts to load the consistent point. Number of input records = "0 ".


SQL3520W "load consistency point" is successful.


The SQL3110N utility has been processed. Read the "5" line from the input file.


SQL3519W starts to load the consistent point. Number of input records = "5 ".


SQL3520W "load consistency point" is successful.


SQL3515W at the time "21:44:52. 121959", the utility has been completed
"LOAD ".




Number of read rows = 5
Skipped rows = 0
Number of loaded rows = 5
Number of rejected rows = 0
Number of lines deleted = 0
Number of implemented rows = 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


Five records are selected.


[Db2inst1 @ db2 ~] $

Iii. Import method-2

Use DB2MOVE with copy action:


Db2move COPY-sn -Tf -Co TARGET_DB User Using MODE [DDL_AND_LOAD, DDL_ONLY, LOAD_ONLY]-u -P


1. Use db2move copy to import data only

[Db2inst1 @ db2 ~] $ Db2 connect to tank


Database connection information


Database Server = DB2/LINUXX8664 9.7.5
SQL authorization id = DB2INST1
Local Database alias = TANK


[Db2inst1 @ db2 ~] $ Db2 "create table DB2INST2. TABLEA (id integer, name char (10), salary integer )"
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 "create table FOO. TABLEC (id integer not null primary key, name char (10), salary integer )"
The DB20000I SQL command is successfully completed.

[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


Five records are selected.

[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


Five records are selected.


[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


Five records are selected.

2. Use db2move copy to import only the specified table


[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 because the Forward Recovery of the database is disabled, the tablespace where the table resides will
It is not put in the backup hold state.
SQL3039W memory available for data buffer load prohibit full LOAD
Parallelism. The load concurrency will be used "4 ".

SQL3254N utility is starting from table in Database "TEST106"
"" DB2INST2 "". "" TABLEA "" load data.
SQL3500W at the time "22:04:42. 391733", the utility starts
"LOAD ".

SQL3519W starts to load the consistent point. Number of input records = "0 ".
SQL3520W "load consistency point" is successful.
The SQL3110N utility has been processed. Read the "5" line from the input file.
SQL3519W starts to load the consistent point. Number of input records = "5 ".
SQL3520W "load consistency point" is successful.
SQL3515W at the time "22:04:42. 935579", the utility has been completed
"LOAD ".
SQL3501W because the Forward Recovery of the database is disabled, the tablespace where the table resides will
It is not put in the backup hold state.
SQL3039W memory available for data buffer load prohibit full LOAD
Parallelism. The load concurrency will be used "4 ".
SQL3254N utility is starting from table "FOO" in Database "TEST106"
"". "" TABLEC "" load data.
SQL3500W at the time "22:04:43. 419405", the utility starts
"LOAD ".
SQL3519W starts to load the consistent point. Number of input records = "0 ".
SQL3520W "load consistency point" is successful.
The SQL3110N utility has been processed. Read the "5" line from the input file.
SQL3519W starts to load the consistent point. Number of input records = "5 ".
SQL3520W "load consistency point" is successful.
SQL3515W at the time "22:04:43. 638720", the utility has been completed
"LOAD ".
SQL3500W at the time "22:04:43. 639014", the utility starts
"BUILD ".
SQL3213I index creation method is "REBUILD ".
SQL3515W at the time "22:04:43. 829744", the utility has been completed
"BUILD ".

3. db2move copy schema Replication

[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 ** when oolspace 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
Check whether the source database exists: exist oolspace
[Db2inst4 @ db2-node01 ~] $ Db2 "create tablespace using oolspace"


DB21034E this command is treated as SQL


[Db2inst4 @ db2-node01 ~] $ Db2 list tablespaces


Tablespace of the current database


Tablespace id = 0
Name = SYSCATSPACE
Type = database management space
Content = all persistent data. Regular tablespace.
Status = 0x0000
Explanation:
Normal


Tablespace id = 1
Name = TEMPSPACE1
Type = system management space
Content = temporary system data
Status = 0x0000
Explanation:
Normal


Tablespace id = 2
Name = USERSPACE1
Type = database management space
Content = all persistent data. Large tablespace.
Status = 0x0000
Explanation:
Normal


Tablespace id = 3
Name = paioolspace
Type = database management space
Content = all persistent data. Large tablespace.
Status = 0x0000
Explanation:
Normal
Execute again

[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


Five records are selected.


[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


Five records are selected.


[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


Five records are selected.


[Db2inst1 @ db2 ~] $ Db2 "SELECT * from foo. TABLED"


ID LASTNAME
---------------------
3001 MAS
3002 MAP
3003 MAC
3003 MAC
3003 MAC


Five records are selected.


[Db2inst1 @ db2 ~] $
4. Use db2move copy pilot DDL and post-import database
[Db2inst1 @ db2 ~] $ Db2 drop table DB2INST2. TABLEA
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 drop table DB2INST2. TABLEB
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 drop table foo. TABLEC
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 drop table foo. TABLED
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 drop schema DB2INST2 RESTRICT
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 DROP SCHEMA FOO RESTRICT
The DB20000I SQL command is successfully completed.
[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 records are selected.


[Db2inst1 @ db2 ~] $ Db2 "SELECT * FROM DB2INST2. TABLEB"
ID NAME SALARY
--------------------------------


0 records are selected.


[Db2inst1 @ db2 ~] $ Db2 "SELECT * from foo. TABLEC"
ID NAME SALARY
--------------------------------


0 records are selected.
[Db2inst1 @ db2 ~] $ Db2 "SELECT * from foo. TABLED"


ID LASTNAME
---------------------


0 records are selected.
[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 is not allowed to perform operations on the table "FOO. TABLED". The Reason code is "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)
Sql00000w recursive common table expression "DB2INST1. R" may contain infinite loops.
SQLSTATE = 01605
The sql310000n EXPORT utility is starting to EXPORT data to the file "345. SQL ".
The SQL3105N Export utility has finished exporting "1.

Number of exported rows: 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
The DB20000I SQL command is successfully completed.
[Db2inst1 @ db2 ~] $ Db2 "SELECT * from foo. TABLED"


ID LASTNAME
---------------------
3001 MAS
3002 MAP
3003 MAC
3003 MAC
3003 MAC