DB2 使用游标迁移数据

1.在源端db2look导出表结构

https://www.cndba.cn/hbhe0316/article/4809

db2look -d testdb -e -a -x -l -f -o /tmp/db2look_testdb_0805.ddl

2.在目标端将表结构导入

https://www.cndba.cn/hbhe0316/article/4809

https://www.cndba.cn/hbhe0316/article/4809

db2 -tvf /tmp/db2look_testdb_0805.ddl

3.游标脚本编写，其中testdb12为远程编目数据库

https://www.cndba.cn/hbhe0316/article/4809

1.shhttps://www.cndba.cn/hbhe0316/article/4809https://www.cndba.cn/hbhe0316/article/4809

for TABLE_NAME in `cat /tmp/testdb_tab.list`
do
  db2 "terminate"
  db2 "connect to testdb"
  db2 "declare c1 cursor database testdb12 user db2inst1 using wwwwww for select * from $TABLE_NAME"
  db2 "load from c1 of cursor modified by identityoverride messages /tmp/.msg  replace into $TABLE_NAME NONRECOVERABLE"
  db2 "terminate"
done

testdb_tab.list里面存的是表的名字

https://www.cndba.cn/hbhe0316/article/4809

https://www.cndba.cn/hbhe0316/article/4809

[db2inst1@db01 ~]$ cat /tmp/testdb_tab.list 
t1
t2

db2 list tables | awk '{print $1}' | awk '{if(NR>3)print}'

4.执行命令，这样将t1和t2表中的数据导入到本地

https://www.cndba.cn/hbhe0316/article/4809

https://www.cndba.cn/hbhe0316/article/4809

[db2inst1@db01 ~]$ sh 1.sh 
DB20000I  The TERMINATE command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.1
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

DB20000I  The SQL command completed successfully.

Number of rows read         = 4
Number of rows skipped      = 0
Number of rows loaded       = 4
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 4

DB20000I  The TERMINATE command completed successfully.
DB20000I  The TERMINATE command completed successfully.

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.1
 SQL authorization ID   = DB2INST1
 Local database alias   = TESTDB

DB20000I  The SQL command completed successfully.

Number of rows read         = 3
Number of rows skipped      = 0
Number of rows loaded       = 3
Number of rows rejected     = 0
Number of rows deleted      = 0
Number of rows committed    = 3

DB20000I  The TERMINATE command completed successfully.

版权声明：本文为博主原创文章，未经博主允许不得转载。

oracle,linux