Db2 client上load from cursor报错SQL1013N The database name .. could not be found

问题描述

今天有一个客户问了一个问题：有两台Db2 server，分别包含数据库source和target，能不能在第三台client机上通过load cursor的方式把source数据库里的数据导入到target数据库里？

测试环境

第一直觉应该是可以的，于是搭建了一个测试环境，三台机器都为Ubuntu linux/Db2 10.5，三台机器名分别为db2a, db2b, db2c:

db2a
数据库名：source
表：ta

用户名/密码：inst105/inst105

db2b
数据库名：target
表：tb

用户名/密码：inst105/inst105

db2c
db2 client, 用于执行load ... cursor命令，目的为把表ta的数据导入到表tb中

问题重现

首先在client上catalog node和database，完成后效果如下，可以看到source和target数据库已经编目好，并且能正常访问sourcedb和targetdb
qingsong@db2c:~$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 2

Database 1 entry:

 Database alias = TARGETDB
 Database name = TARGET
 Node name = NODEDB2B
 Database release level = 10.00
 Comment =
 Directory entry type = Remote
 Catalog database partition number = -1
 Alternate server hostname =
 Alternate server port number =

Database 2 entry:

 Database alias = SOURCEDB
 Database name = SOURCE
 Node name = NODEDB2A
 Database release level = 10.00
 Comment =
 Directory entry type = Remote
 Catalog database partition number = -1
 Alternate server hostname =
 Alternate server port number =

qingsong@db2c:~$ db2 "connect to SOURCEDB user inst105 using inst105"
qingsong@db2c:~$ db2 "select * from ta"

ID           NAME
----------- --------------------
100        aaa1
200        aaa2

2 record(s) selected.

qingsong@db2c:~$ db2 terminate
qingsong@db2c:~$ db2 "connect to TARGETDB user inst105 using inst105"
qingsong@db2c:~$ db2 "select * from tb"

ID            NAME
----------- --------------------
100        bbb1

1 record(s) selected.

qingsong@db2c:~$ db2 terminate

接下来开始load操作，先连接到targetdb，再声明了一个SOURCEDB上的cursor，等load的时候却报错 SQL1013N，说SOURCEDB不存在：
qingsong@db2c:~$ db2 "connect to TARGETDB user inst105 using inst105"
qingsong@db2c:~$ db2 "DECLARE mycurs CURSOR DATABASE SOURCEDB user inst105 using inst105 FOR SELECT id, name FROM inst105.ta"
qingsong@db2c:~$ db2 "LOAD FROM mycurs OF cursor INSERT INTO inst105.tb"
SQL1013N The database alias name or database name "SOURCEDB" could not be
found. SQLSTATE=42705
qingsong@db2c:~$ db2 "LOAD CLIENT FROM mycurs OF cursor INSERT INTO inst105.tb"
SQL1013N The database alias name or database name "SOURCEDB" could not be
found. SQLSTATE=42705

问题分析

1. 首先看一下是不是sourcedb上操作cursor的问题。

在sourcedb上声明一个cursor，open、fetch、close操作都能正常进行，于是排除.
qingsong@db2c:~$ db2 connect to sourcedb user inst105 using inst105
qingsong@db2c:~$ db2 +c "declare c1 cursor for select * from ta"
qingsong@db2c:~$ db2 +c "open c1"
qingsong@db2c:~$ db2 +c "fetch c1"

ID            NAME
----------- --------------------
100        aaa1

1 record(s) selected.

qingsong@db2c:~$ db2 +c "fetch c1"

ID            NAME
----------- --------------------
200        aaa2

1 record(s) selected.

qingsong@db2c:~$ db2 +c "fetch c1"

ID             NAME
----------- --------------------

0 record(s) selected.

qingsong@db2c:~$ db2 +c "close c1"
qingsong@db2c:~$ db2 commit
qingsong@db2c:~$ db2 terminate

2. 再看一下是不是targetdb上load的问题

直接对targetdb进行远程的load client是没有问题的。
qingsong@db2c:~$ db2 "connect to targetdb user inst105 using inst105"
qingsong@db2c:~$ db2 "export to t.del of del select * from tb"
qingsong@db2c:~$ db2 "load client from /home/qingsong/t.del of del insert into tb"

3. 看起来即不是cursor本身的问题，也不像load本身的问题，那就是load cursor的问题了。

于是查信息中心load命令中关于cursor的介绍，发现如下说明：

CLIENT
Specifies that the data to be loaded resides on a remotely connected client. This option is ignored if the load operation is not being invoked from a remote client. This option is ignored if specified in conjunction with the CURSOR file type.

看到这里就明白了，当您 在client端发出load命令时，实际上数据要求是存放在db2 server端的，并且命令是在db2 server端执行的，除非显式地指定CLIENT选项。当使用cursor方式时，如果指定了client，client会被自动忽略。也就是load cursor都是在db2 server (db2b)上进行的， 因为db2b数据库目录里没有SOURCEDB，所以报出SQL1013N的错误。

解决方法

在db2b上将source数据库编目，编目后的名子一定要与DECLARE CURSOR时指定的数据库名一致，问题解决
inst105@db2b:~$ db2 catalog TCPIP node nodedb2a remote db2a server 61100
inst105@db2b:~$ db2 catalog db source as sourcedb at node nodedb2a
inst105@db2b:~$ db2 terminate
inst105@db2b:~$ db2stop
inst105@db2b:~$ db2start

https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008305.html