Proc SQL with DB2 using the with Unbolcking Read (UR)

[kwyland1]

When we run SQL queries from any sql editor against our mainframe db2 tables, we are asked to use the with UR.  Note: We only are allowed 'read-only' access to this database. 

Q. I'm not totally familiar with the isolation methods, but wondering if 'with UR' can be used in Proc SQL.  

Q. If syntax not allowed, how does SAS handle that method?

[PaigeMiller]

Just so I can follow along, what is “UR”?

[Kurt_Bremser]

"UR" is for Unblocking Read, which means the database can still be updated while the query is in progress.

I suggest to use explicit passthrough, where WITH UR is possible.

[Tom]

You can always use pass-thru SQL to run code that uses system dependent syntax changes.

Having to add extra non-standard clauses to every query you write sounds like a difficult way to work. I would hope there would be some way to change a setting so that unrestricted read is the default behavior for your session.  But you will have to ask your DB2 experts as that is NOT standard SQL syntax.

If they are worried you are impacting their operational data perhaps they can give you access to readonly views or a daily dump of the data to work from to isolate their production system from the loads required for reporting needs.

[LinusH]

I think READ_ISOLATION_LEVEL=UR will handle this:

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p1odod748tuqzjn1wwce690kzbcd.htm

[s_lassen]

In the good old days, you could speed up a DB2 query quite a lot by adding "for fetch only" at the end of your query, telling DB2 that you did not want to update anything.

May still be the way to go, it does not hurt to try...