DB2联邦过程中创建和使用nickname报错 SQL1101N的常见场景

在使用DB2的联邦功能时在创建nickname及使用nickname的时候最常见的报错就是SQL1101N，提示用户名或者密码不对不能访问远程数据源。
下面说一下最常见的原因。

[@more@]

一 创建过程中报错

创建db2的联邦通常包含以下四步：
1 CREATE WRAPPER DRDA
2 CREATE SERVER
3 CREATE USER MAPPING
4 CREATE NICKNAME

本地sample数据库要与远程qa数据库做联邦，qa数据库已经编目到本地，实例参数FEDERATED已开启，实际创建过程如下：

db2inst1@lbdb:~> db2 connect to sample

Database Connection Information

Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = DB2INST1
Local database alias = SAMPLE

db2inst1@lbdb:~> db2 CREATE WRAPPER DRDA
DB20000I The SQL command completed successfully.
db2inst1@lbdb:~> db2 "CREATE SERVER trans TYPE DB2/UDB VERSION '9.7' WRAPPER DRDA AUTHID "userdb" PASSWORD "possword" OPTIONS( ADD DBNAME 'QA', PASSWORD 'Y' )"
DB20000I The SQL command completed successfully.
db2inst1@lbdb:~> db2 "CREATE USER MAPPING FOR "test" SERVER trans OPTIONS ( ADD REMOTE_AUTHID 'userdb', ADD REMOTE_PASSWORD 'possword') "
DB20000I The SQL command completed successfully.
db2inst1@lbdb:~> db2 CREATE NICKNAME shad.employee FOR trans.shad.employee
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL1101N Remote database "QA" on node "" could not be accessed with the
specified authorization id and password. SQLSTATE=08004

问题在哪里呢？
用户名及密码都正确，而且这里也不是密码大小写问题

解决方法：
创建nickname时必须由mapping中定义的数据库用户创建，其他用户不可以，实例用户也不行，除非实例用户在mapping中定义。
我们大多数管理员习惯使用实例用户去创建nickname，如果我们定义的mpping用户与管理用户不一致反而会报错。
如上面例子中mapping定义的用户为“test”，则必须进行下面的操作:

db2inst1@lbdb:~> db2 connect to sample user test using possword

Database Connection Information

Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = TEST
Local database alias = SAMPLE

db2inst1@lbdb:~> db2 CREATE NICKNAME shad.employee FOR trans.shad.employee
DB20000I The SQL command completed successfully.

二 使用nickname（select，insert，delete）

沿用前面的环境
db2inst1@lbdb:~> db2 connect to sample

Database Connection Information

Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = DB2INST1
Local database alias = SAMPLE

db2inst1@lbdb:~> db2 "select * from shad.employee "

SQL1101N Remote database "QA" on node "" could not be accessed with the
specified authorization id and password. SQLSTATE=08004
同样我们更换数据库用户
db2inst1@lbdb:~> db2 connect to sample user test using possword

Database Connection Information

Database server = DB2/LINUXX8664 9.7.4
SQL authorization ID = TEST
Local database alias = SAMPLE

db2inst1@lbdb:~> db2 "select * from shad.employee "
结果就完全正常

总结：
1 创建和使用nickname必须使用mapping中定义的用户
2 如果希望其他用户也能使用nickname必须增加相应的mapping定义

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/598443/viewspace-1057088/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/598443/viewspace-1057088/