What is "WITH UR" option in DB2

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi rajesh,

“WITH UR” in a query tells DB2 that you want to use the Uncommitted Read isolation level.

When you append "WITH UR" to a DB2 SQL query, you instruct the database to operate at the Uncommitted Read isolation level for that specific query. This can be beneficial in certain scenarios where you need faster access to data and can tolerate potentially reading uncommitted (and thus possibly inconsistent) data.

Advantages of "WITH UR"
Performance: Because "WITH UR" avoids locking rows, it reduces the overhead associated with locking mechanisms, leading to faster query execution.
Reduced Lock Contention: It minimizes the chances of lock contention with other transactions, which can be especially useful in high-concurrency environments.
Readability: Useful for read-only operations like generating reports or monitoring data where absolute accuracy is not critical.

Disadvantages of "WITH UR"
Inconsistent Data: You may read data that is in the process of being changed by other transactions. This means you could read partially updated data or data that may eventually be rolled back.
Phantom Reads: You may see rows that did not exist at the start of your transaction or miss rows that were deleted.

Some Use Cases
Reporting: Generating reports where exact consistency is not crucial and where performance is more critical.
Monitoring: Real-time monitoring dashboards that need to display data quickly.
Large Queries: Running large queries where strict consistency is less important than performance.

rajesh_1183 · Posted: Tue Jan 03, 2006 4:54 pm

deepamk · New User Joined: 30 Nov 2005 Posts: 8

I found this from the IBM server library...

==============
The SELECT INTO statement produces a result table containing at most one row, and assigns the values in that row to host variables. If the table is empty, the statement assigns +100 to SQLCODE, '02000' to SQLSTATE, and does not assign values to the host variables. The tables or views identified in the statement can exist at the current server or at any DB2 subsystem with which the current server can establish a connection.

This statement can only be embedded in an application program. It is an executable statement that cannot be dynamically prepared.

Syntax:

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi rajesh,

The WITH clause specifies the isolation level at which the statement is executed. (Isolation level does not apply to declared temporary tables because no locks are acquired.)

U can use the following combination in WITH clause...

rajesh_1183 · Posted: Tue Jan 03, 2006 5:24 pm

Hi all,

Thanks for ur prompt response...

Rajesh

anup_marathe · New User Joined: 17 Jan 2006 Posts: 2 Location: India

I agree with khamarutheen's reply except this statement:
"WITH UR can be specified only if the result table is read-only".

Are you sure abt this?

The WITH UR option in DB2 SQL statements is used to specify an "uncommitted read" isolation level for the data being accessed. It allows a transaction to read uncommitted data, meaning data that has been modified but not yet committed (saved) to the database. This option is often used when you need to access data quickly and do not require strict transaction isolation or when you want to avoid locking conflicts with other transactions.

Key Points about WITH UR:
Uncommitted Read: It allows reading data that is currently being modified by other transactions, even if those changes have not been committed yet.

Isolation Level: The WITH UR option sets the isolation level to "Read Uncommitted" for the specific SQL statement. This means that the data read may not reflect the complete state of the database, as it can include uncommitted changes.

No Locking: Using WITH UR avoids placing locks on the data being read, which can improve concurrency and reduce contention in multi-user environments.

Performance: It can improve query performance as it does not wait for locks and reads the data as it is without waiting for other transactions to complete.

Data Consistency: The trade-off of using WITH UR is that it may result in reading inconsistent or partially updated data if concurrent transactions are modifying the same data being read.

When to Use WITH UR:
Reporting Queries: For read-only queries used in reporting or analytics where the most current data is not critical, and performance is a priority.

Data Validation: When performing data validation checks or audits where immediate consistency is not necessary.

Non-Critical Transactions: In situations where data consistency is less critical, such as non-critical batch processes or background tasks.

Considerations:
Data Integrity: Be cautious when using WITH UR as it can lead to reading inconsistent data, especially in highly transactional systems.

Transaction Scope: The WITH UR option applies only to the specific SQL statement it is included in and does not affect the isolation level of the entire transaction.

Lock Avoidance: It helps in avoiding lock contention but may result in "dirty reads" (reading uncommitted data), which may not be suitable for all use cases.

Compatibility: Ensure that your application logic and business requirements support the use of uncommitted reads without compromising data integrity.

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Hi Anup,

reachsenthilnathan · New User Joined: 20 Nov 2005 Posts: 15

saranya_sn · New User Joined: 03 Jul 2005 Posts: 12 Location: India

The "With UR" option is used in the programs where the data retrived can be either the commited / uncommited data. Thus the values of the columns which donot change frequently by the other jobs and the column value is of no importance is selected by the program with "WITH UR" option.

When a job is accessing the same table and updating some value , which the program reading is not using, WITH UR option is used. So that both can access the table and avoid contentional abends.

DavidatK · Posted: Tue Jan 24, 2006 6:43 am

rajesh_1183,

Your going to be rich, you get my two cents also.

As far as some of the previous replies, ?WITH UR? is valid only if the resulting table is read-only. Check out the link to the

Now to what ?WITH UR? means.

A COMMITTED row means that another program has updated the row and it has been permanently updated on the table, either from a ?COMMIT WORK? in the program, or the program successfully ending.

An UNCOMMITED row means that a program has updated the row, the row has a lock placed on it, but the row has not been permanently updated on the table and the update to the row can still be backed out, if the program abends, or the program does a ?ROLLBACK?.

What does this mean to you when you do a ?WITH UR?? The ?WITH UR? will ignore the locks when you do a SELECT and give you the ?UPDATED? row, even with the lock applied. You have, by using the ?WITH UR?, decided that you are willing to take the change that the row will be backed out and you may have incorrect data. The ?WITH UR? is generally used for reporting where it isn't absolutely critical that it is 100% actuate.

If you do not use the ?WITH UR? your program will probably still run, but it will may spend some time in a wait state, waiting for the locked rows to be committed. If your program waits to long, it will abend with a timeout.

Hope this helps,

Dave

ragshere · New User Joined: 20 Dec 2004 Posts: 70

Hi ,
WITH UR option is always used with SELECT query only.
If you that option, DB2 will fetch the qualifying rows (including uncommitted rows i.e modified by someone else and not committed)

In this case if that person gave ROLLBACK, the values you received are wrong. So you may be using with inconsistent data.

generally we use WITH UR to avoid locks on the table.WITH UR won't claim any lock on the table we are using in the query.

Thanks
Raghavendra

rahulvv · Posted: Mon Sep 22, 2008 9:58 am