DB2 Client端export报错：SQL0552N "DB2CLNT" does not have the privilege to perform operation "BIND".

问题描述

新安装的db2 client端执行export操作时报错：SQL0552N  "DB2CLNT" does not have the privilege to perform operation "BIND". 其中client端版本为10.5.10， db2 server版本为10.5.8

 

解决过程

尝试重现问题
在db2 10.5.8 server上创建数据库sample和表t1，并给普通用户db2clnt赋予表t1的select权限。 发现该普通用户db2clnt：
1. 在db2 server上可以export
2. 在db2 10.5.10 client上会报错SQL0552N  "DB2CLNT" does not have the privilege to perform operation "BIND".  
3. 在db2 10.5.8 client上可以export 

以下是重现问题及解决的过程：

0. 实例用户创建数据库SAMPLE和表t1
db2inst1@NODE01:~> whoami
db2inst1
db2inst1@NODE01:~> db2level
DB21085I  This instance or install (instance name, where applicable: "db2inst1") 
uses "64" bits and DB2 code release "SQL10058"
Informational tokens are "DB2 v10.5.0.8", ... and Fix Pack 
"8".
Product is installed at "/opt/IBM/DB2/10.5.8".

db2inst1@NODE01:~> db2 "create db sample"
db2inst1@NODE01:~> db2 "connect to sample"
db2inst1@NODE01:~> db2 "revoke createtab,bindadd on database from public"
db2inst1@NODE01:~> db2 "create table t1(id int, name char(10))"
db2inst1@NODE01:~> db2 "insert into t1 values(100,'aaa'),(200,'bbb')"
db2inst1@NODE01:~> db2 "grant select on t1 to user db2clnt"
db2inst1@NODE01:~> db2 "terminate"

1. 在SERVER上使用 db2clnt 尝试 export，没问题：
db2inst1@NODE01:~> db2 connect to sample user db2clnt
Enter current password for db2clnt: 

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.8
 SQL authorization ID   = DB2CLNT
 Local database alias   = SAMPLE

db2inst1@NODE01:~> db2 "export to /tmp/t1.del of del select * from db2inst1.t1"
SQL3104N  The Export utility is beginning to export data to file 
"/tmp/t1.del".

SQL3105N  The Export utility has finished exporting "2" rows.

Number of rows exported: 2
  
=============
2. 在db2 10.5.10的db2 client上可以查询数据，但不能Export:
db2clnt@NODE01:~> db2level
DB21085I  This instance or install (instance name, where applicable: "db2clnt") 
uses "64" bits and DB2 code release "SQL1005A" with level identifier 
"060B010E".
Informational tokens are "DB2 v10.5.0.10", "s180615", "IP24053", and Fix Pack 
"10".
Product is installed at "/opt/IBM/DB2/10.5.10".

db2clnt@NODE01:~> db2licm -l
Product name:                     "IBM Data Server Client"
Product identifier:               "db2client"
Version information:              "10.5"

db2clnt@NODE01:~> db2 catalog tcpip node node01 remote 192.168.75.130 server 60010
db2clnt@NODE01:~> db2 catalog database sample as sample at node node01
db2clnt@NODE01:~> db2 terminate
db2clnt@NODE01:~> db2 "connect to sample user db2clnt"
Enter current password for db2clnt: 

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.8
 SQL authorization ID   = DB2CLNT
 Local database alias   = SAMPLE

 
db2clnt@NODE01:~> db2 "select * from db2inst1.t1"

ID          NAME      
----------- ----------
        100 aaa       
        200 bbb       

  2 record(s) selected.
 

db2clnt@NODE01:~> db2 "export to t1.del of del select * from db2inst1.t1"
SQL3104N  The Export utility is beginning to export data to file "t1.del".

SQL3015N  An SQL error "-552" occurred during processing.

SQL0552N  "DB2CLNT" does not have the privilege to perform operation "BIND".  
SQLSTATE=42502

SQL27981W  The utility could not verify presence of attached or detached data 
partitions in the target table or the source table.

3. 
在db2 10.5.8  client上也可以export 

NODE01:/opt/IBM/DB2/10.5.8/instance # su - db2clnt2
db2clnt2@NODE01:~> db2level
DB21085I  This instance or install (instance name, where applicable: "db2clnt2") 
uses "64" bits and DB2 code release "SQL10058" with level identifier 
"0609010E".
Informational tokens are "DB2 v10.5.0.8", "s160901", "IP23993", and Fix Pack 
"8".
Product is installed at "/opt/IBM/DB2/10.5.8".

db2clnt2@NODE01:~> db2 get dbm cfg | more

          Database Manager Configuration

     Node type = Client

db2clnt2@NODE01:~> db2 catalog tcpip node node01 remote 192.168.75.130 server 60010
db2clnt2@NODE01:~> db2 catalog database sample as sample at node node01
db2clnt2@NODE01:~> db2 terminate
db2clnt2@NODE01:~>  db2 "connect to sample user db2clnt"
Enter current password for db2clnt: 

   Database Connection Information

 Database server        = DB2/LINUXX8664 10.5.8
 SQL authorization ID   = DB2CLNT
 Local database alias   = SAMPLE

db2clnt2@NODE01:~> db2 "select * from db2inst1.t1"

ID          NAME      
----------- ----------
        100 aaa       
        200 bbb       

  2 record(s) selected.

db2clnt2@NODE01:~> db2 "export to /tmp/t11.del of del select * from db2inst1.t1"
SQL3104N  The Export utility is beginning to export data to file 
"/tmp/t11.del".

SQL3105N  The Export utility has finished exporting "2" rows.

Number of rows exported: 2

4.

解决的方法是在CLIENT上使用有BIND权限的用户显式地绑定相关的包：

db2clnt@NODE01:~> db2 connect to sample user db2inst1
db2clnt@NODE01:~> cd sqllib/bnd
db2clnt@NODE01:~/sqllib/bnd> db2 "bind @db2ubind.lst grant public"
db2clnt@NODE01:~/sqllib/bnd> db2 "bind @db2cli.lst grant public"
db2clnt@NODE01:~/sqllib/bnd> db2 terminate
db2clnt@NODE01:~/sqllib/bnd> cd
db2clnt@NODE01:~> db2 connect to sample user db2clnt
db2clnt@NODE01:~> db2 "export to t1.del of del select * from db2inst1.t1"
SQL3104N  The Export utility is beginning to export data to file "t1.del".

SQL3105N  The Export utility has finished exporting "2" rows.

Number of rows exported: 2

或者先直接使用有权限的用户export一把，它会隐式地绑定所需要的包，再使用 db2clnt export 就可以了。
再或者，直接给用户赋予bindadd的权限。