600字范文 > Db2 client上load from cursor报错SQL1013N The database name .. could not be found

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

时间：2019-11-02 20:08:13

问题描述

今天有一个客户问了一个问题：有两台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"

IDNAME

----------- --------------------

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"

IDNAME

----------- --------------------

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"

IDNAME

----------- --------------------

100 aaa1

1 record(s) selected.

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

IDNAME

----------- --------------------

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的错误。