Lock Hints for AS400 SQL

[ralphwiggum]

Does anyone know of a way to read past locked rows in table? I come from a MS SQL Server background and in that environment you could add a lock hint after the select to ignore locked rows, such as:
SELECT * FROM TABLE WITH(READPAST)

Just wondering if anyone knows of an equivalant functionality in AS400 world or a workaround to achieve this. Thanks in advance for your help

 

[blom0344]

Just a wild guess:

SELECT * FROM TABLE FOR FETCH ONLY;

or

SELECT * FROM TABLE FOR READ ONLY;

T. Blom
Information analyst
tbl@shimano-eu.com

 

[legacy370]

In our environment, which is DB2 version 7 on OS/390, we add the phrase WITH UR to the end of the SQL statement. UR means uncommitted read. This is the so-called "dirty read," that gives you data, even if that page is locked.

 

[ralphwiggum]

Legacy370,
I can't use that level of transaction isolation b/c I don't want to see uncommitted rows in case they're rolled back, I really just want to see commited records and read past rather than wait for the lock to release for any rows that are being updated or not commited for some other reason. Any ideas? Thank you.