问题描述

新安装的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的权限。