Connecting to DB2 database with user that belongs to many groups is very slow.
db2 "connect to <db name> user <user id>" takes several seconds.
Slow down in operating system APIs related to authentication.
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,
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
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
07 August 2018