DB2 for fetch only VS with ur

https://www.idug.org/p/fo/et/thread=43184

For Fetch only 告诉DB2你只读数据，不想更新数据

FOR FETCH ONLY tells DB2 that you really are only going to READ the data (and not subsequently going to update it). This allows DB2 to me much smarter about lock avoidance. In fact, I always suggest that it's good practice to specify FOR FETCH ONLY whenever you know your cursor is read-only. Otherwise, DB2 has to guess and in the case of ambiguous cursors (that could be read only or could be updateable, DB2 has to assume the worse)

WITH UR告诉DB2，即使数据锁定了，你也可以读取。
WITH UR means you are happy to read data EVEN WHEN IT IS LOCKED BY OTHERS (Uncommitted Read) 可以读脏数据
This means your cursor will not be subject to lock waits (and possible timeouts) when reading locked data BUT you must also be prepared to accept potentially inconsistent data. For example, if someone inserts a row and then you read it but then they ROLL BACK the insert, you will have read a row "that never existed" in the table

 

两个写到一块，表示游标只读，而且会读到脏数据

We use such "FOR FETCH ONLY WITH UR" clause to our queries when we want to extract any data from real production tables for analysis without causing any disruption to the batch and online executing in production. This will tell DB2 that we need to only read some data and we are not worried if they have any inflight changes.
Of course we will be getting some uncommitted data but since we will be using these for analysis purpose only we are ok with it.
We do not have any "WITH UR" clause in any of our actual batch or online programs executing in Production. That is our standard.

 

他们两个不是互斥的，他们控制不同的事情。With ur表示，及时数据锁定了，你也可以得到返回数据。

No, they are not mutually exclusive at all - as they are controlling different things
WITH UR controls HOW the data that DB2 is processing is returned - you get data back even if it is locked

FOR FETCH ONLY says you are not then going to issue an update statement via the cursor
Now, you could argue that the FOR FETCH ONLY is superfluous, as WITH UR makes the cursor read only
BUT if, at a later date, someone were to remove the WITH UR clause, and you hadn't specified FOR FETCH ONLY you open up the possibility of an ambiguous cursor which DB2 might not be able to determine whether it is read only or not
So in my mind, it is still good practice to add FOR FETCH ONLY to EVERY cursor that you know really IS only reading data