WITH UR in SELECT/Isolation level in DB2 connector priority

[zulfi123786]

I was wondering if a "WITH UR" clause is put in the user defined query section of DB2 connector stage while reading from table and Isolation level is set to "Cursor stability" which would be given the priority?

Please share your thoughts.

Thanks

[MT]

Hi zulfi123786

DB2 will use UR in this case.

[hi.prabhunami]

I have generally seen in the all client projects that I am working on that.. the isolation level in any DB2 connector stage is always defined as Cursor stabilty however the user defined query always as select statment "WITH UR".

Incase the isolation level in the user defined query will always override the isolation level defined in the connector stage why do we need Cursor stabilty isloation level?

[MT]

Hi

Well it will override it if it is a SELECT statement - and it is needed to have an isolation level set when the WITH <Isolationlevel> is NOT used (so also for generated queries).
Cursor stability is the default isolation level in DB2 that is the reason why you see it in most situations (for the generated statements).
The reason that you see UR in custom statements is that the developer wanted to get ideal access and does not want to lock anything in the source accepting that he might read uncommitted data (this really depends also on the DB2 version used).