Hi,
I have a peoplesoft application that connects to db2 udb on aix . The app appears to hang - however d/b snapshots show there is database activity but it is painfully slow - i had to kill the thread that was connected to the database eventually after it ran over 5 hours on a single statement . This same app was running in seconds before. Has anyone encountered similar problem ? Can u detail the steps to diagnose this issue ? The statement it hangs on is a select statement but i see xclusive row level locks on the table . Can this issue be related to locking or a memory issue ?

First of all, you should probably make sure that locktimeout is set to a reasonable number, like 60 seconds. The default is -1 which means that an application waiting on a resource that is locked will wait indefinitely. You can check this with a "db2 get db cfg" command.

Next I would try doing some snapshots, like applicaiton snapshot, or database snapshot, or dynamic SQL snapshot.

In the case of a dynamic SQL snapshot, you can see how long the SQL statement takes (total for all executions, and number of executions). Do a visual explain (using Control Center) on any long running SQL statement to see if the relative cost and access plan is reasonable. Snapshots are described in the Command Reference manual, and also the Monitoring guide.

I did the explain and the optimizer is doing a table scan - is it possible that the app could lock itself ? there are X'clusive row level locks on that table even though its basically doing a select .The app is doing a cursor fetch at the point it slows down. Will table level lock help in this case ?

DB2 will start-off with row-level lock, but once you reach the lock limit threshold, the lock will escalate to a table level lock. And its very likely that the table you are scanning is quite large.

As others suggested, perform snapshot when the query is running, perform an explain plan and also try db2advis (useful on 8.2.0+) to get some directions on what you can do to improve performance and avoid the lock escalation.