Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

"WITH UR" option in DB2 Select

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
rajesh_1183

Active User


Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem

PostPosted: Tue Jan 03, 2006 4:48 pm    Post subject: "WITH UR" option in DB2 Select
Reply with quote

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...whats this "WITH UR"

Thanks,
Rajesh.
Back to top
View user's profile Send private message

khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Jan 03, 2006 4:53 pm    Post subject: Select query
Reply with quote

Hi rajesh,
I have never c'n such a query.. May b that 'WITH UR' is like header.. but i dont know what is the use here.. let us c if any body can say??????? icon_wink.gif
Back to top
View user's profile Send private message
rajesh_1183

Active User


Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem

PostPosted: Tue Jan 03, 2006 4:54 pm    Post subject: "with ur" doubt in DB2
Reply with quote

*************************************************************
EMP_NM
+----------+
|ID|NAME |
|-- |------- |
|10|Sanders|
|20|Pernal |
|50|Hanes |
+----------+

*************************************************************
SELECT * FROM emp_nm WHERE name like 'S%' WITH UR;

*************************************************************
ANSWER
==========
ID NAME
-- -------
10 Sanders

Whats this "WITH UR" in the above query..can any body explain me..

Sorry for the inconvienence in the above..

Thanks,
Rajesh
Back to top
View user's profile Send private message
deepamk

New User


Joined: 30 Nov 2005
Posts: 8

PostPosted: Tue Jan 03, 2006 5:01 pm    Post subject:
Reply with quote

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





________________________________________________________________________
| |
| |
| <_,_____________ |
| >>__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.




# 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
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Jan 03, 2006 5:02 pm    Post subject: With clause
Reply with quote

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...

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
View user's profile Send private message
rajesh_1183

Active User


Joined: 24 Nov 2005
Posts: 121
Location: Tadepalligudem

PostPosted: Tue Jan 03, 2006 5:24 pm    Post subject:
Reply with quote

Hi all,

Thanks for ur prompt response...

Rajesh
Back to top
View user's profile Send private message
anup_marathe

New User


Joined: 17 Jan 2006
Posts: 2
Location: India

PostPosted: Tue Jan 17, 2006 3:59 pm    Post subject:
Reply with quote

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
View user's profile Send private message
khamarutheen

Active Member


Joined: 23 Aug 2005
Posts: 680
Location: NJ

PostPosted: Tue Jan 17, 2006 4:03 pm    Post subject: DB2 Query
Reply with quote

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
View user's profile Send private message
reachsenthilnathan

New User


Joined: 20 Nov 2005
Posts: 15

PostPosted: Sun Jan 22, 2006 7:14 pm    Post subject:
Reply with quote

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
View user's profile Send private message
saranya_sn

New User


Joined: 03 Jul 2005
Posts: 12
Location: India

PostPosted: Tue Jan 24, 2006 4:54 am    Post subject:
Reply with quote

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
View user's profile Send private message
DavidatK

Active Member


Joined: 22 Nov 2005
Posts: 700
Location: Troy, Michigan USA

PostPosted: Tue Jan 24, 2006 6:43 am    Post subject: Re: "with ur" doubt in DB2
Reply with quote

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 IBM DB2 manual below.

[url]
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/dsnsqh13/4.4.5?ACTION=MATCHES&REQUEST=UR&TYPE=EXACTW&SHELF=&DT=20030509185119&CASE=&searchTopic=TOPIC&searchText=TEXT&searchIndex=INDEX&rank=RANK&ScrollTOP=FIRSTHIT#FIRSTHIT
[/url]


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
View user's profile Send private message
ragshere

New User


Joined: 20 Dec 2004
Posts: 70

PostPosted: Sun Jan 29, 2006 4:49 pm    Post subject:
Reply with quote

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
View user's profile Send private message
rahulvv

New User


Joined: 05 Apr 2006
Posts: 3
Location: Mumbai, India

PostPosted: Mon Sep 22, 2008 9:58 am    Post subject: Reply to: "with ur" doubt in DB2
Reply with quote

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
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts BWO option in VSAM blayek CICS 3 Sat Nov 05, 2016 10:47 am
No new posts Regarding COBOL Stored Procedure opti... selvamsrinivasan85 DB2 4 Fri Nov 04, 2016 8:57 pm
No new posts Two Selects in a single select query Rohit Umarjikar DB2 1 Fri Nov 04, 2016 8:46 pm
No new posts Amount field is getting corrupted whe... thesumitk SYNCSORT 5 Tue Oct 18, 2016 8:20 pm
No new posts hot to get details when "EXEC CI... Andi1982 CICS 11 Tue Sep 20, 2016 5:01 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us