IBM Support

Slow connect with Transparent LDAP

Troubleshooting


Problem

Connecting to DB2 database with user that belongs to many groups is very slow.

Symptom

db2 "connect to <db name> user <user id>" takes several seconds.

Cause

Slow down in operating system APIs related to authentication.

Environment

Transparent LDAP

Diagnosing The Problem

With Transparent LDAP, authentication is performed outside of DB2. Verify by running the stand-alone test program from technote Non-local accounts return SQL30082N rc=24, SQL1092N, SQL0551N with timing enabled to determine how fast the operating system APIs are responding.

In example below, Linux operating system has been configured to authenticate against Windows Active Directory but the user belongs to more than 100 groups, thus taking 29 seconds to go through all of the groups.

# ./linuxTransLdap -u db2user -p mypassword -t   
linuxTransLdap Version: 2                                         
main() start timestamp : Thu Jan 26 12:03:40 2018                 
Starting getting groups for user db2user.                         
getpwnam_r( db2user ) start timestamp : Thu Jan 26 12:03:40 2018  
getpwnam_r() end timestamp : Thu Jan 26 12:03:40 2018             
                                                                  
getgrouplist() start timestamp : Thu Jan 26 12:03:40 2018         
getgrouplist() end timestamp : Thu Jan 26 12:03:41 2018           
                                                                  
getgrouplist returned 100 groups.                                 
990800513, 990900399, 990980809, 990910182, 1322035361, 990894142,

(snip)

gid = 990877734, group name = group2                             
getgrgid_r( 990842068 ) start timestamp : Thu Jan 26 12:03:48 2018
getgrgid_r() end timestamp : Thu Jan 26 12:03:48 2018             
                                                                  
gid = 990842068, group name = group3                             
Finish.                                                           
main() end timestamp : Thu Jan 26 12:04:10 2018

Resolving The Problem

Root cause is external to DB2 and outside of DB2's control. Work with your system administrator to resolve the performance problem.

On Linux try setting sssd.conf parameter ignore_group_members=true then restart SSSD service.

If the stand-alone test program runs quickly, the slowdown could be inside DB2. Collect trace and open a case with DB2 Support team.

1) db2trc on -t -Madd SQLEX -Madd SQLO -f trace.dmp                
2) Repro the slow connect: db2 "connect to <db name> user <userid>"
(You will be prompted for password)                             
3) db2trc off                                                      
4) db2trc fmt trace.dmp trace.fmt                                  
5) db2trc flw -t trace.dmp trace.flw                               
6) db2trc fmt -c trace.dmp trace_drda.fmt                         

With newer releases of DB2 v10.5 there is support for performance report via -perfrep option.

1) Trace to circular memory buffer instead of a file to reduce impact of trace:                             
db2trc on -t -l 512m -Madd SQLEX -Madd SQLO                                                
                                                                     
Note: This defaults to 32MB memory buffer instead of 512 MB          
Unless db2set DB2TRC_DEF_BUFFSIZE=1024M is set, but this requires DB2 instance restart                                                     
                                                                     
2) Recreate the problem                                                                                                               
db2 "connect to <db name> user <userid>"                                              
                                                                     
3) Dump the trace from memory to disk                                
db2trc dmp trace.dmp                                                 
                                                                     
4) Format the trace                                                  
db2trc perfrep -g trace.dmp trace.perfrep                            
                                                                     
5)  This must be last step otherwise db2trc perfrep will fail if         
trace is stopped before then                                         
db2trc off                                                           
                                                                     
                              

 

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF033","label":"Windows"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Product Synonym

db2

Document Information

Modified date:
07 August 2018

UID

ibm10720171