DB2 创建和使用nickname 报错 SQL1101N

vike681

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

一 创建过程中报错
创建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定义