• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 688
  • Last Modified:

DB2 ODBC connection Tracelevel

Hello,

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

0
sleepingluke
Asked:
sleepingluke
  • 2
  • 2
1 Solution
 
sachinwadhwaCommented:
look for db2diag.log file on both sides (server & client).

0
 
ghp7000Commented:
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?
0
 
sleepinglukeAuthor Commented:
sachinwadhwa-san,
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...


ghp7000-san,
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,
SleepingLuke
0
 
ghp7000Commented:
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
0
 
sleepinglukeAuthor Commented:
thank you very much, problem sorted at last with using with UR !!!
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now