topic Proc SQL with DB2 using the with Unbolcking Read (UR) in SAS Programming

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

kwyland1 — Wed, 13 Dec 2023 15:56:25 GMT

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?

Re: Proc SQL using the with UR

PaigeMiller — Tue, 12 Dec 2023 14:43:50 GMT

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

Re: Proc SQL using the with UR

Tom — Tue, 12 Dec 2023 16:00:48 GMT

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.

Re: Proc SQL using the with UR

Kurt_Bremser — Tue, 12 Dec 2023 16:36:31 GMT

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

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

LinusH — Wed, 13 Dec 2023 15:59:38 GMT

I think READ_ISOLATION_LEVEL=UR will handle this:

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

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

s_lassen — Mon, 18 Dec 2023 12:52:42 GMT

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