IBM Support

Troubleshooting DB2 connection delays

Troubleshooting


Problem

This document will help you learn the methods used to troubleshoot common connection delays.

Resolving The Problem



Troubleshooting at the Client

Start troubleshooting a "connection delay" at the Client attempting to connect. The first thing to do is to attempt to connect from the DB2 CLP
    db2 connnect to <dbalias> user <userid>

If the connection is established without a delay, then the 'connection delay' must be inside the Client Application experiencing the delay. It is likely that as part of its connection routine, the application is issuing a query after the successful connect; and it is that query that is performing slowly. Unfortunately troubleshooting a slow query is beyond the scope of this technote.

If connecting from a DB2 client on a Windows platform, the 'connection delay' may be caused by not specifying an authentication type in the database catalog. This is the default, and it means that the Client will negotiate with the DB2 Server to use the correct authentication method. To check if this is the case, review the Database directory
    db2 list db directory

If the Authentication method is not specified, the output will look similar to this:
    Database 7 entry:

    Database alias = VMSAMP95
    Database name = SAMPLE
    Node name = NDE4D554
    Database release level = c.00
    Comment =
    Directory entry type = Remote
    Catalog database partition number = -1
    Alternate server hostname =
    Alternate server port number =

To determine the correct Authentication method, logon to the DB2 Server and execute
    db2 get dbm cfg

and review the output; for example:
    Database manager authentication (AUTHENTICATION) = SERVER

To specify the Authentication method - Authentication SERVER in this example:
  1. db2 uncatalog db vmsamp95
  2. db2 catalog db sample as vmsamp95 at node nde4d554 authentication server
  3. db2 terminate

The 'list db directory' output will now report the Authentication method
    Database 7 entry:

    Database alias = VMSAMP95
    Database name = SAMPLE
    Node name = NDE4D554
    Database release level = c.00
    Comment =
    Directory entry type = Remote
    Authentication = SERVER
    Catalog database partition number = -1
    Alternate server hostname =
    Alternate server port number =



· Troubleshooting at the Network
    Check to see whether the network is busy. Please see the ‘tuning considerations’ link below or contact your network administrator.


· Troubleshooting at the Server
    If connecting to a DB2 server on the AIX platform, the cause of the connection delay could be due to slow authentication. By default on AIX, the authentication is performed by calling the GETGRENT function that returns all the groups that the user ID belongs to on the OS. However, a faster way to perform the group lookup is to call the GETGRSET function. You can tell the DB2 server to use this function instead of GETGRENT by setting the DB2_ALTERNATE_GROUP_LOOKUP registry variable. You can run the following command in the DB2 Command prompt to do so:

    db2set –i DB2_ALTERNATE_GROUP_LOOKUP=GETGRSET
    You should recycle the DB2 instance after setting this registry variable for changes to take effect

    On your operating system, check the number of groups that exist on the server. During the authentication process, the user ID specified during the connection will be checked to see which groups it belongs to at the server. If there are many groups to enumerate, it will take more time for the group lookup to complete.

    On your operating system, check the number of users that exist in the same group as the user ID specified in the connection. If there are many users, it will take more time to go through all these users before finding a match.

    Check your operating system authentication configuration since DB2 calls OS functions to perform authentication. For example, if connecting to an external NIS (Network Information Service) server or LDAP (Lightweight Directory Access Protocol) server to perform authentication, the slow connection to these external servers could be a factor.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Connectivity - Other","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;10.1;10.5","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSEPDU","label":"Db2 Connect"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":" ","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.7;9.5;9.1","Edition":"DB2 Connect Application Server Edition;DB2 Connect Enterprise Edition;DB2 Connect Personal Edition;DB2 Connect Unlimited Edition for System i;DB2 Connect Unlimited Edition for System z","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21455469