|
View previous topic :: View next topic
|
| Author |
Message |
rajesh_1183
Active User
Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem
|
|
|
|
| Code: |
EMP_NM SELECT * ANSWER
+----------+ FROM emp_nm ==========
|ID|NAME | WHERE name like 'S%' ID NAME
|-- |------- | WITH UR; -- -------
|10|Sanders| 10 Sanders
|20|Pernal |
|50|Hanes |
+----------+ |
Please any one explain me this query...
what's this "WITH UR" option in the DB2 SELECT SQL Query. |
|
| Back to top |
|
 |
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. |
|
| Back to top |
|
|
rajesh_1183
Active User
Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem
|
|
|
|
| Code: |
***********************************
EMP_NM
+----------+
|ID|NAME |
|-- |------- |
|10|Sanders|
|20|Pernal |
|50|Hanes |
+----------+
***************************************
SELECT * FROM emp_nm WHERE name like 'S%' WITH UR;
********************************* |
ANSWER
| Code: |
==========
ID NAME
-- -------
10 Sanders
|
What's this "WITH UR" in the above query.. can any body explain me..
Sorry for the inconvenience in the above..
Thanks,
Rajesh |
|
| Back to top |
|
|
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:
| Quote: |
______________\________________________________
| |
| |
| <_,_____________ |
| >>__select-clause__INTO____host-variable_|__from-clause______________> |
| |
| >__ ___________________ _______________________________>< |
# | |_where-clause_| |_WITH__ _RR_ _| |
# | |_RS_| |
| |_CS_| |
| |_UR_| |
| |
|___________________________________________|
|
WITH
Specifies the isolation level at which the statement is executed.
| Quote: |
# RR
Repeatable read
# RS
Read stability
# CS
Cursor stability
# UR
Uncommitted read |
WITH UR can be specified only if the result table is read-only.
==============
Hope it was helpful... |
|
| Back to top |
|
|
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...
| Code: |
CS Cursor stability
UR Uncommitted read
RR Repeatable read
RR KEEP UPDATE LOCKS
Repeatable read keep update locks
RS Read stability
RS KEEP UPDATE LOCKS
Read stability keep update locks |
WITH UR can be specified only if the result table is read-only. |
|
| Back to top |
|
|
rajesh_1183
Active User
Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem
|
|
|
|
Hi all,
Thanks for ur prompt response...
Rajesh |
|
| Back to top |
|
|
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?
Anup |
|
| Back to top |
|
|
khamarutheen
Active Member
Joined: 23 Aug 2005
Posts: 677
Location: NJ
|
|
|
|
Hi Anup,
| Quote: |
| Are you sure abt this? |
Yes i m sure. Since it is uncommited read it can b done on read only mode. |
|
| Back to top |
|
|
reachsenthilnathan
New User
Joined: 20 Nov 2005
Posts: 15
|
|
|
|
| Quote: |
Are you sure abt this?
Yes i m sure. Since it is uncommited read it can b done on read only mode.
|
I dont think so. I tried a program where i openend a cursor with 'WITH UR' and then updated the rows in the cursor. It got updated. 'WITH UR' gets you even uncommitted data and it never locks the table. thatz it. It has no constraints that it has to be used only in read-only mode and all.
Please correct me if i am wrong.
Regards,
Senthil |
|
| Back to top |
|
|
saranya_sn
New User
.jpg)
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. |
|
| Back to top |
|
|
DavidatK
Active Member
Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA
|
|
|
|
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 |
|
| Back to top |
|
|
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 |
|
| Back to top |
|
|
rahulvv
New User
Joined: 05 Apr 2006
Posts: 3
Location: Mumbai, India
|
|
|
|
| Quote: |
| The ISOLATION (UR) or uncommitted read option allows an application to read while acquiring few locks, at the risk of reading uncommitted data. UR isolation applies only to the following read-only operations: SELECT, SELECT INTO, or FETCH from a read-only result table. |
UR stands for Uncommitted Read.
to understand let me take you through a scenario.
Let's say, in a program we has the following structure
Query 1: Insert into Table A.
Query 2: Select COL1 of Table A for sequence number(could be auto-generated).
Query 3: Insert into Table B whose COL2 is a Foreign key on COL1 of table A.
Query 4: Commit.
In such a scenario, the Query 2 has to select UNCOMMITTED ROW of Table A.
Only if the Query 2 is coded with 'WITH UR' the correct (UNCOMMITTED) row will be selected.
Thanks,
Rahul Vijayan Valsala |
|
| Back to top |
|
|
|
|
