Long running query

[jpotucek]

DB2 on Windows V 9.5 FIXPACK1 enterprise edition

We have a report that has been running WAY too long (according to the programmer)

Used to run an hour and all of a sudden it is running about 2 1/2 hours.

what is the best way to troubleshoot this? I'm just learning UDB. Is there a trace function that I can run to capture the SQL as the report runs? What other options for troubleshooting this are there? Any help would be greatly appreciated.

 

[v0520]

One way is to use explain plans.
There is a free tool called visual explain on ibm website where you can explain a query and it will visually create a graph showing what indexes are being used (if any) as opposed to tablespace scans etc..)

Also, look into runstats, if it used to run fine and all of a sudden taking 2.5 hours (and assuming no other code or indexes got changed), sometimes you need to update the database stats so the indexes can be appropriately tuned up by the db2 query optimizer.

Also, new versions of ibm db2 control center may have similar functionality built-in as the visual explain product I was describing.

Gilbert M. Vanegas
Senior Programmer Analyst
County of San Bernardino - ISD

 

[jpotucek]

Thank you !!! I do run RUNSTATS every day. This is a Datamart and it is loaded every day and we have runstats built into the cycle.

I didn't know about visual explain.. i will check it out..

Really the problem is though I don't exactly know what SQL might be choking. This is a webfocus reporting job and it probably contains more than one complicated queries.

Is there anyway to have them run the job and then start a trace or something to try and capture all the SQL?

 

[Truusvlugindewind]

Same problem. We went from 8.2(32bit) to 9.5.2a(64bit) on win2003. Great improvements, but some queries choke. I'm still investigating... my "suspects" are more than 1 "left join" in a single query. Can you acknowledge that?

 

[jpotucek]

still trying to get my hands on the actual SQL. I will surely let you know !!!

 

[v0520]

You can do odbc traces by adding entries in the db2cli.ini file.

Alternatively, if its running on a pc workstation, you can try the odbc trace option in control panel, that works but it puts out huge trace files. but, you should be able to get the sql out of the trace.

If its running on some sort of mainframe server, the easiest way is to simply look at the source code.


Gilbert M. Vanegas
Senior Programmer Analyst
County of San Bernardino - ISD