I'm playing around with the LDAP security plugin. I noticed that after a certain amount of grants I get an error that the ldap server can't be reached:

SQL30082N  Security processing failed with reason "26" ("UNEXPECTED SERVER 

related entries from db2diag.log

2018-08-16- E650341472E720        LEVEL: Severe
PID     : 87873                TID : 140137426052864 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : WT000N11
APPHDL  : 0-1814               APPID: *LOCAL.db2inst1.180816060533
AUTHID  : DB2INST1             HOSTNAME: nya-50
EDUID   : 581                  EDUNAME: db2agent (NYA) 0
FUNCTION: DB2 UDB, bsu security, sqlex_get_authid_type, probe:300
MESSAGE : ADM13001E  Plug-in "IBMLDAPgroups" received error code "-16" from the 
          DB2 security plug-in API "db2secDoesGroupExist" with the error 
          message "InitLDAP: bind failed rc=81 (Can't contact LDAP server) 

2018-08-16- I650342193E845        LEVEL: Info
PID     : 87873                TID : 140137426052864 PROC : db2sysc 0
INSTANCE: db2inst1             NODE : 000            DB   : WT000N11
APPHDL  : 0-1814               APPID: *LOCAL.db2inst1.180816060533
AUTHID  : DB2INST1             HOSTNAME: nya-50
EDUID   : 581                  EDUNAME: db2agent (NYA) 0
FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:670
          "The server security plugin encountered an unexpected error"
DATA #1 : String, 51 bytes
An error was detected during statement compilation.
DATA #2 : String, 156 bytes
Compiler error stack for rc = -2141453961:

A simple sh script to reproduce the problem:


db2 connect to db
e=$(db2 -x "with t(n) as ( values 1 union all select n+1 from t where n<300 ) select listagg('A' || n,', ') from t")

for t in $(db2 -x "select rtrim(tabschema)||'.'||rtrim(tabname) from syscat.tables where tabschema not like 'SYS%'"); do
        db2 -v "grant select on table $t to group $e"
        if [ $? -ne 0 ]; then
                exit 1
        n=$(expr $n + 1)
exit 0

There's a strong correlation between the number of groups in the grant and the number of iterations before the crash (the numbers is exactly the same every time I run), so I suspect that some resource gets exhausted.

grant select on table Ti to group A1, A2, ...,An

| Number of groups (n) | Iterations before crash (i) |
|                  100 |                         141 |
|                  200 |                          69 |
|                  300 |                          46 |
|                  600 |                          22 |

I can't find any errors in the LDAP-side.

I can repeatably run the test without restarting anything between, and the crash appears at the same iteration, every time.

If I configure Db2 to use two ldaps:

LDAP_HOST = host1 host2

it switches between ldaps when the crash appears, and the test-script continues.

* Edit: Additional observations *

Adding a sleep between each grant increases the number of iterations. For 600 groups:

| Sleep seconds | Iterations before crash  |
|             0 |                      22  |
|             1 |                      28  |
|             2 |                      31  |
|             3 |                   *1166* |

For the 3 second sleep, it ran all 1166 tables successfully. Unfortunately, such sleep won't be a very practical solution.

Any clues anyone?

* Edit: Additional observations 2 *

Running several test-scripts in parallel causes the situation to appear really fast. I suspect now that it is a problem with LDAP rather than with Db2. I do notice that Db2 keep a connection open for some time, so it may help to increase some limit in LDAP. Will check tomorrow.

  • Looks like a case for Db2 technical support. – mustaccio Aug 16 '18 at 12:24
  • I agree, will open a PMR but wanted to check if someone else seen the problem – Lennart Aug 16 '18 at 12:25
  • maybe grant the selects to a role, then grant the role to the group. – Paul Vernon Aug 16 '18 at 12:45
  • Interesting thought, will try. However, this may indicate that there is a problem that will show during heavy load, so I will try to figure out what is going on. – Lennart Aug 16 '18 at 13:12

I never did open an PMR since increasing the number of local ports available to ldap seemed to solve the problem:

sysctl -w net.ipv4.ip_local_port_range="30000 65535"

Also you could decrese timeout value for ports

sysctl -w net.ipv4.tcp_fin_timeout=30

Also using P. Vernons suggestion decreased both the time and the overall resource usage. I.e. instead of:

for t in ${tables}: do
    db2 "grant select on table $t to group g1,g2,...gn"

the following can be used:

db2 "create role ${ROLE}"
db2 "grant role ${ROLE} to group g1,g2,...gn" 
for t in ${tables}: do
    db2 "grant select on table $t to role ${ROLE}"

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.