You can translate the question and the replies:

DB2 - WITH UR

Database: DB2 We are having a performance issue with a query and our DBA suggested to use "With UR" clause at the end of the query. At this time, the view in Denodo is built using the UI and I am not able to add this in where clause and I don't see any option in the settings of the datasource. Please advice is it possible to do it using the UI. Secondly, I created a view using 'create from query' option for the same view. For example the query is ``` select a, b, c from tab1, tab2 where tab1.x = '1' tab2.y = '2' with UR ``` But when Denodo pushes this query to DB2, it changes it to something like this ``` SELECT t0.a, t0.b, t0.c FROM ( select a, b, c from tab1, tab2 where tab1.x = '1' tab2.y = '2' with UR ) t0 ``` This changes the syntax and gives an error when I run it in DB2 directly. Please advice whether WITH UR is supported and how to proceed.
DB2
user
09-04-2020 15:26:03 -0400

2 Answers

With the second approach that I mentioned earlier, I am able to fix it by setting delegate sql sentence as sub query to no for the wrapper source configuration. So, the only question remaining is to know whether we have an option to include With UR to the view built using UI?
user
09-04-2020 15:33:23 -0400
Hi, Using the “With UR” clause at the end of a query tells DB2 that you want to use the uncommitted read isolation level. Denodo Virtual DataPort allows you to choose the transaction isolation which is the isolation level of the transactions and queries executed in the database of this data source. In order to choose the uncommitted read isolation level, when creating a new data source choose “Read uncommitted” from the Transaction isolation drop down menu. For more information please refer to [JDBC](https://community.denodo.com/docs/html/browse/7.0/vdp/administration/creating_data_sources_and_base_views/jdbc_sources/jdbc_sources) document. Hope this helps!
Denodo Team
14-04-2020 15:13:08 -0400
You must sign in to add an answer. If you do not have an account, you can register here