Topic
  • 8 replies
  • Latest Post - ‏2012-09-04T22:15:32Z by SystemAdmin
fshaikh
fshaikh
2 Posts

Pinned topic DB2 Data Provider for .NET DB2Connection open() too slow

‏2010-07-07T12:11:21Z |
Hi,

I am using DB2 .NET provider and it takes 20 - 30 seconds to establish a connection to the database using the following code:

DB2Connection conn = new DB2Connection(ConnectionString);
mySqlConnection.Open(); <=== This line is taking 20 - 30 seconds to execute

My Database version is DB2/AIX64 8.2.8.

It's a web application and connection pooling doesn't seem to be working as for each web user it takes 20 - 30 seconds just to establish the connection.

Any help will be much appreciated.
Best regards,
F
Updated on 2012-09-04T22:15:32Z at 2012-09-04T22:15:32Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2010-07-07T15:08:45Z  
    Hi,

    I am assumming that mySqlConnection.Open(); is a typo on your part, as the connection variable you're creating above is called "conn".
    What kind of DB2 client are you running your application from? If it is a DB2 Runtime client, can you please try a DB2 CLP connection to the server and see how long it takes to establish. Also, could you please provide the output of the testconn20 run against your server.

    Thanks,
    Alex
  • fshaikh
    fshaikh
    2 Posts

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2010-07-07T17:41:16Z  
    Hi,

    I am assumming that mySqlConnection.Open(); is a typo on your part, as the connection variable you're creating above is called "conn".
    What kind of DB2 client are you running your application from? If it is a DB2 Runtime client, can you please try a DB2 CLP connection to the server and see how long it takes to establish. Also, could you please provide the output of the testconn20 run against your server.

    Thanks,
    Alex
    Many thanks for the reply Alex,

    My client machine is running on Windows 2000 Server with IBM Data Server Client version 9.1.700.855 installed

    From command line (CLP) it's also taking about 20 - 30 seconds to establish a connection using the following command,

    db2 connect to mydb user testuser

    And below is the testconn20 result:

    C:\Program Files\IBM\SQLLIB\BIN>testconn20.exe database=DBNAME;server=SERVER:PORT;userid=USER;password=PWD
    Step 1: Printing version info
    .NET Framework version: 2.0.50727.832
    DB2 .NET provider version: 9.0.0.2, file version: 9.1.7.2
    Capability bits: ALLDEFINED
    Build: 20090308
    Factory for invairant name IBM.Data.DB2 verified
    Elapsed: 3.4219845

    Step 2: Connecting using "database=DBNAME;server=SERVER:PORT;userid=USER;password=PWD"
    Server type and version: DB2/AIX64 08.02.0008
    Elapsed: 32.469789

    Step 3: Selecting rows from SYSIBM.SYSTABLES to validate existance of packages
    SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
    Elapsed: 0.2343825

    Step 4: Calling GetSchema for tables to validate existance of schema functions
    Elapsed: 0.375012
    Test passed.

    C:\Program Files\IBM\SQLLIB\BIN>
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2010-07-07T18:35:30Z  
    • fshaikh
    • ‏2010-07-07T17:41:16Z
    Many thanks for the reply Alex,

    My client machine is running on Windows 2000 Server with IBM Data Server Client version 9.1.700.855 installed

    From command line (CLP) it's also taking about 20 - 30 seconds to establish a connection using the following command,

    db2 connect to mydb user testuser

    And below is the testconn20 result:

    C:\Program Files\IBM\SQLLIB\BIN>testconn20.exe database=DBNAME;server=SERVER:PORT;userid=USER;password=PWD
    Step 1: Printing version info
    .NET Framework version: 2.0.50727.832
    DB2 .NET provider version: 9.0.0.2, file version: 9.1.7.2
    Capability bits: ALLDEFINED
    Build: 20090308
    Factory for invairant name IBM.Data.DB2 verified
    Elapsed: 3.4219845

    Step 2: Connecting using "database=DBNAME;server=SERVER:PORT;userid=USER;password=PWD"
    Server type and version: DB2/AIX64 08.02.0008
    Elapsed: 32.469789

    Step 3: Selecting rows from SYSIBM.SYSTABLES to validate existance of packages
    SELECT * FROM SYSIBM.SYSTABLES FETCH FIRST 5 rows only
    Elapsed: 0.2343825

    Step 4: Calling GetSchema for tables to validate existance of schema functions
    Elapsed: 0.375012
    Test passed.

    C:\Program Files\IBM\SQLLIB\BIN>
    Since your CLP connection takes just as long to astablish as the .NET one, this is not a .NET issue. It could be that the server is overloaded and is taking a long time to start up an agent to process your connection request, or it could be a network performance/integrity issue, or a combination of the two. My suggestion would be to contact your database and/or network administrator to help you diagnose the problem.
  • S74C_Falk_Seifert
    S74C_Falk_Seifert
    1 Post

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2012-06-20T05:44:31Z  
    Since your CLP connection takes just as long to astablish as the .NET one, this is not a .NET issue. It could be that the server is overloaded and is taking a long time to start up an agent to process your connection request, or it could be a network performance/integrity issue, or a combination of the two. My suggestion would be to contact your database and/or network administrator to help you diagnose the problem.
    Hello,

    i have the same problem. Connecting via DB2Connection to the database takes about half a minute.
    When I use DBVisualizer which utilizes JDBC to connect, its dramatically faster - abot 1 second...

    Regards
  • TapanKumarGhosh
    TapanKumarGhosh
    10 Posts

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2012-07-04T10:08:35Z  
    Hello,

    i have the same problem. Connecting via DB2Connection to the database takes about half a minute.
    When I use DBVisualizer which utilizes JDBC to connect, its dramatically faster - abot 1 second...

    Regards
    What is the difference in connection details given in DBVisualizer and DB2Connection.

    There could be many reasons for DB2 connection to take long time, please ensure
    1) You do not have any Network related issue.
    2) DB2 server is not overloaded.
    3) Try to connect with DB2 CLP connection to the server and check how long it takes to establish the connection.
    4) Additionally, when we try a simple scenario as below, it returns in less than 1 second. Please provide more details if you are trying something different.

    DB2Connection conn = new DB2Connection();
    conn.ConnectionString = "Database=sample; Server=<*************>:<50000>; User Id=<userid>; Password=<password>";
    conn.Open();
    Console.WriteLine("Connection opened successfully");
    conn.Close();
  • PWPN_Andreas_Manz
    PWPN_Andreas_Manz
    1 Post

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2012-08-03T09:59:17Z  
    Any Answers?

    I have the same problem. I have no network issues. Database is DB2LUW 9.7FP4 64Bit and DotNet-Provider Version 9.7.4.4.
    Also by me open takes about 30s.
    Other Tools with jdbc-Driver have no problems.
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2012-09-04T04:49:31Z  
    Any Answers?

    I have the same problem. I have no network issues. Database is DB2LUW 9.7FP4 64Bit and DotNet-Provider Version 9.7.4.4.
    Also by me open takes about 30s.
    Other Tools with jdbc-Driver have no problems.
    Hello
    I have also had the same experience.

    When connecting it with DB2 which works on Windows, connection became quick by attaching a domain or a server name to userid.

    <slow>
    testconn20.exe database=DB2;server=DB2123:50000;userid=scott;password=tiger

    <quick>
    testconn20.exe database=DB2;server=DB2123:50000;userid=Db2123\scott;password=tiger

    Please try.
  • SystemAdmin
    SystemAdmin
    2826 Posts

    Re: DB2 Data Provider for .NET DB2Connection open() too slow

    ‏2012-09-04T22:15:32Z  
    This problem has been around for a while the way we fixed it was by creating a windows user with the username used in the connection string and adding that user to the DB2ADMINS group.