Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Apr 2012
    Posts
    12

    Unanswered: DB2 UDB upgrade leads to excel odbc error

    I had been using IBM DB2 Development Center v8.1 FP7 and Excel 2007. Then I was able to connect to the DB2 database through the Excel Data Connection wizard (Data -> From other source -> From Data connection wizard -> Other/Advance -> Then by selecting DB2 driver and existing database connection). After upgrading to IBM DB2 Dev Center v8.1 FP18, the above data connection method is failing with Excel hanging and closing.

    Even the earlier executing Excel macro to connect to DB2 is failing after the upgrade with Excel/Visual Basic Run-time error '-2147417848 (80010108)' Automation Error The object invoked has disconnected from its clients.. The VBA code on which it fails is .Refresh BackgroundQuery:=False.

    Please assist.

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Look under the covers to see what's happening.
    Take a CLI trace and search in there for the real error .
    Also you can do basic checks like checking if other ODBC connections work or not (or give a more detailed error message or code).

  3. #3
    Join Date
    Apr 2012
    Posts
    12
    Also, I tried other ODBC connections, and all are failing for the same reason.

    I tried enabling the CLI trace. Attached it here. I am not able to understand it.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    no error in this file
    must be at another level... os... drivers.....
    have you tried a native db2 connect to this db ?
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The trace does not show any *connect* to any database, as far as I can see, and that is what we need.
    Are you sure you took the trace correctly, and collected the whole file?
    What entries did you add to the db2cli.ini ?
    If you have the full DB2-client installed on the workstation, what information do you get from db2ca ?

  6. #6
    Join Date
    Apr 2012
    Posts
    12
    Quote Originally Posted by przytula_guy View Post
    no error in this file
    must be at another level... os... drivers.....
    have you tried a native db2 connect to this db ?
    I am able to connect to DB2 through Command Center, Contol Center and Development Center. I am not aware of native db2 connect. Please assist.

  7. #7
    Join Date
    Apr 2012
    Posts
    12
    Quote Originally Posted by db2mor View Post
    The trace does not show any *connect* to any database, as far as I can see, and that is what we need.
    Are you sure you took the trace correctly, and collected the whole file?
    What entries did you add to the db2cli.ini ?
    If you have the full DB2-client installed on the workstation, what information do you get from db2ca ?
    The following entries have been made to db2cli.ini file for enabling trace.
    [COMMON]
    Trace=1
    TracePathName=C:\temp\trace
    TraceComm=1
    TraceFlush=1
    TraceTimeStamp=1

  8. #8
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by nmr4u View Post
    I had been using IBM DB2 Development Center v8.1
    Upgrade to db2 v10? In that case you cannot use your client v8 any more.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  9. #9
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    The successful connections from Command-Centre, Control-Centre, Development-centre are probably all or JDBC connections (not ODBC). Please run db2ca, then Selected --> Test connection, choose connection-type ODBC only (de select the others) and try to connect. If the ODBC connection works correctly in db2ca from the same workstation where Excel is failing then your ODBC setup is fine. You have to look under the covers of what Excel/VBA is doing to see what the real error is.
    You probably realise that you are using a very outdated (out of support) DB2 client version and you should also indicate if *anything else* changed (for example, did anything change at the db2 server side).

  10. #10
    Join Date
    Apr 2012
    Posts
    12
    Quote Originally Posted by db2mor View Post
    The successful connections from Command-Centre, Control-Centre, Development-centre are probably all or JDBC connections (not ODBC). Please run db2ca, then Selected --> Test connection, choose connection-type ODBC only (de select the others) and try to connect. If the ODBC connection works correctly in db2ca from the same workstation where Excel is failing then your ODBC setup is fine. You have to look under the covers of what Excel/VBA is doing to see what the real error is.
    You probably realise that you are using a very outdated (out of support) DB2 client version and you should also indicate if *anything else* changed (for example, did anything change at the db2 server side).
    I am able to connect through db2ca ODBC.

    Nothing other than the DB2 version change on my PC has been done. I am able to connect to the database on another PC using FixPak7.

  11. #11
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    So you have proved that the basic ODBC connection works fine, but your Excel/VBA code has a problem.

    Is your Excel/Office-2007 at the most recent maintenance level also ?

    (The DB2 fixpack 18 dates from August-2009, and ODBC versions do change over time, which can cause compatibility issues).

    If you are not at the last maintenance release of Office-2007 maybe you could eliminate this as a cause on one test workstation.


    Note: I'm able to use antique Excel-97 (fix pack 2) to access a DB2 v8.1 fixpack13 database without problems. I will upgrade the DB2 to fixpack 18 to see if it breaks.

  12. #12
    Join Date
    Apr 2012
    Posts
    12
    Quote Originally Posted by db2mor View Post
    Is your Excel/Office-2007 at the most recent maintenance level also ?
    The version of Office is 12.0.4518.1014 and it looks to be the latest Office 2007 version.

  13. #13
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    pd/psi is excellent for many people ..

    why did you upgraded the old db2 version - if no real need - keep this version/fp
    dbserver level ??
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  14. #14
    Join Date
    Apr 2012
    Posts
    12
    Quote Originally Posted by przytula_guy View Post
    pd/psi is excellent for many people ..

    why did you upgraded the old db2 version - if no real need - keep this version/fp
    dbserver level ??
    I wanted to solve the error JR20884.

  15. #15
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    I upgraded from v8.x fixpack 13 to v8.x fixpack 18, rebound all stuff with db2ca

    I am successfully able to use Excel-97 'get external data' via the ODBC driver to fetch data without problems from the same DB2 database as before.

    Note: I bound db2schema.bnd independently.

    If you did not already bind that one, do it and retry.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •