DB2 ODBC connection Tracelevel

Posted on 2006-05-11
Last Modified: 2008-03-03

I am really not familiar with UDB, but recently am having issue with client application written in VB that will connect to UDB database.... So need somebody's help!

Actually that VB app will just through SQL to UDB dataserver to retrieve data (just select statement) and then write that into flat text file.

Normally this task finish quickly like in few seconds, but once a two-three weeks, suddenly ODBC timeout happens and it fails.
At that time, if I try to use different PC, no issues can be seen.... Also no special errors are available on dataserver side it seems...

Assuming this is PC side whatever the issue, but cannot point out what is exactly wrong. So tried to turn on trace option in ODBC property dialog on Client XP PC and tried to see errors in that, but seems that, it is not writing anything into log even if there is some event (such as logon to dataserver, execute select statement etc...)..

So assuming there will be some options to generate ODBC logs more or having something odbctrace.dll thing that can be used for this kind of debugging...

If somebody have good tool/way of debugging for this case, would you please let me know?

Thanks Regards,
Sleeping Luke

Question by:sleepingluke
    LVL 7

    Expert Comment

    look for db2diag.log file on both sides (server & client).

    LVL 13

    Expert Comment

    probably a lock timeout issue, when you run the sql statement, another user is holding non compatible locks for your statement. Try running the statement with the UR qualifier if its safe for you to read rows that might be changing
    db2 select something from table WITH UR
    when you say 'it fails', what exactly do you mean, how do you know it has failed? The connection abends? The connection terminates normally with no data written? What happens exactly?

    Author Comment

    Thank you very much for your comment, I can see that log on my PC but not on the PC which was encountered problem, how I can enable logging on that PC? is it from ODBC tracing...? may be not...

    Thank you very much for your comment, actually we are just using application written with visual basic 6, and that application shows error dialog when it stucked (there is error trap though it does not show useful info for now.)

    And it is not writting but just running select statement to read info from table, though not small one, so during the time when it reads data from database, it stucked and seems timed out... (application suddenly started thinking something - waiting for callback from dataserver - and timed out)

    for the UR thing, shall I just put "with UR" after the SQL?
    Sorry but what does it do?

    Thanks Regards,
    LVL 13

    Accepted Solution

    if its reading a large table, then probably you are encountering lock issue (another user is holding a row lock on same table which prevents select statement from completing because it cant read the locked row)

    the UR qualifier at the end of your select statement means read the rows in isolation mode 'UNCOMMITTED READ', that is to say, read all rows that are changing regardless of whether the change has been committed. This is simply an easy way to see if your problem is with row lock contention. There are other ways to determine if you are running into lock contention problem, but this suggestion is quick and dirty way.

    To invoke UR mode, simply add WITH UR at the end of your select statement.
    You can also modify your VB application to return the sql code from dataserver when it times out, this is by far the easiest and most efficient way to resolve these types of problems

    Author Comment

    thank you very much, problem sorted at last with using with UR !!!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Suggested Solutions

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Sending a Secure fax is easy with eFax Corporate ( First, Just open a new email message.  In the To field, type your recipient's fax number You can even send a secure international fax — just include t…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now