Hi
i want to use 'WITH UR' option in a select query.
actually the query is causing a deadlock or timeout.
the following error is coming
SQL0913N Unsuccessful execution caused by deadlock or timeout. Reason code
"". SQLSTATE=57033

is this the correct way of using WITH UR

select c, d from mno , uvw where mno.a=uvw.b
WITH UR;

the db is db2V8.1 fp 14 solaris
any help would be appreciated.

Dead lock mean two applications(SQL) are trying to use same information (tables or rows). You got find out why other SQL or application holding that long. Yes you can use with ur (UNCOMMITED READ) for select stament.

try

Select .... FOR READ ONLY WITH UR

Regards

Harikumar

set the following registry variables

DB2_EVALUNCOMMITTED=YES
DB2_SKIPINSERTED=YES

that should help. u can search for these if u want more info

but please post ur LOCKLIST, MAXLOCKS and LOCKTIMEOUT parameter and keep taking some snapshots to know what actually happening

And were your files schema-bound after running the fix pack?

Hi,

This may help:
db2 " ? SQL0913N"


SQL0913N Unsuccessful execution caused by deadlock or timeout.
Reason code "<reason-code>".

Explanation:

The request issued was involved in an unresolved contention for
use of an object and the execution failed.

The reason codes are as follows:


2 transaction branch failed due to deadlock.

68 transaction branch failed due to lock timeout.

72 transaction rolled back due to an error concerning a DB2 Data
Links Manager involved in the transaction.

80 statement failed due to timeout.



User Response:



o For reason code 80, you can retry the failed statement
without terminating the application. If the application
accesses multiple remote databases, it may be better to
rollback the transaction to avoid the possibility of a global
deadlock.

o For other reason codes, issue a request to rollback the
transaction. The transaction cannot be committed due to the
failure of the current transaction branch.

o To help avoid deadlock or lock timeout, issue frequent COMMIT
operations, if possible, within long-running applications or
applications requiring data with high concurrent access.



sqlcode : -913

sqlstate : 57033

When you try:
SELECT c,d
FROM mno , uvw
WHERE mno.a = uvw.b
WITH UR;

and add "FETCH FIRST 1
ROW ONLY"

does it work?

add "FETCH FIRST 1 ROWS ONLY"

select .... FETCH FIRST n ROWS ONLY FOR READ ONLY WITH UR